A SQL script I am writing is generating the error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_CI_AS" in the equal to operation.

I am not familiar with this error and don't know how to deal with it. I have been able to determine it is caused by the last IF check in the script as once I remove it the script runs fine. Can anyone help me figure out how to fix this issue? Here is my current code:

DECLARE @Source varchar(25);

SET @Source = 'DOD';
Source options are:

IF (@Source = 'DOD')
    USE DOD;




IF (@Source = 'SRAPROD')
  • 1
    Are you sure this error is coming from this isolated piece of code? This doesn't seem likely, can you share more of the code? – Aaron Bertrand Sep 12 '13 at 15:07
  • That is all the code I have written so far and as soon as I remove the last if statement it works fine. I can set the other if statements to look the same and they work fine. – user11512 Sep 12 '13 at 15:57
  • Ok, so what is your server's collation, what is the collation of SRAPROD, and what is the collation of the database where you're calling this code from? – Aaron Bertrand Sep 12 '13 at 16:01
  • 1
    Have you tried using COLLATE DATABASE_DEFAULT on your IF (@Source = '...')? You can put it on both sides of the compare "@Source COLLATE DATABASE_DEFAULT = 'SRAPROD' COLLATE DATABASE_DEFAULT". I don't see how this should be necessary, but you can try. – RLF Sep 13 '13 at 2:21
  • Sorry I have been sick for a few days and have not been able to update. I am not sure what the collation (or even how to check that) is for each DB. I will give your solution a try RLF and let you know how it goes. – user11512 Sep 13 '13 at 16:01

Well, that is interesting. I created your 3 databases using different collations for each database. I tried starting from different databases to see the results. Naturally I do not know which collations you used on each database, but apparently the SQL Server is (in fact) aware of this.

  • FIRST - SQL_Latin1_General_CP1_CI_AI
  • DOD - SQL_Latin1_General_CP850_CI_AS
  • SPRAGUEENERGY - SQL_Latin1_General_CP850_CI_AS
  • SRAPROD - SQL_Latin1_General_CP1_CI_AI

Depending on the database I start with (I tried all 4) some give collation errors and some do not. So... The answer is:


This raises no errors no matter which database I start with.

| improve this answer | |
  • I just tested this out and adding the collate to the if statements has correct the problem. Thanks. – user11512 Sep 13 '13 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy