?
Solved

moving Great Plains to server with different collation?

Posted on 2006-05-22
7
Medium Priority
?
1,132 Views
Last Modified: 2008-01-09
We have a db/app server hosting Great Plains 8 that is having hardware problems - rather than repairing (it's an old machine) or replacing it, I'm considering moving GP over to a much newer server with plenty of overhead.  The potential problem is the two SQL servers (both are MSSQL2k, one SP3, one SP4) use different collations - Great Plains is running on SQL_Latin1_General_CP1_CI_AS (case insensitive) while the server it needs to move to is using Latin1_General_BIN (case sensitive).

My questions are - can the db be converted to another collation, or......would GP function correctly in a case sensitive environment?   Alternatively, I know the collation supported is fairly granular and can be set at different levels, but can a particular database use one collation with the rest of the server using a different one?
0
Comment
Question by:cusamis
  • 5
  • 2
7 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 16736535
0
 
LVL 3

Author Comment

by:cusamis
ID: 16737368
I should have asked that a little differently - the db collation can be changed, but will Great Plains 8 support the Latin1_General_BIN collation?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16737462
I'm not sure -- it should -- let me see if I can find it in the KB
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 38

Expert Comment

by:Jim P.
ID: 16737489
Issue

After transferring data from one SQL server to another SQL server, I am getting the error message - SQL Server cannot resolve collation conflict for equal to operation, the stored procedure glCLAccountMaster failed accessing SQL DBMS: 446 eEnterprise: 0 when running checklinks against the Account Master.

Resolution

You will need to check that each database is using the same collation (sort order). Run the following in Query Analyzer against the master database:

sp_helpdb

This will give you the list of your databases and information about each database. Look under the Status column. You will see Collation = and Sort Order =. This needs to match your master database Collation = and Sort Order =.

If DYNAMICS and all your company database have the same Collation = and Sort Order=, but the Collation = and Sort Order = is different than the master database. You will need to reinstall or rebuild your master database to match the Collation and Sort Order of your DYNAMICS and company databases.

This article was TechKnowledge Document ID: 24755
0
 
LVL 3

Author Comment

by:cusamis
ID: 16737771
Ok, thanks, that tells me that all databases on the server will need to be the same collation if GP is involved, no per-database collation.


Now, the only thing left is if forced case-sensitivity will break GP at all, that's my biggest concern.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 1500 total points
ID: 16737934
The following list shows the SQL Server sort orders that the financial applications support:• Column to verify: Server Collation Default
Column contents: Latin1-General, binary sort
Column meaning: Binary Sort Order 50
• Column to verify: Server Collation Default
Column contents: 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
Column meaning: Dictionary Order Case Insensitive (DOCI) Sort Order 52  
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16738043
Glad to be of assistance. May all your days get brighter and brighter.

Why the B?
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question