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

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-03-09 : 05:58:17

Hi

I have one SP that runs fine on 2 MS SQL servers. However, if I try to create the same SP on another MS SQL Server, I get:

"cannot resolve collation conflict for like operation" error.

So why is it that the same SP runs fine on 2 servers, but fails on another??

regards

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-09 : 06:11:21

Hi,
Its becuase of different collations of servers..
while comparing specify the collation
select * from
t1
join
t2 on t1.col1 = t2.col2 collate database_default

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-03-09 : 06:20:14

I tried to add "collate database_default" to the Inner Joins of my SP. However, I still get the error.

Rather than changing the SP, Can I change the Server settings (because I would like to keep the same SP for different servers)

regards

Kristen
Test

22859 Posts

Posted - 2006-03-09 : 12:52:53

Are you accessing data from TWO servers in a single statement e.g.

SELECT *
FROM MyLocalTable AS L
JOIN MyRemoteServe.dbo.MyRemoteTable AS R
ON R.ID = L.ID

If not then the likely cause is that you have a CREATE TABLE (permanent table, #temporary table, or @table variable), in which case adding COLLATE statements to all the CHAR/VARCHAR/TEXT column definitions will fix the problem. But you won't be able to use:

SELECT *
INTO #MyTempTable
FROM MyRealTable

but that is bad practice anyway, better to pre-Create the table.

"collate database_default"

Note that you may need to specify explicitly what you want (i.e. the collation of your previous servers), rather than relying on a default!

"Can I change the Server settings"

I've done it once and vowed never again!

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=how+to+change+Collate+on+the+server

Kristen

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-03-09 : 13:21:44

I've had some problems with this aswell, especially after moving a database from one server to another using script. What I did was just to edit the collation for each and every field in the tables using Ent. Manager. It's a lame way to do it but I couldn't get those scripts I found to work...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources