SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Collation Issue - change temp db Collation?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 11/26/2012 :  10:47:13  Show Profile  Reply with Quote
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

United Kingdom
39 Posts

Posted - 11/26/2012 :  11:08:13  Show Profile  Reply with Quote
Change the collation of the Model database using alter collation - see here http://msdn.microsoft.com/en-us/library/ms175835.aspx

Then restart the server.

Tempdb and any new databases you may create will have the new collation.



________________________________________
-- Yes, I am indeed a fictional character.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 11/26/2012 :  11:43:05  Show Profile  Reply with Quote
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.aspx

Then 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?
Go to Top of Page

Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/26/2012 :  15:01:58  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 11/26/2012 :  18:50:54  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000