Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Collation error - Execute SQL task

Answered Collation error - Execute SQL task

  • Thursday, February 07, 2013 3:25 PM
     
     
    Sign In to Vote
    0

    All

    While i am updating  records using Execute SQL Task, i am getting following error. Could you please let me know how can trace error causing rows?

    Error

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

    Thanks

     

All Replies

  • Thursday, February 07, 2013 3:30 PM
    Moderator
     
     Proposed Has Code
    Sign In to Vote
    0

    The collations are different, SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_As, in the equal operation.  Check your T-SQL where you are comparing two values, in this case VALUE1 = VALUE2, and change your code to specify the collation:

    WHERE 'VALUE1' COLLATE Latin1_General_CI_As = 'VALUE2' COLLATE Latin1_General_CI_As

    David Dye My Blog

    • Proposed As Answer by Russ Loski Thursday, February 07, 2013 4:04 PM
    •  
     
  • Thursday, February 07, 2013 9:33 PM
     
     
    Sign In to Vote
    0

    I used query directly on database just to check, it works fine, why not which execute sql task?

     
  • Friday, February 08, 2013 1:31 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Using the same query in the execute SQL task should also work.  The issue is that not all collations can be compared equally.  Using the COLLATE you can specify the collation at the query level.

    From your post I am guessing that you are attempting to compare values that are of different collations.  Using the COLLATE statement you are insuring that the comparison is done using identical collations.  Collation in SQL Server begins at the instance level, the collation of the server which includes the system databases, then the database level, the column level, and in this example the statement level.  The collation dictates how data is stored and compared and in this case the collations were disparate which prohibited them from being compared.

    David Dye My Blog