SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Collation conflict between SQL 2012 SP1 and SQL 2005 SP3 ?


Collation conflict between SQL 2012 SP1 and SQL 2005 SP3 ?

Author
Message
mtassin
mtassin
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7398 Visits: 72521
I have the following error coming back at me

Msg 468, Level 16, State 9, Line 243
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Except that the server collations are all SQL_Latin1_General_CP1_CI_AS
The database collations are SQL_Latin1_General_CP1_CI_AS
The Table collations are SQL_Latin1_General_CP1_CI_AS
and the specific column in question is using SQL_Latin1_General_CP1_CI_AS as its collation as well.

This worked fine with SQL 2008 R2 SP1 SQL_Latin1_General_CP1_CI_AS and SQL 2005 SP3, is this something to do with SQL 2012 SP1?



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97535 Visits: 38988
Unfortunately, we can't see what you see so it is hard to know what to tell you.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3297 Visits: 6497
Mark, did you copy and paste the error message directly from SSMS? If yes, then they are indeed different. Just look closely, the one is Latin1_General_CI_AS, and the other one is SQL_Latin1_General_CP1_CI_AS (note the CP1 part).

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97535 Visits: 38988
Jan Van der Eecken (2/15/2013)
Mark, did you copy and paste the error message directly from SSMS? If yes, then they are indeed different. Just look closely, the one is Latin1_General_CI_AS, and the other one is SQL_Latin1_General_CP1_CI_AS (note the CP1 part).


Even more of a difference than that, one is a windows collation and the other a SQL collation.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3297 Visits: 6497
Lynn Pettis (2/15/2013)
Jan Van der Eecken (2/15/2013)
Mark, did you copy and paste the error message directly from SSMS? If yes, then they are indeed different. Just look closely, the one is Latin1_General_CI_AS, and the other one is SQL_Latin1_General_CP1_CI_AS (note the CP1 part).


Even more of a difference than that, one is a windows collation and the other a SQL collation.


w00t

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
mtassin
mtassin
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7398 Visits: 72521
I'm aware of the differences between what is shown. However looking at the actual collations at the server, database, table and column levels they were always SQL_Latin1_General_CP1_CI_AS

In this case it turns out to somehow have been a busted linked server of all oddities.

Rebuilding the linked server that the queries were passing to resolved the collation conflict that was not there.

I had tried building it using SQL Native Client 10 and 11. 10 had worked when it was a 2008R2 server, and the client was still present on the server, 11 was working when I made actual connections to the SQL 2005 SP3 server.

When I changed the client to use MSOLEDB instead of the Native Client the collation error problems went away.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3297 Visits: 6497
In other words, you are sorted?

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
mtassin
mtassin
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7398 Visits: 72521
Yes, I wanted to post above what the fix was in case anybody else runs into this.

If you're going from 2012 SP1 to 2005 SP3 (probably from 2012 of any flavor to 2005 of any flavor) and get a collation conflict that you are sure isn't there (because you have inspected everything from point a to point z).

Try deleting the linked server and recreating it. Try each of the most common connection libraries as well.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search