Alter database/Collation question

Question

Alter database/Collation question

Randy Doub

SSCertifiable

Points: 5322
More actions
December 16, 2011 at 2:15 pm

#252170

I have a server with collation Latin1_General_Bin. I created a new db on this server from a backup and then ran Alter database mynewdb COLLATE SQL_Latin1_General_CP1_CI_AS. There's a table in the db with column name usrID having value 'Admin'.
I can select * from table where usrid = 'Admin' but I can't select * from table where usrid = 'admin'.
Shouldn't I be able to do that? The column name is no longer case sensitive. Shouldn't the data also be case insensitive?
So then I tried creating a new tmp table that matched the old, selected out the data from old to new, deleted from old, then inserted back the data from new to old.
And I still can't select * from table where usrid = 'admin', but if I select * from new tmp table where usrid = 'admin' I get the "Admin" row.
Am I misunderstanding something about changing the collation of a user database? Do I have more steps that need to be taken?
Thanks all.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

yeah, you have to change the collation of each individual column that is char/nchar/varchar/nvarchar as a seperate suite of steps...man it's a lot of work, especially when some of the columns have indexes(ie index on LASTNAME?), or default constraints (ie DEFAULT('N')), or check constraints ie(CHECK MyCol ='Y' or mycol ='N')

in those cases, you have to drop teh index/constraint, change the collation, and put the index/constraints back.

that can be a lot of work.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Randy Doub SSCertifiable Points: 5322 More actions · Answer 2

Well... that's not going to happen. Too many opportunities for a mistake. The app in front of this database imports text data and formats paychecks for laser printing. No mistakes allowed.

So i guess the best option would be to script the db and build it on another server with the new required case insensitive collation and use SSIS to move the data over.

This is a vendor app, and the latest version REQUIRES SQL_Latin1_General_CP1_CI_AS. They didn't care before and that's why it's on the same binary db server as the payroll system.

Thanks much.

Lowell SSC Guru Points: 323518 More actions · Answer 3

it's not horrible to do, i've thrown some time at it a couple of times, and a simple search can find you someone elses script they already wrote to change SQL server Collation;

I can give you my sample code i did, but it's not bullet proof yet.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Randy Doub SSCertifiable Points: 5322 More actions · Answer 4

We have lots of sql servers here (only the 1 binary). Plenty of places this database could move to.

But I'll do the search and check it out.