May 27, 2014 at 11:13 am
We have a production server where the collation is set to Latin1_General_BIN at the server level. all Databases on the server are also set to Latin1_General_BIN
On our test server it is set to: SQL_Latin1_General_CP1_CI_AS at the server level.
On this test server I have set the databases that originate on the Latin1_General_BIN server to Latin1_General_BIN at the Database level. But the mismatch of a capital Q to a lower case q did not get caught while testing. Can anyone explain this?
Thanks
@QueryType VARCHAR(25),
IF @queryType='delete'
August 7, 2014 at 6:48 pm
Please try a quick Internet search:
I quickly found this here
The one on your test server was case insensitive (hence the CI portion in the name). That is no longer the case.
----------------------------------------------------
August 8, 2014 at 7:44 am
Ignore - should have re-read the question!
August 8, 2014 at 7:58 am
Just in case MMartin1's reference wasn't enough:
Changing the database default collation has no effect on existing columns in existing tables, only on new columns (columns in new tables created after the database collation change and new columns added to existing tables afterthe database collation change); all existing columns that need to use the new database default collation need to have their collation set individually.
Tom
August 8, 2014 at 7:58 am
If you change the collation of an existing database, that doesn't change the collation of all the columns in it. It only sets the default for newly created objects. Therefore if you already had databases that were SQL_Latin1_General_CP1_CI_AS and you changed them to Latin1_General_BIN, you wouldn't see much difference unless you also changed the collation of each table and column. You have to drop indexes, constraints and all sorts to do that. It's a world of pain. There are application out there that will generate scripts to do just that. I've found that they work quite well, but you'll want to inspect the scripts carefully before you run them.
John
August 8, 2014 at 1:05 pm
Another option if you wanted to have the collation changes apply to existing objects, you could migrate the data to a new database with the desired collation.
@QueryType VARCHAR(25),
IF @queryType='delete'
Depending on how @QueryType was populated in the past, it could have come as 'Delete' , which would resolve to true in the past. But running this script now would not succeed. You could upper case the variable and the comparison literal
IF upper(@QueryType) ='DELETE' ...
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply