Server collation and case attributes
-
We have SQL Server 2000 on two different servers, "production" and "test". It was our intention that they be set up identically, but now, many months later, I discovered these differences via sp_server_info:
sp_server_info
-------------------------------
IDENTIFIER_CASE
production: SENSITIVE
test : MIXED
COLLATION_SEQ
production: charset=iso_1 collation=Latin1_General_BIN
test : charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52
How do I change the test environment to make it consistent with production? What things might "break" if I do this?
Thanks in advance. -Neal
-
since you dev-server is less strict than your prod server, there might be problems at dev-side.
on the other hand, if there were to occur problems, the developers would have to have them adjusted before migratin to production anyway.
Since you're at it , why not reinstall the dev-server so it matches your prod settings 100% ?
Plan it and keep dev-downtime in mind !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi,
you might simply change the server collation of the development server. That will change the collation of the system databases, but not the collation of your user databases. The collation of the server mainly has an impact on new databases and on user objects that were created in the system databases. Each user db has a collation setting of its own, that is completely separate from the server's collation setting.
HTH karl
Best regards
karl -
Collation affects alot of things ... some of the more notable things, order by and group by clauses (resultant query data may appear in a different order). You can change the 'collation of a user database with the alter command - beware if the database is large it will take some time (it's dropping and recreating all of your indexes, amongst other things, in the background. Also if you perform cross database queries with different collations you'll have to add some addition DML clauses to your SQL to deal with this as well. You're best bet is to probably backup your existing databases/users/etc for development and install everything from the ground up. Restore the databases with their original collation, DETS the databases to new databases with the correct collation, drop the original databases, rename the new databases. Yes, it sounds like a lot to do but you'll probably be better off in the long run doing the extra work once now instead of ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
And it gets a lot more complicated if you've got tables with dublicate primary keys after the collation change, say you have a pk on a varchar field that is case sensitive, with rows in upper and lower, and you move to case insensitive....
Best regards
karl
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply