stevegray
4Penny.net
Points: 55409

9/24/2017 3:37:03 PM

How to change the DB Collation for a Dynamics GP server

* This article, and all our great SQL (Dynamics) documentation, Is available on the SQL (Dynamics) menu

BACK UP YOUR DATABASE FIRST

This article represents several days work. I'm sure that you won't want to read through the code, but read enough that you'll remember where you saw it. That way when you need it a year from now you'll remember where you saw it.

This week I encountered a Dynamics GP SQL server set to 'BINARY' collation. I tried to use it but as I loaded my library code onto the box to do an integration, none of it was running. The stored procedures wouldn't even create. Grrrr.

The resulting solution is below. There are three basic steps:

  • Set the SERVER collation
  • Set the DATABASE collation
  • Set the TABLE FIELD collation (each text field in each table is assigned its own collation)

I didn't need to do the first, the consultant on the job had already fixed the SERVER collation; so the code below does not do that. But you'll need to do that first.

So, my task was to set the DATABASE and FIELD collation. There is a method that I see described that involves using BCP to export all the data, then fixing the install and then BCPing the data back in. I came across this solution before I found help with the BCP method, so that's what I used.

I got the method (and most of the code) from

http://sqlmag.com/database-performance-tuning/seven-step-process-changing-database-collation

I fine tuned it for use with Dynamics GP, and my resulting code is easier to use for a GP db. But props to SQLMAG, their code is great, and the concept came from them.

The work is not in the 'change collation' commands, they're easy. The work is in scripting all the indexes, constraints, views, and table valued functions that need to be dropped and recreated to be able to run the 'change collation' commands.

One final note. The code below consists of the first main script and a number of following scripts that handle various 'drop and recreate' tasks. The scripts are numbered 020, 030, 040, etc. They each produce two resulting scripts: a drop script and a create script. I named all those '020 drop' and '020 recreate', etc. Make sense?

Ready? Here we go. HTH.

 

4Penny.net
Version: All
Section: SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3