Wednesday, March 7, 2012

Collation Conflict

You can create a database with a different collation to the server collation. This is one issue you will face in case database collation is different from the server collation.

image

Then let us create a table with few data,

image

Let me use temp table to use this data.

image

So you are using temp table and actual table. (This is a hypothetical scenario just to present this idea).

Last query will return following error.

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Danish_Norwegian_CI_AS" in the equal to operation.

This is due to the temp database has the server collation, so the workaround will be at the time of creating temp table, you have to explicitly mention the collation.

image

1 comment:

  1. LinkedIn SQL Server Professionals


    The other issue you will face is with variables especially with a case sensitive server collation.
    e.g. in the scenario of CS server and CI database the following code will fail:

    DECLARE @iTest integer;
    SET @iTest = 7;
    SELECT @itest;

    SELECT statement will fail because no variable @itest. CS master database will require all variable to match the case of the declaration, even in CI database.

    Posted by Carl Weibgen

    ReplyDelete