SQL Sort order

(0) Share

Report

Posted on by Community Member

Hello,

We are in the process of migrating our Dynamics GP SQL databases from SQL 2005 to SQL 2008. Both instances of SQL are on separate servers.

The existing SQL Server 2005 sort order shows the following when doing a sp_helpsort query:

Latin1-General, binary sort

The SQL server 2008 Server shows when running a sp_helpsort query:

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

If I backup the SQL databases from SQL 2005 and restore to SQL 2008, will this be an issue given the different sort orders between the two servers?

If the difference of Sort Orders will be an issue, what are my options?

One more item I want to add is that once I move all the GP databases to SQL 2008, I will plan to upgrade GP 10 to GP 2013 with Management Reporter.

Any advice is greatly appreciated

*This post is locked for comments

All responses (4)

Answers (0)

Richard Wheeler 75,848 Moderator on at

Like (0)

Report

RE: SQL Sort order

Jay, I have been bitten by this a couple of times in the past. I get asked to perform a server move, I install SQL using the default collation sequence and then load up GP only to discover the collation sequence is wrong. I see two options here. Install SQL with the default collation sequence and then do the bulk copy process or Install SQL with the binary sort collation sequence. This was when you create new databases the collation sequence on the database will also default to binary sort. You want to make sure you do not mix collation sequences. Both collation sequences are supported. The other topic is searching. binary sort is case sensiitve and the other is not. So if you want to always search by case sensitive use the binary sort.
Community Member on at

Like (0)

Report

RE: SQL Sort order

Thanks John / Richard for the reply. Is the option of not changing sort order supported? If I were to just build the SQL 2008 server to keep the same sort order as Latin1-General, binary sort that is on SQL 2005, would this be supported? My only concern is for other products such as Management Reporter to work with this collation.
Suggested answer

John Lowther 5,122 on at

Like (0)

Report

RE: SQL Sort order

Hey Jay,

While I agree with Richard, I wish to throw in my two cents.

The last I heard, Microsoft states that any collation conversions are supported only if they are done by MBS Professional Services.

With that said, you may want Microsoft to do the conversion for you.

It will cost, but it guarantees that you are still covered under support.

Let me know how it goes,
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

RE: SQL Sort order

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data is the default SQL collation sequence and is compatible with GP. The Latin1-General, binary sort collation is the old original sort order for GP dating back to version 1.0 and SQL Server 6.5 and 7.0. To convert I have always done it the hard way by doing a bulk-copy out and a bulk copy in. There are other ways but this has always worked for me. Essentially what you are doing is extracting all tables in the GP databases out to text files, create GP databases with the newer collation sequence, truncate all tables and then import those text files into the new daabases. I have the instructions and scripts if you need them. Just send me a private message and I will send them over.