March 22, 2004 at 4:19 am
I downloaded the script sp_CompareDB
http://qa.sqlservercentral.com/articles/articlesexternal.asp?articleid=519
I started off by comparing two databases on the same server, one is a backup / restore of the other.
I am getting the following error message:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
I don't understand this as collation in both databases is exactly the same:
SQL_Latin1_General_Cp1_CI_AS
Any ideas on how I get this SP working would be appreciated.
I hope to use this SP to eventually compare differences between a Development and a Production Server to make sure that all scripts for promoting changes are in place
March 22, 2004 at 5:54 am
Check if the sp uses any temp table (#) and if so, check the collation of the tempdb database.
If it is different with db that you try to compare, then there is the problem.
March 22, 2004 at 8:21 am
The user databases have Collation Name:
SQL_Latin1_General_Cp1_CI_AS
TempDb has a collation name:
Latin1_General_CI_AS
I don't know how this happened as installation just accepted the defaults. How would I go about bring collation in line?
March 23, 2004 at 6:30 am
Latin1_General_CI_AS is the Prefered Coallation for SQL2000 I think the install gets it it from the Windows Coalltion settings. SQL_Latin1_General_Cp1_CI_AS is for backward compatability. Latin1_General_CI_AS is probably your server coallation.
You could (I don't know how safe this is) back up your temp db fron the Production server and restore it on your Dev server or ..
Use
ALTER DATABASE database {COLLATE < collation_name >}.
Haven't tried any of these options myself so I am totally open to correction.
March 23, 2004 at 7:18 am
I did not make myself clear. The two collation examples I gave above were on the same server.
March 23, 2004 at 7:55 am
ALTER DATABASE Userdb COLLATE Latin1_General_CI_AS.
Will change the user db's - You can't change the tempdb because it is a system db
March 24, 2004 at 6:10 am
Thanks for that. I managed to change the collation on two small databases and everything worked fine so this is obviously on the right track.
However when I tried to change the collation on the target database I got the messages below.
I imagine that I have to change these individual items somehow. Advice would be appreciated - also are there any risks changing these collations?
ALTER DATABASE kbusertrial COLLATE Latin1_General_CI_AS
Server: Msg 5075, Level 16, State 1, Line 1
The object 'CK TblTrades EI_indicator' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'fnSearchCompanies.OrganisationName' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'fnSearchCompanies.CountryCode' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'fnSearchCompanies.City' is dependent on database collation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'kbusertrial' cannot be set to Latin1_General_CI_AS.
April 13, 2004 at 12:14 pm
I am having this same problem, any help would be appreciated.
Nick
April 14, 2004 at 3:40 am
Nick, I am still stuck on this one. Hopefully someone will be able to help.
Stefan
April 14, 2004 at 3:53 am
I had the same problem when we upgraded from SQL Server 7 to 2000, the install for 2000 used the default collation but the backups we restored from 7 used the old default from SQL 7. This means that any tempory tables created will use the 2000 default collation which will cause problems with compares to the tables in your database
To change the collation on tempdb you need to rebuild the master using ReBuildM.exe. Be sure to backup all your databases as you will lose them and will need to restore them again after running ReBuildM.exe from the tools\binn folder.
Andy
April 14, 2004 at 9:40 am
I haven't a clue how I got these collation differences but I am not too keen on using ReBuildM.exe. Presumably I would lose all my DTS packages.
I decided to try eliminating the problem objects. The first was a constraint which actually came from the Access resize wizard and which I don't want - so removed it. The only other object was a function which I dropped, successfully changed the collation and then recreated it.
I then successfully ran sp_CompareDB but found it only compares tables not Procedures or DTS packages. So it's not going to be much good to me after all as it is mainly procedures and DTS Packages which I need to monitor.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply