KB20230523/01: Sage 200 Evolution Tip & Trick – Microsoft SQL Backup/Restore. How to compare Microsoft SQL objects contents between two Sage 200 Evolution company databases.

Please note:

a) It’s recommended that this query should preferably be handled by a professional Evolution support consultant as it may include complex, database analysis skills and understanding how to compile SQL queries, to correct the above problem.

Therefore, please contact either your Evolution business partner (BP) or Evolution Support to get this issue resolved.

b) Sage does not directly support any foreign objects in an Evolution company. Please consult with the relevant 3rd party developer (who have developed them) if any issues / errors are encountered in the Evolution company relating to them.

c) Sage Evolution is however still able to accommodate the existence of having foreign objects within the Evolution company database as used for various reasons, e.g. integration with a 3rd party application.

Apply the following steps to achieve the above:

1. This solution includes comparing the client’s Evolution company database (containing foreign object/s) with a ‘standard’ database in which no foreign objects should exist, in order to identify the foreign objects.

2. Create a new Evolution company database (DB) (called e.g. DEMO) on the same Evolution version as the relevant live DB in which the issue/error/s have been detected.

3. Within MS SQL Management Studio, click the Results to File button

4. Run the following MS SQL script in the DEMO DB:

Use DEMO

select * from Sysobjects

order by name

5. Notice the Save Results screen that automatically opens. Browse to the relevant location and save the new export file with an appropriate name

6. Run the following MS SQL script on the live DB in which the issue/error is observed and to which the DEMO DB is compared with. In this case, the DB’s name is called SUNSHINE.

6.1. Use SUNSHINE

6.2. Select * from Sysobjects

6.3. Order by name

7. Repeat step 5 above and save it with an appropriate name.

8. Open a new MS Excel file.

9. Within Excel go to File | Open

10. Browse to the first file exported in step 5 above

11. Change the option at the bottom right below to All Files and open the file

12. Click the Next button below.

13. Click the Next button below.

14. Click the Finish button below.

15. The new Excel file should now open with the imported sysobjects’ records content and then be saved as a normal MS Excel file.

16. Repeat steps 8 -15 above but this time import the second *.rpt file exported from MS SQL Management Studio, from the live Evolution company DB. 

17. You can now copy the content of the first MS Excel file into the second one (or vice versa) and use the standard MS Excel tools/utilities to compare the content between the sysobjects records exported from the two mentioned databases, by specifically referring to the name column below.

18. The goal here would be to identify the sysobjects records originating from the live Evolution DB, which are not found in the DEMO DB.

Such records could be regarded as foreign objects as they have not been developed by Sage. Any foreign objects identified should then be inspected and fixed by the relevant 3rd party developer.

19. Finally, please also note that any foreign objects (existing in the company) may cause company upgrade issues/errors. You should therefore do the following in this case:

Restore the backup that was made before the company upgrade was performed.

With the assistance of the 3rd party developer or your local business partner, drop all foreign objects in the company database.

Perform the company upgrade.

With the assistance of the 3rd party developer (or your BP if he knows how), restore the foreign objects as needed on the upgraded company database.