November 24, 2004 at 6:50 am
Quick Question Guru's
I have a database with the collation SQL_Latin1_General_CP1_CI_AS. I use this collation on many different databases on many sql servers. BUT! One of mw sql servers (test) for some reason is case sensitive. Is this set on the "server" level or the database level. Is this an easy change?
Any help would be greatly appreciated.
Shane
November 24, 2004 at 7:59 am
Collation is defined (beginning with SQL 2K) at the Server, Database, and even columnar-level.
Easiest fix (because it is your test server) is to dump the database and recreate it with the correct Collation and not accept the server default. If you cannot do this you can update the columns to be the correct collation (but will be a logistical nightmare)
Good Hunting!
AJ Ahrens
webmaster@kritter.net
November 24, 2004 at 8:06 am
Collation is set during installation of sql server and determines the collation for the system databases (eg master, tempdb) and the default for any created databases thereafter.
You can override the default collation when you create a database or table.
To change the default collation you have to rebuild the master database using the rebuildm.exe utility or reinstall sql server. In either case you will have to detach and re-attach or recreate your databases.
Far away is close at hand in the images of elsewhere.
Anon.
November 24, 2004 at 8:27 am
One thing to consider if you decide to change the collation on the database in question is that tempdb also has the system collation. If you create temporary tables, they will be case sensitive and you could run into collation conflicts.
I think if it was me, since its a test server, I'd reinstall SQL.
Steve
November 24, 2004 at 8:43 am
Thanks guys.
So just to clarify: It is the collation that makes the sql server case sensitive. Right?
I just have no idea how to make not be case sensitive.
November 24, 2004 at 9:46 am
Yes, collation sets case sensitivity among other things, character sets, etc...
In SQL_Latin1_General_CP1_CI_AS, the CI says "case insensitive". You can change the collation of a database, but the objects currently within the database will not change. You would have to change each object individually. This and the issue about temp tables have the system collation is why I suggested re-installing SQL. The default collation for SQL is case insensitive.
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply