Cannot resolve collation conflict

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

Transact-SQL (2000)

Cannot resolve collation conflict

Author

Topic

Tigger
Yak Posting Veteran

85 Posts

Posted - 2003-04-03 : 18:30:23

I had a lovely little stored procedure part of which joins two tables which live in different databases. It has been working quite happily for the last few months - until today

I ran it again today and I now get the following error message:

Cannot resolve collation conflict for equal to operation.

One of the databases was upgraded this morning and it seems that the collation has now been changed. Why? I don't know. Am I able to change it? No !! Grrr...

After hunting around on the internet I eventually found that I could force the collation of the column but know I get a syntax error and I can't see why..

UPDATE Unique_Number
SET
next_number = MU.next_number
FROM
Unique_Number U
INNER JOIN
MAD..UNIQUE_NUMBER MU
ON
U.table_name = MU.table_name COLLATE SQL_Latin1_General_CP1_CI_AS

Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'COLLATE'.

Please help me !! I've wasted most of the morning trying to sort this out and my good mood is rapidly disappearing ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-03 : 18:53:54

I'm not sure that you can do what you are trying to do, but to get around that I would suggest creating a temporary table that is a duplicate of UNIQUE_NUMBER but has the table_name values already collated. Then join on the temporary table. It would mean that you have more data to store, but if no one else on this forum can come up with a better solution then you're probably stuck with this solution.

SELECT table_name COLLATE SQL_Latin1_General_CP1_CI_AS, other columns...
INTO #MAD_UNIQUE_NUMBER
FROM MAD..UNIQUE NUMBER

UPDATE Unique_Number
SET next_number = MU.next_number
FROM Unique_Number U
INNER JOIN MAD..#MAD_UNIQUE_NUMBER MU ON U.table_name = MU.table_name

DROP TABLE #MAD_UNIQUE_NUMBER

Tara

Edited by - tduggan on 04/03/2003 18:54:54

Tigger
Yak Posting Veteran

85 Posts

Posted - 2003-04-03 : 19:09:29

Thanks for that suggestion.

Can you see whats wrong with this?

CREATE TABLE #temp
(table_name varchar(70) COLLATE SQL_Latin1_General_CP1_CI_AS, next_number int)

INSERT INTO #temp
(table_name, next_number)
SELECT table_name, next_number
FROM MAD..UNIQUE_NUMBER

I still get
Line 36: Incorrect syntax near 'COLLATE'.

I've been fiddling around with this for so long now I can no longer think straight

Tigger
Yak Posting Veteran

85 Posts

Posted - 2003-04-03 : 19:15:43

I'm even more confused now !

If I run
CREATE TABLE #temp
(table_name varchar(70) COLLATE SQL_Latin1_General_CP1_CI_AS, next_number int)

INSERT INTO #temp
(table_name, next_number)
SELECT table_name, next_number
FROM MAD..UNIQUE_NUMBER

on the first database I get
Line 36: Incorrect syntax near 'COLLATE'.

But if I run it on the MAD database its fine! Unfortunately the stored procedure can't run from the MAD database it has to run on the other one ... I soooo want to just go home

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-03 : 19:16:54

What version of SQL Server are you using? Only SQL Server 2000 allows collations to be set at the server, database or column level. Before SQL Server 2000, you could only specify the collation on the server level.

Tara

Tigger
Yak Posting Veteran

85 Posts

Posted - 2003-04-03 : 19:19:58

Its SQL 2000.

If I ever find out who changed the collation on the database ........

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-03 : 19:29:55

I am now at a loss for this one because it should work exactly the same on either server as long as they are both SQL Server 2000. Hopefully someone else will have an idea as to why it fails on one of your servers and not the other.

Tara

Tigger
Yak Posting Veteran

85 Posts

Posted - 2003-04-03 : 19:33:51

Thanks for your help.

Its good to know I'm not just going mad/stupid !

Not really sure where to go from here. I need to find a way round it somehow ....

Argyle
Yak Posting Veteran

53 Posts

Posted - 2003-04-03 : 20:48:12

Is the database with the syntax error in sql 7 compatibility mode maybe?

/Argyle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-04 : 12:39:11

This thread is pretty much closed now. He started a new one for this specific problem.

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=25120[/url]

Tara

Edited by - tduggan on 04/04/2003 13:15:50

Argyle
Yak Posting Veteran

53 Posts

Posted - 2003-04-05 : 10:25:18

Yea I noticed that after answering. Clicked on a link on the front page so I didn't notice the other thread :P

/Argyle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources