After updating Backstage R3 to R4, BackStage is unable to connect to the remote databases anymore.
When the BackStage databases have been moved wrongly to a remote database server, it is possible that one configures the database connection to use sa with the default sa password to make things work, because somehow it doesn't work with admin.
However, when updating BackStage from R3 to R4 the update will fail as the different BackStage databases are not owned by admin but by dbo which is probably the sa user.
An error will appear stating "java.sql.SQLException: There is already an object named 'dbcnv' in the database." . In the bgmd.log file this and all other errors for the different tables can be found.
The owner of the BackStage databases must be admin!
To verify who the owner of the databases is, you can use the SQL Server Management Studio (Express). See the above screenshot. You can install the SQL Server Management Studio Express from the Esko Software Suite 7 R4 DVD2. Unfortunately you cannot change the owner with this tool.
The below solution describes a few SQL commands. If you are inexperienced in this, it is strongly advised to ask assistance of someone who is.
Here are the steps to solve the problem:
Use the BackStage Server Admin wizard to create a database (name is not important as it will be deleted later) in the database instance where the BackStage databases reside. If you encounter an error message while connecting to the SQL 2005 databases, you have to install the SQL Server 2005 Backward Compatibility tool from Esko Software Suite 7 R4 DVD2.
Other SQL Server 2005 Express Edition tools
The SQL Server 2005 Backward Compatibility component is required if the BackStage database administration tool is to be used to administer a remote SQL Server 2005 system and if SQL Server 2005 Express Edition is not installed on that BackStage system.
Install SQL Server 2005 Backward Compatibility
The SQL Server 2005 Management Studio Express Edition component can be used to administer SQL Server 2000 and SQL Server 2005 instances.
Install SQL Server 2005 Management Studio Express Edition
By creating this database with the BackStage Server Admin wizard the admin user will be created for that particular instance with the correct settings as needed by the BackStage.
Start the T-SQL command line through the BackStage Server Admin wizard.
Connect to the correct database server and instance as sa user and the default sa password through SQL authentication. A command window will appear with a 1> prompt.
To change the owner for the BSJobs database tables type the following:
- 1>use BSJobs [enter]
- 2>go [enter]
- 1>exec sp_changeobjectowner '[dbo].[Categories]','admin' [enter]
- 2>exec sp_changeobjectowner '[dbo].[Customers]','admin' [enter]
- type this line for every table in the BSJobs database !!!
- 22> go [enter]
When no errors are returned, all tables should now show admin as the owner as shown in this screenshot.
Repeat step 3 for Fastlane_TM, Fastlane, BGARCHBASE and BGTHMBBASE if necessary.
Delete the database you created earlier to have the admin user created.
Now run Activate Server and observe that no database errors appear!