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


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?


ghuey wrote May 31, 2011 at 7:55 PM


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.


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: