Issue When upgrading an existing database to V4.0.1 the DBUpdate Utility terminates with an error.
Detail The DBupdate utility does not script for all unknown constraints, indexes, foreign keys or primary keys associated with each table within the Reach database. This issue occurs due to unknown indexes and constraints etc that the automated script cannot remove. This can occur when a database has been modified outside of the scripting and Anthem Software has not controlled the modifications. Solution Step1 Remove the following articles from the DatabaseNameDB_Pub publication. • Categories_X_Items • CustomProperties_X_Items Remove the following articles from the DatabaseNameDB_Pub_Maintain publication. • Categorie_X_Resource_Items • Custom_Properties_X_Resource_Items • Entities Remove the following views from the DatabaseNameDB_Pub_Maintain publication. • vwSearch • vwSearchOrders Depending on how old the database is these views may not exist. Step 2 Remove any constraints, primary keys or indexes from the tables that were removed from the publications in Step1. You may need to do this by way of a script. E.g. USE [InternalServerDB] GO ALTER TABLE [dbo].[Categories_X_Items] DROP CONSTRAINT [DF_Categories_X_Items_SelectedItemGUID] Note: You will may need to remove the RowGUID before removing anything else. If you don’t you may have problems deleting constraints etc. Step 3 The script ReachServer_001_000_to_001.sql needs to be manually run against the problem database. Backup the database before running the script! The script is stored on the database server in C:\Program Files\Anthem Software\ReachServer\Scripts. This location assumes defaults were used during the original install. Replace the *ReachServerDBName* parameters with the actual value. Once the script has been manually executed, there may be a list of errors, E.g.: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 227 Cannot rename the table because it is published for replication. Msg 15051, Level 11, State 1, Procedure sp_rename, Line 227 Cannot rename the table because it is published for replication. Msg 15051, Level 11, State 1, Procedure sp_rename, Line 227 Cannot rename the table because it is published for replication. (0 row(s) affected) (1 row(s) affected) (0 row(s) affected) (1 row(s) affected) Msg 3728, Level 16, State 1, Line 14 'DF_Categories_X_Items_Value' is not a constraint. Msg 3727, Level 16, State 0, Line 14 Could not drop constraint. See previous errors. Msg 3728, Level 16, State 1, Line 14 'DF_Categories_X_ResourceItems_Value' is not a constraint. Msg 3727, Level 16, State 0, Line 14 Could not drop constraint. See previous errors. Msg 3728, Level 16, State 1, Line 5 'DF_CustomProperties_X_Items_Value' is not a constraint. Msg 3727, Level 16, State 0, Line 5 Could not drop constraint. See previous errors. Msg 3728, Level 16, State 1, Line 6 'DF_CustomProperties_X_ResourceItems_Value' is not a constraint. Msg 3727, Level 16, State 0, Line 6 Could not drop constraint. See previous errors. Msg 207, Level 16, State 1, Procedure vwSearchOrders, Line 7 Invalid column name 'OrderAdjustment'. Msg 208, Level 16, State 1, Procedure vwSearch, Line 6 Invalid object name 'dbo.vwSearchOrders'. Dealing with Errors Of the errors listed above the first is; Msg 15051, Level 11, State 1, Procedure sp_rename, Line 227 Cannot rename the table because it is published for replication. The table that this is trying to rename is being reported as being part of a publication. The tables have been removed from the publications in Step 1 and we therefore need to move to Step 4 to resolve any issues. .......continued in next post >>
Step 4 Export the data out of the Categories_X_ResourceItems table. To do this you will need to use the SQLServer Import and Export Wizard. (Right Click the database and select Export from the menu). Rename the above table to Categories_X_ResourceItems_OLD and locate the exported data. This will typically be stored in a table called Query in your database. Rename the table ‘query’ to Categories_X_ResourceItems and run that failing part of the script. It should now execute successfully. Repeat this for any remaining tables with errrors. Step 5 Run the following script on the database to update the versions table. INSERT INTO [InternalServerDB].[dbo].[Versions] ([DBVersion] ,[DataVersion] ) VALUES (1,1) Step 6 Rerun the DBUpdate utility. The file DBUpdate.exe can be found in C:\Program Files\Anthem Software\ReachServer. Select File and set the Database Parameters before clicking on Update.