December 23, 2007 at 11:16 pm
Hi,
When I am migrating stored procedure from SQL Server 2000 to SQL Server 2005, I am getting the following error message.
/**********/
Msg 468, Level 16, State 9, Procedure sp_ATSR_SwiftAckNaksReport, Line 36
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation.
/**********/
Please find the script of the stored procedure in the attachment.
Kindly Help me in resolving this issue..
Regards,
Ezhilan
December 23, 2007 at 11:43 pm
Is the collation of the 2 servers different?
It looks like one is case sensitive and one is case insensitive. Is that deliberate, or should both servers be the same?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 24, 2007 at 12:02 am
Yes..you are correct. In one server it is Case sensitive and in other it is not. It is deliberate. How can we resolve this issue?
Regards,
Ezhilan
December 24, 2007 at 12:47 am
Did you copy the database from the other server, or just the proc?
The server's default colation only affects the system databases and new databases created on that server. Databases copied from elsewhere keep the collation that they were created with
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 24, 2007 at 2:18 am
I am just copying the procedure. Not the the database.
Regards,
Ezhilan
December 24, 2007 at 3:15 am
OK, then it's most likely because the server's collation differs from the databases, or the database has tables in it with different collations.
Check the collation of the DB that you're running this in, see if its the same as the server's. Check that the collations are what you want.
You can force a collation when comparing strings of different collations with the COLLATE keyword.
WHERE Column1 = Column2 COLATE SQL_Latin1_General_CS_AS (or whatever collation you want)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 24, 2007 at 3:22 am
Thank you...Gail Shaw. Its working now.
Regards,
Ezhilan
December 27, 2007 at 8:24 am
Hi,
we are using COLLATE in expressions only if we can not change the collation as this seems to be very slow... I think you should keep that in mind.
Regards,
Jan
November 1, 2010 at 10:18 am
Hi, Gail.
Can I have full update SQL?
November 1, 2010 at 11:50 am
Huh? This thread is 3 years old. What are you asking for?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply