CRMScripts for SuperOffice
Overview: In certain situations we need to merge two databases, and this routine is ment to automate this process as much as possible. This user guide will assume that both databases are mounted locally and fully functional.
The dbMerge-script must be executed on the source database. The moveAttachment-script can be run on any working installation afterwards. These scripts will generate a SQL script for merging databases, and a batch output for copy/renaming the files in the attachment folder. Please note that SO_ARC (and Customer Center-files) will need to be copied manually.
This script has been tested successfully on the following versions of SuperOffice:
8.5 R10
8.5 R15
What the different scripts do:
dbMerge.crmscript - merges two locally mounted SQL databases
moveAttachments.crmscript - renames and copies files from the attachment-folder
dbPrep.sql - modifies an online DB to run on a local SQL-server
dbMove.sql - modifies install-paths, urls and more when moving a DB
Preparation:
You will need admin access to both installations, as well as ejsysuser credentials.
Make sure expander services is activated on both databases.
Mount both databases on a local server or image and make sure the installations is fully functional.
Make sure both databases is running the same version of SO.
Right before merging, consider taking a backup of both databases.
Merge database:
Run the dbMerge.crmscript on the source database, adjusting variables as needed. Copy/save the output (SQL query) and run it on the SQL server to merge the databases. This will take some time, and in turn output some bumped values from the sequence-table. Note the value for attachment, as you will be using this in the next step.
Merge files:
Attachments: On the target database load moveAttachments.crmscript, and alter the paths as needed. Insert the attachment value from the previous step. Run it and copy/save the output. The output should be saved as a batch script, meaning you should create a .bat file on the server so that you can run it in order to rename and merge files from the attachment folder.
SO_Arc: All the folders in the source SO_Arc must be renamed to foldername(DB_Merge). This can be done via the command line, using this command: for /D %f in (C:\SuperOffice\SO_ARC*) do rename "%f" "%~nxf(DB_Merge)"
Copy/move SO_ARC manually from source to target destination.
Manual steps:
Every merged user need to have their password reset, and probably their username changed as they will appear with the ‘DB Merge’-tag.
Most of the merged data will appear with a ‘DB Merge’-tag. This needs to be cleaned up, preferably by the customers data quality responsible. This is time consuming work, and it's recommended that the customer is made aware of this.
Verification after merge:
Appointments; Does appointments show for both an existing user and for a merged user?
Files in sales: Are you able to open a document from and existing entity and a merged one?
Selections: Does imported selections function correctly?
Sales: Are you able to view imported sales and quotes?
Service: Check and see if requests has been imported.
Service: Try to download or view an attachment from an imported request.
Maintaining the script:
With each release there might be modifications done to the table structure, and it will be listed here: https://community.superoffice.com/documentation/SDK/SO.Database/html/WhatsNew-WhatsNew.htm. If this happens, and you discover it first, notify the script maintainer, and/or update the script to include the latest changes. Update this document as well with the latest tested version successfully tested.
Variables:
Script 1 / Variable 1:
Script 1 / Variable 2:
Script 1 / Variable 3:
Script 2 / Variable 1:
Script 2 / Variable 2: