1
Vote

Unable to resolve column level collations - on any table with XML column

description

I get an error on 4 of my tables which have (non indexed) xml columns when analysing and migrating - data does not copy.
 
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to resolve column level collations
 
The columns all have 'default' collation set on them. Any ideas?

comments

ghuey wrote May 31, 2011 at 7:55 PM

Hi,

Would it be possible to take this offline? If possible, can you script one of your tables and provide a couple rows of dummy data? You should be able to contact me via Codeplex.

Thanks,
George

kcshot wrote Jun 5, 2011 at 3:15 PM

In the end I had to remove all dependencies and all collations in order to set default collation on everything. I also had to remove/deactivate service broker, and amend all SP's to replace 'Select Into' with 'Create Table / Insert Into' statements.

Thanks to the help obtained at http://blog.sqlauthority.com/2009/10/19/sql-server-change-collation-of-database-column-t-sql-script-consolidating-collations-extention-script/

I needed to run the scripts generated using the examples provided, using the SQL Server 2005 Backward Compatability download:
http://download.microsoft.com/download/A/D/0/AD021EF1-9CBC-4D11-AB51-6A65019D4706/SQLServer2005_BC.msi
http://download.microsoft.com/download/f/7/4/f74cbdb1-87e2-4794-9186-e3ad6bd54b41/SQLServer2005_BC_x64.msi