June 2, 2008 at 4:40 am
Hi there
I've recently installed SQL2005 x64 on a new server. I didn't min the collation and now the collation on the new server is : Latin1_General_CS_AS. On the old server it is : SQL_Latin1_General_CP1_CI_AS.
Is this a problem ?
If yes how can we change this ?
The purpose of the SQL server is a Navision database
Many thanks
Jeffrey
JV
June 2, 2008 at 5:26 am
Hi JV,
For one thing, the CS in the new collation name means it's case sensitive, so unless all the code in your DBs is totally consistent, you probably will get errors. Eg SELECT Date FROM Dates is no longer the same as SELECT date FROM DATES. If can afford downtime on the server I'd suggest reinstalling with the correct collation, although there may be simpler, less tedious ways of dealing with it than the one I know of, which is specifying the collation in all queries etc. There are other issues too, such as tempdb problems (http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=7b4c9796-66d0-4ed2-b19d-bef6bb1e3e1d#a7b4c9796-66d0-4ed2-b19d-bef6bb1e3e1d) and sort order, though I don't know much about that so maybe someone else can expand.
Cheers
Cath
June 2, 2008 at 6:32 am
You could change the collation with the command
alter database collate ... (see BOL)
However, I don't know what the impact is of this command. AFAIK, It requires an exclusive database lock.
I don't know how long this command will run, so try this on a testenvironment first
Wilfred
The best things in life are the simple things
June 2, 2008 at 7:10 am
You need to rebuild the system databases to change the Sql server collation. You can use the below command to perform the same,
start /wait setup.exe /qn INSTANCENAME=”MSSQLSERVER” REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=”XXXX” SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
The below link deals with the same but you need specify the SQLCollation parameter as specified in the above command. http://sql-articles.com/index.php?page=articles/rebuildsysdb.html
[font="Verdana"]- Deepak[/font]
June 2, 2008 at 9:03 am
I also had same problem with collation settings.
The error message was conflict with collation settings.
Many of them suggested me to Rebuild Master Database with New collation settings, but few experts told me to
Reinstall with required collation settings.
I backed up my databases. Reinstalled server with selecting collation option as below.
"SQL_latin1_General CP1_CI _AS(Case insenstitive - Accent Sensitive)"
Check option AI
In the drop down list select "1252 Character insensitive".
This should solve your problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply