Today I migrated an exisiting set of tables from one database, to a new database, to isolate their work.
After copying all the stored procs, views, tables and data, one of my stored procs wouldn't run.
Everytime I tried to execute it, I got the error:
"Cannot resolve collation conflict for equal to operation."
It transpires that this error can be caused by many different problems, butt he main one being that the collation types for the columns being joined in the sql statement is different.
In my instance this was not the case, but by telling the columns to use the databases default collation on either side of the join fixed the problem - e.g.
SELECT table1.*, table2.* FROM table1
INNER JOIN table2 ON table1.column1 COLLATE DATABASE_DEFAULT = table2.column1 COLLATE DATABASE_DEFAULT
Ref: http://wyerarch.blogspot.in/search?updated-max=2009-06-29T01:12:00-07:00&max-results=7&start=14&by-date=false
After copying all the stored procs, views, tables and data, one of my stored procs wouldn't run.
Everytime I tried to execute it, I got the error:
"Cannot resolve collation conflict for equal to operation."
It transpires that this error can be caused by many different problems, butt he main one being that the collation types for the columns being joined in the sql statement is different.
In my instance this was not the case, but by telling the columns to use the databases default collation on either side of the join fixed the problem - e.g.
SELECT table1.*, table2.* FROM table1
INNER JOIN table2 ON table1.column1 COLLATE DATABASE_DEFAULT = table2.column1 COLLATE DATABASE_DEFAULT
Ref: http://wyerarch.blogspot.in/search?updated-max=2009-06-29T01:12:00-07:00&max-results=7&start=14&by-date=false
Thanks for blogging this, saved me a TON of time today!
ReplyDelete