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
It's a lot of work no matter how you slice it but, please read this from Microsoft on restoring datbases of differing collations. technet.microsoft.com/.../ms190725(v=sql.105).aspx
I am with Ian and Jonathan on this one.
We had a situation with this in the recent past - new server, hardware vendor doing everyone a favor by 'pre-installing' software - no clue - and due to that 3 of the 4 vendors had to redo all their work on a new instance.
We walked into a mess and simply created a new instance MBSGPS on the same server with the GP supported collation and we were fine.
New instance is better that 'manually' moving data any time.
You can find the instructions for this http://www.mssqltips.com/sqlservertip/2901/how-to-change-server-level-collation-for-a-sql-server-instance/ here. The bulk is simply detatching the user databases and then making your changes and then attach the database back. IF you had the option of a clean install that would be the best way but if not at least test in a dev environment. I hope this helps.
Hi Kim,
We have in the past changed the sort order of the DynamicsGP Databases through BCP.
It depends on the situation as Mohammad mentioned on his earlier post.
If the SQL Server collation is correct but your Dynamics GP databases collation are wrong, what we would do is to export the content of each database through BCP then drop and recreate all the databases through Dynamics GP Utilities.
Once the databases are created then truncate the tables, then BCP back all the content of the database.
The steps used to be posted as a KB article in PartnerSource, if you are worried about Microsoft having to support the collation change.
Kind regards,
Leh
Richard,
Did you find out if Microsoft will no longer support you if you change the collation yourself?
We have to change the collation on our Dynamics GP 2010 database. I've researched the steps to changing the collation of a database but have read conflicting posts. General posts on how to change the collation of a SQL database do not mention having to export data yet posts on changing the collation of a Dynamics GP database do state the data must be exported using BCP. Is that true? Why?
In our test environment we created a new SQL server with the new collation. Copied over our DYNAMICS and company databases from production. Now I'm in the process of following the steps outlined in this post sqlmag.com/.../seven-step-process-changing-database-collation. Does that match up with your past experience in changing the collation of the DYNAMICS and company databases for Dynamics GP?
Thanks for any assistance you can provide.
Kim
Francisco, is it true that Microsoft will no longer support you if you the change of collation yourself? What if your partner does it? I have have had to do a handful of these over the years and have the process well documented. I go back to the days of eEnterprise and SQL 6.5/7.0 where the case sensitive sort order was the only one supported. Then when our clients upgraded they were on the original sort order and would request to switch to the newer case insensitive sort order.
Please send me a mail to mohdaoud (at) gmail (dot) com
Hi Mohammad,
Can you help me out with this problem? can you hand me the exe?
kind regards
JJ
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.
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.
#1
André Arnaud de Cal...
293,245
Super User 2025 Season 1
#2
Martin Dráb
231,923
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
Share
Report
All responses (
Answers (
