RKL eSolutions | Insights, Tips and Trends from a top Sage Reseller and Technology Specialist

Changing the Server Collation Method in SQL for Sage ERP X3

Written by Cliff Horst | Sep 24, 2013 10:10:42 AM

We recently encountered a SQL Server that had Sage ERP X3 installed for testing, but it did not have the correct collation method. Sage ERP X3 requires a SQL collation method of Latin1_General_BIN. This option gets set during the installation of SQL Server and cannot be changed from SQL Management Studio or scripting. However, the collation method can be changed by reinitializing the system databases. Here is the process to change the server collation method:

  1. From the Sage ERP X3 client, Stop the Accounting Tasks and the Batch Server
  2. Perform a data extract of each folder you would like to retain
  3. Script out any SQL Agent jobs you wish to save
  4. Drop the x3v6 database in SQL (and any other user databases)
  5. Run the following from a cmd window (will have to run the cmd window by right-clicking and choosing Run as Administrator)

    C:SQL Media>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=X3V6 /SQLSYSADMINACCOUNTS=BUILTINAdministrators DOMAINsagert /SAPWD=[SQL sa password]

    /SQLCOLLATION=Latin1_General_BIN

  6. This process runs about 5 minutes and it will reload the master, model, msdb, and tempdb databases from default. Any SQL jobs and user databases will need to be recreated.

Here is the process to recreate a Sage ERP X3 database from scratch:

  1. Access the following XML files to change the status to IDLE from Active:
    1. SAFEX3ADXADMINinstAdx_appli.xml
    2. SAFEX3ADXADMINinstAdx_runtime.xml
    3. SAFEX3ADXADMINinstAdx_sqlserveur.xml
    4. SAFEX3ADXADMINinstAdxinstalls.xml (just change the database, runtime and application sections)
    5. SAFEX3ADXADMINinstlistsolutions.xml (just change the database, runtime and application sections)
    6. SAGEX3V6X3V6Folderssolution.xml (just change the database, runtime and application sections)
  2. Open the Sage ERP X3 Console and expand the solutions
  3. The status should read IDLE on the Database, Application and Process
  4. Click the Data button to allow Sage ERP X3 to recreate your Sage ERP X3 database
  5. Click the Application button to load the default Sage ERP X3 reference folder into the new Sage ERP X3 database
  6. Once the Sage ERP X3 database is at the desired patch level, import each of the folders you extracted data from earlier

This process is rather involved, but it is still a time saver from uninstalling and starting from scratch.
View full post