Collation 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

General SQL Server Forums

New to SQL Server Programming

Collation problem

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-30 : 23:19:51

Dear Experts,
I'm getting a different problem here that....
i've a function with me, it is working fine with me, and working well with different servers. but for some servers, the fuction is giving error saying that "cannot resolve the conflict with collation error."

i've checked the server collation settings. those are same for both working server and not working server. i'm sure that problem is regarding collation. compatibility level also same.

how can i rectify this?

Vinod
Even you learn 1%, Learn it with 100% confidence.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-30 : 23:41:02

Did you check collation for dbs involved?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 01:10:56

Are you using temporary tables in function? If yes, there could be a chance that collation of tempdb is different in the server. One method to fix this is to check for places where collation error happens in function and manually set collation of columns of involved using COLLATE <collation setting>

ex:
suppose when using below query

SELECT
   TableA.col1,
   TableB.col2
FROM TableA
INNER JOIN TableB
ON TableA.col3 = TableB.col3

you get error like:-

Msg 468, Level 16, State 9, Line 1
Cannot resolve collation conflict between 'Latin1_General_CI_AS' and 'SQL_Latin1_General_CP1_CI_AS' in equal to operation

you can fix this using COLLATE clause as:-

SELECT
   TableA.col1,
   TableB.col2
FROM TableA
INNER JOIN TableB
ON TableA.col3  COLLATE Latin1_General_CI_AS = TableB.col3 COLLATE Latin1_General_CI_AS

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-31 : 01:21:45

it is same for different databases.....

Vinod
Even you learn 1%, Learn it with 100% confidence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 01:43:30

What's the error message you are getting?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources