The following entry found in SRM.log file
Failed to create database tables. Details: DB does not contain SRM schema: VdbError: Column name does not exist in table dr_product_info
SRM version: 5.0.0
SQL server version (original SQL server): 9.0.5292
SQL server version (new SQL server):9.0.5292
SRM and SQL servers are on different host. Both SQL servers are running in mixed mode.
Here is how the database was migrated
- Stopped SRM service
- Detached database from original SQL server
- Copied database and log file
- Attached database on new SQL server
- Create SQL user with the same name and password
- Ensure SQL user meet requirements (Screenshots attached)
Database name: SRM (same name on both old and new SQL server)
user: srm (same user and password on both old and new SQL server)
schema: srm (same on both server)
Applied following permissions to srm user: db_owner, bulk insert,connect,create table,create view.
- Updated ODBC connection in the SRM server. Only change was the server name. ODBC connection test is successful
- Started SRM service. Service did not start. The above error logged in the log file.
I have already tried these steps to solve the issue
- Gave srm user access to master and msdb database
- Gave sysadmin permission to srm user
- Login in as srm user in SSMS. I was able to create a test table in SRM database with out any errors.
- Executed sp_tables to find owner of tables in SRM database. All tables are owned by srm user (See screenshots)
As soon as I updated the ODBC connection to point the old SQL server, SRM service starts with out any error. I used SQL profiler to trace the queries executed while the service was started. I noticed the queries included the srm user for old SQL server but not for new SQL server.
SQL Profiler - Old SQL server
exec [sys].sp_columns_90 N'pd_localsite',N'srm',NULL,NULL,@ODBCVer=3,@fUsePattern=1
exec [sys].sp_pkeys N'pd_localsite',N'srm',NULL
exec [sys].sp_statistics N'pd_localsite',N'srm',NULL,N'%',N'N',N'Q'
exec [sys].sp_columns_90 N'pdr_protectiongroup',N'srm',NULL,NULL,@ODBCVer=3,@fUsePattern=1
exec [sys].sp_pkeys N'pdr_protectiongroup',N'srm',NULL
exec [sys].sp_statistics N'pdr_protectiongroup',N'srm',NULL,N'%',N'N',N'Q'
SQL profiler - new SQL server
exec [sys].sp_columns_90 N'pd_localsite',N'',NULL,NULL,@ODBCVer=3,@fUsePattern=1
exec [sys].sp_pkeys N'pd_localsite',N'',NULL
exec [sys].sp_statistics N'pd_localsite',N'',NULL,N'%',N'N',N'Q'
exec [sys].sp_columns_90 N'pdr_protectiongroup',N'',NULL,NULL,@ODBCVer=3,@fUsePattern=1
exec [sys].sp_pkeys N'pdr_protectiongroup',N'',NULL
exec [sys].sp_statistics N'pdr_protectiongroup',N'',NULL,N'%',N'N',N'Q'
Does anyone know how to fix this issues ?
