Thursday, February 07, 2013 3:25 PM
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?
Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_As in the equal to operation.
Thursday, February 07, 2013 3:30 PMModerator
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
I used query directly on database just to check, it works fine, why not which execute sql task?
Friday, February 08, 2013 1:31 AMModerator
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
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Thursday, February 14, 2013 3:00 AM