web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Change of SQL Collatio...
Microsoft Dynamics GP (Archived)

Change of SQL Collation (GP10)

(0) ShareShare
ReportReport
Posted on by William Kow 3,795

Currently, the GP 10 (SP2) is ODBC connected to SQL 2005 database with SQL Collation = Latin1_General_CI_AS

Could you please advise me how to change the SQL Collation from Latin1_General_CI_AS changing to a different Collation SQL_Latin1_General_CP1_CI_AS

Many thanks in advance.

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    Do you have GP installed and running already, is there data in the tables? Would it be acceptable for you to re-install MSSQL and lose any data you had? 

    Facing the same issue today on a customer site!! SQL installed by the hardware supplier with the incorrect collation. Its a new install of GP planned for today. Rather than re-install MSSQL or try and do a command line install, I just installed a second instance with the correct collation and carried on from there.

    I'm guessing that you are detatching databases from MSSQL2000 and attaching to a new install of MSSQL2005? Remember to choose codepage 1252 (this is the CP1). There is also a tick box for 'use collation for older versions of SQL' that you need to select.

    Best regards,

     

     

     

    Best regards,

     

  • William Kow Profile Picture
    William Kow 3,795 on at

    Many thanks for your reply.

    Yes, the existing GP has been running with the wrong SQL collation (Latin1_General_CI_AS) for nearly 2 years. There are so many transaction records are already in this live production databases.

    In order to have different collation, I will need to setup another SQL instance. However, I do believe it will be a problem -- by detaching those live databases in the old SQL instance and then re-attach them in the new SQL instance with correct SQL collation.

    Appreciate if you can provide me some guide on how to deal with this issue. Thank you very much.

  • Community Member Profile Picture
    Community Member on at

    Understood. Its not easy...Check on the web for 'changing collation' etc. and there is quite a bit of info.

    Install a new instance of MSSQL with the desired collation. You will have to export all of the data from the old database (using BCP or DTS), and import it back into the new data base again using BCP or DTS. I did this once and swore I'd never do it again!

    Also, check out Mohammad's post here https://community.dynamics.com/blogs/gpmohammad/comments/48816.aspx. He has an exe that might help.

    Best regards,

     

  • Ron Wilson Profile Picture
    Ron Wilson 6,010 on at

     William,

     Not trying to be "thick" here but why would you want to change the collation?

     Ron

  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    Changing the collation is not as simple as it sounds. Typically, you will need to export all data contained in all tables to a text file or XML file, rebuild your SQL Server, reinstall GP, recreate all companies, then reimport the data. I suggest you take a look at Support Debugging Tool which would allow you to export all your data to XML then reimport once the environment is reconfigured.

    Also, take a look at my article Microsoft Dynamics GP and SQL Server Collations. This should help clarify a few things.

  • Emily Ann Profile Picture
    Emily Ann 620 on at

    This isn't an answer it is an expanded question ... currently we have GP9 on a SQL Server 2005 server.  The server and databases use Latin1_General_CI_AI  and interact with other servers and databases that use the same collation.  At this point in time and for the foreseeable future our company only uses the English language aside from names that may have accents.  We are getting ready to upgrade to GP 2010 which will be on a SQL Server 2008 R2 installation.  We had created the test environment uses the same collation:  Latin1_General_CI_AI.  When we upgraded to GP 9 from GP 7.5 the system requirements did not specifically say to use SQL_Latin1_General_CP1_CI_AS  ... it just said code page 1252 and either sort order 50 or 52.  Considering our environment, can anyone point me to any specific reason we would have to change the collation?  To take the time and effort to change server/database collations would require a lot of justification to management.

    Thank you in advance for any insight you may have.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Emily, There are products aditional to Dynamics GP that if the company plans on using them they all need to match one SQL collation.

    I've had cases where customers have sharepoint in 1252, and GP in Binary, and they wanted to install Business Portal on top of it, so changing collation is a must.

    I agree with Mariano about changing collations and the processes to do it, in fact its not a simple task but doable, but Microsoft stands that you are out of support if you do your own collation change, in order for them to keep you under support then you must pass this task to Professional Services.

    I know, I know, but in order to e compliant I had to send the DB's on all 5 cases before, I have tested the methods on my test installs, but just don't want to have my clients out of support.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Gents,

    As a clarification, kindly keep in mind that we have two collations, one for SQL Server Installation and the other for installed databases:

    1. Changing the SQL Installation Collation could be easily done be two methods, either by reinstalling SQL server and selecting the correct collation or by creating specific command using below article: msdn.microsoft.com/.../ms179254.aspx that recreates the master database with the new collation.

    2. Changing the SQL Database Collation is also divided into two sections, one for databases with indexes and dependencies on collation (like Dynamics GP Databases) and one without any dependencies on the collation, the second type could be modified by running a one line script and everything will be changed.

    The problem is with the first type where there are dependencies need to be dropped and recreated, this will not be an easy task to be done manually.

    Normally I use an exe that generates all the scripts to drop and recreate all the collation dependencies objects and change your database collation, mail me and I will be happy to send you the application.

  • Community Member Profile Picture
    Community Member on at

    Hi Mohammad,

    Can you help me out with this problem? can you hand me the exe?

    kind regards

    JJ

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Please send me a mail to mohdaoud (at) gmail (dot) com

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Community Member Profile Picture

Community Member 2

#2
mtabor Profile Picture

mtabor 1

#2
Victoria Yudin Profile Picture

Victoria Yudin 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans