Cannot resolve collation - ERROR!

Hello all,

I seem to get the following error when I join two tables from two separate databases.

[[Cannot resolve collation conflict for equal to operation.]]

Using

SELECT DATABASEPROPERTYEX('AAA', 'COLLATION'),
SELECT DATABASEPROPERTYEX('BBB', 'COLLATION')

returns exactly the same answer therefore the default collation order in both databases is the same. Why do I still get this error then?!

I know that you can change it for each individual table but isn't there a better solution?

Your help would be appreciated.Didn't specified which version of SQL is using, if its SQL 2K then you can change COLLATION using ALTER Database .... ALTER TABLE... refer to books online for more information.

If its version 7 then using REBUILDM utility to rebuild the master database for similar collation settings.|||Originally posted by Satya
Didn't specified which version of SQL is using, if its SQL 2K then you can change COLLATION using ALTER Database .... ALTER TABLE... refer to books online for more information.

If its version 7 then using REBUILDM utility to rebuild the master database for similar collation settings.

I am using SQL Server 2000.

The problem is that altering the database to set the collation order to be the same as the other one is what I have done but I still get the same error.

I am not keen on altering the collation order for every table/column hence my original question on this forum.

Your help would be appreciated.|||Refer to this DBJournal (http://www.databasejournal.com/features/mssql/article.php/1587631) article for more information.|||hi,

you might specify the collation directly like this

:
join table on fieldname COLLATE ... = fieldname COLLATE ...
:
and use the same collation for both fields

or have a look at bol "COLLATE"

markus|||Originally posted by msieben
hi,

you might specify the collation directly like this

:
join table on fieldname COLLATE ... = fieldname COLLATE ...
:
and use the same collation for both fields

or have a look at bol "COLLATE"

markus

Thanks for your help but as I said, I am not keen on using collate on every column I happen to use. Mind you, thank God MicroSoft introduced this new command in SQL Server 2000.

I am still trying to figure out why I keep getting the error even though both databases have the same default collation order!|||Originally posted by Crespo-n00b
Thanks for your help but as I said, I am not keen on using collate on every column I happen to use. Mind you, thank God MicroSoft introduced this new command in SQL Server 2000.

I am still trying to figure out why I keep getting the error even though both databases have the same default collation order!

Probably, we could help you more if you'll post the JOIN in the forum.
I got similar error when in a stored procedure I've created a "Table" type variable and used it in a join... all this happened in one database. The problem was solved by creating the "Table" type variable with the current database's collation order.

Hope this helps!

Best regards,
Botond