Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2012-11-26 : 10:47:13
|
| Here's our situation. Our Database is set to collation SQL_Latin1_General_CP1_CI_AS but our customer's Server is Latin1_General_CI_AS. So if any Stored procedure has a Temp Table missing the Collation specified, we get the Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" error.Our problem is that we can not find these errors in testing because on our Development Server, both our Server and our Database is SQL_Latin1_General_CP1_CI_AS.Does anyone have an idea how to address this - some way that we can change the temp db setting so it uses a different collation by default so that if we don't specify the collation, we will get the collation error? |
|
|
Elizabeth B. Darcy
Starting Member
39 Posts |
Posted - 2012-11-26 : 11:08:13
|
| Change the collation of the Model database using alter collation - see here http://msdn.microsoft.com/en-us/library/ms175835.aspxThen restart the server. Tempdb and any new databases you may create will have the new collation.________________________________________-- Yes, I am indeed a fictional character. |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-11-26 : 11:43:05
|
quote:
Originally posted by Elizabeth B. Darcy
Change the collation of the Model database using alter collation - see here http://msdn.microsoft.com/en-us/library/ms175835.aspxThen restart the server. Tempdb and any new databases you may create will have the new collation.
Thanks a lot. I think this will be our only option. I don't suppose there is a way of doing this without restarting - or that you can choose only temp tables called from a certain Database will have this Collation rather than temp tables from all databases having the same one.Also, if Databases have their own Collation and temp db collation comes from the Model Database, what does the Server Collation used for? |
|
|
|
Elizabeth B. Darcy
Starting Member
39 Posts |
Posted - 2012-11-26 : 15:01:58
|
| I don't know of a way to have temp tables created in different databases take on different collations by default. You can of course explicitly specify the collation of the columns when you create the temp tables - and your request would be honored regardless of the collation of the tempdb or the collation of the database.As to your question about the server collation, it is a hierarchy -- When you install SQL Server, the collation you specify becomes the collation of system databases including master, model and tempdb - the so-called server collation.- When you create a database, you can specify the collation, and if you don't it takes on the collation of model database.- When you create a table, you can specify the collation (of character columns) and if you don't, it takes on the collation of the database.- When you specify an expression or literal string you can specify a collation and if you don't, it takes on the collation of the database.Changing the server collation means changing the collation of the system databases including that of the master database. It is a rather complex process requiring you to drop user databases, rebuild the system databases (including master) etc. There is more information on this MSDN page and the "Rebuilding System Databases" link on that page: http://msdn.microsoft.com/en-us/library/ms179254(v=sql.105).aspx________________________________________-- Yes, I am indeed a fictional character. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-11-26 : 18:50:54
|
| Thanks it worked.SQL Server wouldn't let me change the Model DB Collation directly. I had to back it up, restore it under a different name, change the Collation on the copy, then backup and restore it over the Model DB. |
|
|
|
|
|
|
|
|
Collation Issue - change temp db Collation?