August 4, 2009 at 9:32 am
The Gurus,
Could you please, tell me how to change the collation settings of a database to match that on the database server on which it is installed? The database in question uses SQL_Latin1_General_CP1_CI_AS and the database server uses Latin1_General_CI_AS. All other databases on the server uses Latin1_General_CI_AS. What is the best thing to do? My end user is requesting that I change the server collation to match his which is SQL_Latin1_General_CP1_CI_AS but all other server uses Latin1_General_CI_AS.
Many thanks in advance.
Regards,
Sahoong
August 5, 2009 at 2:51 am
Could someone look at this for me, PLEASE.
Many thanks.
August 6, 2009 at 7:19 am
If I understand your question, the end user wants you to change your server collation to SQL_Latin1_General_CP1_CI_AS. As far as I know, the server collation comes from the OS. I believe you would have to change the OS on the server to change it's collation. Which, I'm not quite sure why it matters. If your database has the same collation as his, then as far as I know you shouldn't have any issues. We have the same thing in our environment and it's working ok. The only issue comes in if we need to join to system tables because they will have the servers collation not the user database collation. And, if this happens you can change the collation in the query to be able to do the join.
You'll need to research this on your own to be certain though. I just googled it and found all kinds of articles.
I would ask the user what their reason is for wanting the collation changed. You should be able to prove out any issues he might have with collation by doing some testing. Then you'd know if you had to change it.
August 6, 2009 at 8:03 am
sahoong (8/5/2009)
Could someone look at this for me, PLEASE.Many thanks.
Hey there,
Good news, this is actually reasonably simple and straight forward. I had huge troubles with this but then i stumbled on the details below and all got smooth 😎
http://www.dbforums.com/microsoft-sql-server/998397-change-database-collation-quickly.html
http://seer.entsupport.symantec.com/docs/304556.htm
http://seer.entsupport.symantec.com/docs/293475.htm
Read the following and you will see that althjough they may not directly answer your particular issue, you can adapt the information and scripts to suite. Even though my origenal issue was with KVS i have gone on to successfully implement this 'style' of solution on many occassions.
Let me know if any questions. Have also attached a word file (again more geared to the KVS issue) but easily mod'ed.
Have fun.:hehe:
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 6, 2009 at 8:53 am
I have read many books and blogs on sql server. Everywhere this is written that we need to reinstall if need to change the collation setting. IN sql server 2005 they have combined collation, sorting and unicode... So a new installation is needed......... But this should cause data loss.. Oh I am confused
-Lucky
August 6, 2009 at 9:01 am
luckysql.kinda (8/6/2009)
I have read many books and blogs on sql server. Everywhere this is written that we need to reinstall if need to change the collation setting. IN sql server 2005 they have combined collation, sorting and unicode... So a new installation is needed......... But this should cause data loss.. Oh I am confused-Lucky
Was this in reply to my post?
I was talking about changin a datrabase collation and re-sorting the data. My advise would be to restore a backup as a test and then try the procedure.
If your talking about server collation, it doesnt get it from the OS, you set it at install time. To change is easy but then you have to rebuild sys dbs.
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 6, 2009 at 9:04 am
sahoong (8/4/2009)
The Gurus,Could you please, tell me how to change the collation settings of a database to match that on the database server on which it is installed? The database in question uses SQL_Latin1_General_CP1_CI_AS and the database server uses Latin1_General_CI_AS. All other databases on the server uses Latin1_General_CI_AS. What is the best thing to do? My end user is requesting that I change the server collation to match his which is SQL_Latin1_General_CP1_CI_AS but all other server uses Latin1_General_CI_AS.
Many thanks in advance.
Regards,
Sahoong
I'd find out why they need it. If it's questionable and has no real benefit, get it shut down with a document showing how much of a waste of time it is compared to whatever benefit they have 😀
Change collations
http://msdn.microsoft.com/en-us/library/ms177439(SQL.90).aspx
August 6, 2009 at 9:08 am
I had the same kind of request from users couple of months back to change Collation SQL_Latin1_General_CP1_CI_AS to Latin_general_BIN on a particular database.
Directly changing collation with Alter database command won't work. If you got columns with datatypes like nvarchar, nchar, ntext, text, varbinary etc you cannot change with Alter command.
Best solution I found was to create a new database with Latin_general_BIN collation and then migrate data from old database to new database. Take backup of old database, drop old database and rename new DB to Old database name. Luckily there is no data loss reported by users after migrating data to new version.
SQL DBA.
August 6, 2009 at 9:26 am
Hello sahoong,
To change the collation at database level you can do it with the following statement.
ALTER DATABASE MyDB COLLATE MyCollationDesired
To check the entire list of collations:
select * from fn_helpcollations()
Regards,
Victor Alvarez
http://sqlpost.blogspot.com
August 6, 2009 at 9:29 am
Sanjay this was a nice response
Also tell me when you tried to migrate the data from one collation to another... would not that gave you some issues?
-Lk
August 6, 2009 at 9:35 am
If you don't specifically pick a collation at install time, SQL Server Setup automatically detects the Windows system locale and selects the appropriate SQL Server collation. That's what I meant when I said it came from the OS. Sorry for any confusion. 🙂
---http://msdn.microsoft.com/en-us/library/ms144260.aspx
To identify your Windows system locale on computers that are running Windows Server 2003, click Regional and Language Options in Control Panel, and then click the Regional Options tab. In most cases, a computer runs the Windows system locale that matches the language requirements of the user. Therefore, SQL Server Setup automatically detects the Windows system locale and selects the appropriate SQL Server collation.
August 6, 2009 at 9:40 am
How can you skip collation setting when you are installing sql server 2005. Last but one option asks you to confirm collation setting........... I am confused Randy because of your post now 🙁
-Lk
August 6, 2009 at 9:51 am
Yes, you have to pick one...but by default you will get the one SQL Server detected from the Windows locale. Thus, the one that is set by the OS. Unless, you specifically select one that is different. Either way, you are correct, you have to pick one. Did I explain it any better that time??? Sorry blood sugar is getting low, I need lunch.....
August 7, 2009 at 1:43 am
andy russell (8/6/2009)
Did I explain it any better that time??? Sorry blood sugar is getting low, I need lunch.....
Hey Andy, I didnt know that. I've never seen the default offering any different from General_Latin_CI_AS so thought this was just a default (didnt know it was picked up from OS). Oh master i stand in thr shadow of your wisdom 😉
Going back to what Sanjay said earlier, that is exactly what i used to do but the solution i suggetsed yesterday is quicker. Did you read it Lucky?
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 7, 2009 at 6:58 am
Haven't had a chance to read it yet, but I hope to. Not sure it was wisdom, just Google and finding the link....:-) But I appreciate the vote of confidence. Anyway, I haven't had a lot of experience with collation issues, it just happened that I had the exact same issue a couple of weeks ago so I starting reading on it. And I just posted the info I found. Have a great weekend!!!:-D
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply