April 20, 2006 at 6:04 pm
Hi I have restored a database (8gig) that has a collation of SQL_Latin1_General_CP1_CI_AS into a database server that has a default collation of Latin1_General_CI_AS.
I just wondering if i'm goining to have any issues?
Are there any compatable collations in MS SQL Server?
Thanks for your help, Warwick.
April 20, 2006 at 9:27 pm
You will have issues if the SP has temp table or declared table variables having character fields not defined with the COLLATE DATABASE_DEFAULT option. As these tables are created in memory, they will take on the tempdb collation, which in your case is Latin1_General_CI_AS.
When you try to compare character fields with different collations (as in a join or a where clause), you will receive an error similar to this:
Server: Msg 446, Level 16, State 9, Line 7
Cannot resolve collation conflict for equal to operation.
To overcome this, you have two options:
1. redefine the table definitions, eg:
Instead of this: declare @tabA table (id int, colA varchar(10))
Change it to this: declare @tabA table (id int, colA varchar(10) COLLATE DATABASE_DEFAULT)
2. rebuild the master database to change the collation for the SQL Server.
April 20, 2006 at 11:19 pm
Thank you for your reply!
Do you know if there is such a thing as compatible collations?
Thanks Warwick.
April 23, 2006 at 10:29 pm
There is a 'compatibility collation', which is somewhat different to either SQL Collation or Windows Collation. This knowledge base article has more about it, if you're interested:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply