Collation conflict problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Collation conflict problem

Author

Topic

alanlambert
Starting Member

26 Posts

Posted - 2005-06-30 : 04:28:51

I get the error message 'Cannot resolve collation conflict for equal to operation.' when I run the following query (abbreviated for ease) which attempts to link two tables, one of which is on a linked server:

DECLARE @ProjectCode varchar(10)

SET @ProjectCode = 'DETO006'

SELECT dl.ref2, ca.AttributeName
FROM oas_docline dl LEFT JOIN DEGWLONSRV4.Icorp.dbo.Cons_Attribute ca
	ON dl.ref2 = ca.AttributeId 
WHERE dl.el2 = 'P' + @ProjectCode AND dl.doccode = 'TIMESHEET' 
	AND ca.AttributeTypeId=2
GROUP BY dl.ref2, ca.AttributeName

The relevant columns in the tables are defined as follows:

oas_docline:

el2 - varchar(72)
ref2 - varchar(32)

DEGWLONSRV4.Icorp.dbo.Cons_Attribute:

AttributeId - varchar(20)
AttributeTypeId - int
AttributeName -varchar(50)

I realise that this has to do with different collation being used on the two servers: the collation on the main database is SQL_Latin1_General_CP850_CI_AI whereas the collation on database on the linked server is SQL_AltDiction_CP850_CI_AI; both servers are SQL 2000.

Is there a way around this?

Many thanks for your help.

Alan

mr_mist
Grunnio

1870 Posts

Posted - 2005-06-30 : 05:19:02

Force the collation in the join, or change the collation on one of the two tables.

To do it in the join you would use the syntax

JOIN ... ON yourcolumn COLLATE collationname = yourothercolumn COLLATE collationname

-------
Moo. :)

alanlambert
Starting Member

26 Posts

Posted - 2005-06-30 : 05:43:28

Many thanks for the help. As I can't change the collation on either of the tables I've tried forcing the collation in the join:

DECLARE @ProjectCode varchar(10)

SET @ProjectCode = 'DETO006'

SELECT dl.ref2, ca.AttributeName
FROM oas_docline dl LEFT JOIN DEGWLONSRV4.Icorp.dbo.Cons_Attribute ca
	ON dl.ref2 COLLATE SQL_Latin1_General_CP850_CI_AI =
		ca.AttributeId COLLATE SQL_Latin1_General_CP850_CI_AI 
WHERE dl.el2 = 'P' + @ProjectCode AND dl.doccode = 'TIMESHEET' 
	AND ca.AttributeTypeId=2
GROUP BY dl.ref2, ca.AttributeName

Now I get the error: "Incorrect syntax near 'COLLATE'"

Even if I remove the first Collate (as the column is already using the correct collation) I still get the error.

Could you tell me what I've done wrong now?!

Alan

mr_mist
Grunnio

1870 Posts

Posted - 2005-06-30 : 06:05:08

Not really, as that code seems fine.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources