CODEWITHSUNDEEP
sqlsql-server
Glen
Glen

Reputation: 830

SQL Server COLLATION ISSUE

I have table has field CHAR(3) collation set as database-default. Database default is SQL_Latin1_General_CP1_CI_AS

I run a query where I am dumping the result of a stored proc into a temp table where I declare the field in question as CHAR(3)

CREATE TABLE #TempTable (
    ...
    FieldAChar3 CHAR(3) NOT NULL
    ...
)

No mention of collation so I assume it uses the default!?

I do a union of my table and temp table on the field in question

SELECT Field1Char3 FROM Table1
UNION
SELECT FieldAChar3 FROM #TempTable

and I get a collation error of:

Msg 468, Level 16, State 9, Line 90
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the UNION operation.

Obvious I can apply the collate statement of the default setting

collate database_default or
collate SQL_Latin1_General_CP1_CI_AS

to the second union statement as a work around but why am I getting the error in the first place?

Is it possible the collation was not applied like an SQL Server bug. I can verify the collation was changed on the database prior to the creation of the table.

Upvotes: 1

Views: 918

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300489

Your tempdb and database have different collation settings. This often happens when servers are upgraded from a version using the older collation defaults.

You could create the temp table with the SQL_Latin1_General_CP1_CI_AS collation.

There are few differences between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS. They both use the same Locale (1033) and the same Code Page (1252).

The SQL_ Collations have been deprecated for some time and should be avoided if possible.

Upvotes: 2

Related Questions