update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update query

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2014-03-28 : 15:47:17

Hi,

i got this error message when try to run my updateq query. Can some one please help.

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

===============
update dbo.Supplyitems
set supplierNo = a.t_otbp
from ttdpur400452 a
left join ttccom100700 b on b.t_bpid COLLATE database_default= a.t_otbp
where Supplyitems.supplierNo = b.t_seak

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-28 : 17:39:18

Try

update dbo.Supplyitems
set supplierNo = a.t_otbp
from ttdpur400452 a 
left join ttccom100700 b on b.t_bpid COLLATE database_default= a.t_otbp COLLATE database_default
where Supplyitems.supplierNo = b.t_seak

If that does not work, run this query to find the collation of the columns involved and make sure that you force the collations on the left and right hand side of the equals sign in the where clause to be the same using the COLLATE construct as in the example above:

SELECT OBJECT_NAME(Object_Id) AS TableName, name, collation_name
FROM sys.columns
WHERE OBJECT_NAME(object_id) IN ( 'ttdpur400452', 'ttccom100700')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-03-29 : 13:39:07

Also make sure to use the same collation. Forcing it in a SELECT statement will result to always Index scan

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources