How do I change the Server Collate thing?

[JNeave]

We wrote a SP to compare and update two database structures (devel to live).

But now were getting a "Cannot resolve collate conflict for equal to operation."
for WHERE db1.Table_Name = db2.Table Name

We had a look at the server collate settings.
It turns out the the devel db is SQL_LATIN1_GENERAL_CP1_CI_AS
And the live system is LATIN1_GENERAL_CI_AS

They might be the exact spellings for those settings.

The point is, how do we fix this? We have no idea...

Cheers,

Jim.

 

[evatype2]

Well if Microsoft hasn't completely trashed the original Sybase core the command would be:
ALTER DATABASE dbname COLLATE collatename

Don't remember if you need to reboot, it has been years. You can also set COLLATE at different levels, but I think this is a needless pain. I prefer, when humanly possible, to pick the same collate across related databases.

 

[karluk]

According to BOL, Eva's suggestion should work. However, I bet that you will also have to alter the tables that already exist in the db. Otherwise they will most likely retain the collation assigned to them when they were created.

ALTER TABLE table_name1 COLLATE collatename
ALTER TABLE table_name2 COLLATE collatename

etc.

 

[JNeave]

Will try, thanks.

According to what we've found in BOL

"You have to rebuild the master and user databases"
"Shutdown SQLServer - Run the rebuilding tool"

Erm. No thankyou....

Cheers,

Jim.