January 6, 2010 at 7:12 am
Having restored a SQL Server 2000 backup into SQL Server 2005, we are finding that nine columns spread across three tables have had their collations changed from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS. These nine columns are the only varchar/char columns in those three tables. All the other varchar/char columns in all the other tables still have their collation set to SQL_Latin1_General_CP1_CI_AS.
The 2005 server's collation is SQL_Latin1_General_CP1_CI_AS and the database's collation in SQL Server 2000 was SQL_Latin1_General_CP1_CI_AS.
This is giving us major hassles. Since this database is part of a vendor's product (and is also linking to Foxpro Tables, which I also need some help with), we're a tad hesitant to change things. We've been through three days of almost useless telephonic and email support from the vendor, and since this is tax/financial software and is the core of the business, we're getting slightly titchy.
Can a restore change collations like this? I wouldn't think so, but...
Thank you
January 6, 2010 at 7:17 am
GDI Lord (1/6/2010)
Can a restore change collations like this? I wouldn't think so, but...
Shouldn't be able to. A restore recreates the database as it was at the point it was backed up. To change a column's collation requires an ALTER TABLE... ALTER COLUMN statement.
Just out of paranoia, please run this:
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Can you take a fresh SQL 2000 backup and restore again?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2010 at 12:27 am
Thanks Gail. The DBCC CHECKDB reported no errors.
We got tired of waiting for the vendor and decided to risk it. After unsuccessfully trying to replicate the error on an unnetworked laptop, we backed up and restored the live database onto the laptop. We ran ALTER TABLE ... ALTER COLUMN ... COLLATE SQL_Latin1_General_CP1_CI_AS NULL and the relevant features in the product worked. We ran it on the live database and it appears to be working too.
One of my colleagues suggested that the program changed the collation when the server had a default collation of Latin1_General_CI_AS the first time we restored the database and ran the program. This sounds interesting, but I wonder why it wouldn't have changed it back to SQL_Latin1_General_CP1_CI_AS now that the server has been reinstalled with the default collation of SQL_Latin1_General_CP1_CI_AS.
On a more humourous note, we might just send the ALTER script in to the vendor.
Meh.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply