Collation Problem

Question

Collation Problem

gbd77rc

Ten Centuries

Points: 1031
More actions
November 5, 2010 at 5:12 am

#231302

Hi All,
Let me first outline my environment and then ask the question to my problem.
I restored a SQL 2005 database with SQL_Latin1_General_CP1_CI_AS collation set to a SQL 2008 R2 box with SQL_Latin1_General_CP1_CI_AS collation set. The backup upgraded the database correctly.
Here is the output from sys.databases
masterSQL_Latin1_General_CP1_CI_AS100
tempdbSQL_Latin1_General_CP1_CI_AS100
modelSQL_Latin1_General_CP1_CI_AS100
msdbSQL_Latin1_General_CP1_CI_AS100
WSLasDBSQL_Latin1_General_CP1_CI_AS100
As you can see they all have the same collation and database version.
Now here is my problem if I use DECLARE table variable and run these commands
DECLARE @Packages TABLE (
PackageName VARCHAR(50),
Description VARCHAR(255))
INSERT INTO @Packages
SELECT 'RN WS', 'RN Test 1'
UNION
SELECT 'RN WS NFR', 'RN Test 2'
DELETE dbo.package WHEREname IN ( SELECT PackageName FROM @Packages )
I get
Msg 468, Level 16, State 9, Line 62
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
If I switch out the @Packages with #Packages then it works fine.
CREATE TABLE #Packages (
PackageName VARCHAR(50),
Description VARCHAR(255))
INSERT INTO #Packages
SELECT 'RN WS', 'RN Test 1'
UNION
SELECT 'RN WS NFR', 'RN Test 2'
DELETE dbo.package WHEREname IN ( SELECT PackageName FROM #Packages )
Now my question is why is there a difference? I am running the query in SSMS manually.
The original SQL 2005 server does have it collation set to Latin1_General_CI_AI which is different to the error collation (notice the AI/AS). All I can think of is that originally this database was restored from another SQL box (SQL 2000 I think) which did have the collection set Latin1_General_CI_AS. So where is this set in the database, its not in the normal place as the database properties think it is SQL_Latin1_General_CP1_CI_AS?
Regards
Richard....
http://www.linkedin.com/in/gbd77rc

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

I would expect the table variable to default to the database collation or maybe the user but the temp table to default to the tempdb collation - I've had this issue before with tempdb having a different collation to the user database.

Check the collation of the collumn being compared and see if it's the same as the database. I guess it is otherwise the temp table would give the error.

Is the database in compatibility mode? That could have odd effects.

what happens if you try a select * into #a from tbl then compare that with the temp table and table variable

How about creating the table variable while connected to another database

CHeck the collation on your connection too - right click on your login and select connection properties.

Cursors never.
DTS - only when needed and never to control.

gbd77rc Ten Centuries Points: 1031 More actions · Answer 2

Hi All,

After some more digging around I have isolated it to the fact when the database was originally created they had mix collations in the table creation scripts (these are no longer in existance so I am assuming this, and we all know what that could mean :-)). I have now look at the individual collation on every column in all tables in this database. They are of mix collation. Some are NULL so they will inherit the default one, some are Latin1_General_CP1_CI_AS, some are SQL_Latin1_General_CP1_CI_AS. Luckly we don't use temp tables in this application or else we would have had errors before now.

So during our move of the database (the main reason why I picked up on this) from old hardware to new I am altering all the columns to the same collation as the database. So yes it was a mix collation problem, but why it worked from CREATE TABLE instead of DECLARE is something to think about for the future.

Thanks for you help.

Regards

Richard...

http://www.linkedin.com/in/gbd77rc

WayneS SSC Guru Points: 95460 More actions · Answer 3

gbd77rc (11/5/2010)
Hi All,
After some more digging around I have isolated it to the fact when the database was originally created they had mix collations in the table creation scripts (these are no longer in existance so I am assuming this, and we all know what that could mean :-)). I have now look at the individual collation on every column in all tables in this database. They are of mix collation. Some are NULL so they will inherit the default one, some are Latin1_General_CP1_CI_AS, some are SQL_Latin1_General_CP1_CI_AS. Luckly we don't use temp tables in this application or else we would have had errors before now.
So during our move of the database (the main reason why I picked up on this) from old hardware to new I am altering all the columns to the same collation as the database. So yes it was a mix collation problem, but why it worked from CREATE TABLE instead of DECLARE is something to think about for the future.
Thanks for you help.
Regards
Richard...

FYI: table variables get their collation from the database that the current connection is in. Temporary tables get their collation from the tempdb database.

Just to make sure: you are creating the temporary table with a CREATE TABLE statement, and not with a SELECT * INTO # FROM statement? The SELECT/INTO will get the columns (and their collations) from the table(s) specified in the FROM clause, overwriting the database collations.

If you step through the code, you're getting the error on the DELETE command, correct?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

gbd77rc Ten Centuries Points: 1031 More actions · Answer 4

Hi

Yes it is on the DELETE statement. I always use CREATE TABLE, never use SELECT INTO as there have been historically issues with that syntax. I must admit that was Sybase a long time ago and I have got out of that habit just to make sure I don't get bit again :). Normally I don't specify the collation either so that it will pick what the database is set to, which in turn should be what the server (tempdb) is set to.

In the end I specified the collation that was expected in the table variable and all works now.

Thanks for your help.

Richard...

http://www.linkedin.com/in/gbd77rc