[468] Cannot resolve the collation conflict between - Error

jzavala

I am attempting to copy stored procedures from one SQL 2008 R2 db to another on the same box. One db is using "Latin1_General_CI_AS_KS_WS" and the second is using "SQL_Latin1_General_CP1_CI_AS" collation. I have set the ignore collations flag under Project Options | Ignore | Collations, yet I am receiving an error below. Is this expected behavior or a bug? Is there a workaround?


The following error message was returned from the SQL Server:

[468] Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


Thanks in advance! :-)

~jzavala

peter.peart

Hi,

Thanks for your post. I haven't been able to replicate this I am afraid, so if you would like us to look into this further we would ideally need either SQL Compare snapshots or backups of your DB's to enable us to replicate the problem.

You can send these to support(at)red-gate.com with reference number F0047354 in the title.

Pete

jzavala

Pete, would it be possible setup a "web session" where you can see what I am doing and genereate the error?

~Juan

peter.peart

Thanks for your reply and sorry for the late reply from me, I was on holiday.

I can arrange a web session to see what you are doing provided you have a valid support and upgrades contract, but it probably won't tell me a huge amount. I'd need to repro the issue in house to confirm if it was a bug or not and then have any chance of getting it fixed by the development team.

Pete

jzavala

Pete,

Can you please send me a time and date of when you would like to setup a web session? Additionally, the content we are attempting to move contains sensative data and I am wondering what is it exactly that you need in order to replicate the issue.

We are running short on time and really need someone from redgate to help with this issue.

Please advise.
Juan

Brian Donahue

"Ignore collations" is probably not going to help. This error message usually happens when you try to join two columns with different collations. The only fix in that case is to explicitly state the collation in the join clause.

Would be interested in seeing the SQL trace from SQL Profiler when this error pops up. I can't imagine how a CREATE or ALTER PROC command would cause this sort of SQL violation.

peter.peart

Hi Juan,

If you want to mail support with the previous reference number given along with your license key we can look into setting this up providing you have a valid support and upgrades contract.

As I've said though and as Brian said, we probably aren't going to learn a huge amount just "watching" you perform the compare. We would need either SQL Compare snapshots of the DB's or possibly the sync script being generated.

The error being returned is directly from SQL Server itself because of the clash in collations. Like Brian said, it's normally when joining columns but the sync script, profiler trace or snapshots would enable us to see that because we can properly analyze what the tool is doing. It could either be an issue with how SQL Compare is attempting to script out the sprocs in the new environment, or it could be because of how the database has been designed and there will be very little we can do about. Either way, I'm afraid the web session won't really give us a lot to work work with other than seeing the error message pop up; it won't enable us to debug what's going on.

Pete