| Author |
Topic |
|
roger_james_jr
Starting Member
7 Posts |
 Posted - 2005-04-14 : 06:18:59
|
| Hi,I am wondering where collation settings for a catalog are stored. The reason being, I am having conflicts, and I would like to remove the collation setting for my database, and have it use the DATABASE_DEFAULT. Thats all well and good - I can use alter database and set it to the database server's default, however if the database server's default changes, then I would prefer my database did not have any explicitly set collation. For example, if I create a new database, and dont set collation, then check its properties in enterprise manager, it displays nothing for collation, which is good, and ultimately what I am trying to achieve - the ability to set the database catalog collation back to this nothing setting.thanksRoger |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-14 : 07:21:59
|
how do you do that??if i create a new db and leave it's collation blank or to server default it displays the server default in properties of the db.and how do you change the server collation?? i've read here many times it's adviseable to reinstall the server than to change it's collation manually.Go with the flow & have fun! Else fight the flow  |
 |
|
|
roger_james_jr
Starting Member
7 Posts |
Posted - 2005-04-14 : 07:50:44
|
| ok, well to change the collation of a catalog to match that of the server, I do this: USE masterALTER DATABASE MyDb SET SINGLE_USER WITH ROLLBACK IMMEDIATEDECLARE @DatabaseCollation VARCHAR(100)SELECT @DatabaseCollation = CONVERT(VARCHAR(100), SERVERPROPERTY ('collation'))EXEC ('ALTER DATABASE MyDb COLLATE ' + @DatabaseCollation)ALTER DATABASE MyDb SET MULTI_USER WITH ROLLBACK IMMEDIATEIf you want to change the server's collation, you need to do this:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_8w8p.aspRoger. |
|
|
|
jason
Posting Yak Master
164 Posts |
Posted - 2005-04-14 : 15:30:09
|
| Even if that's possible, which I don't think it is, setting a database and/or server's collation to nothing would be detrimental to the server's performance since collation defines how the server stores and sorts character data.You would be better off explicitly specifying the database's collation instead of setting it to the default.I'm a light weight yak, so maybe Rob or someone could give us a hand on this one. |
|
|
|
roger_james_jr
Starting Member
7 Posts |
Posted - 2005-04-14 : 19:47:50
|
quote:
Originally posted by jason
Even if that's possible, which I don't think it is, setting a database and/or server's collation to nothing would be detrimental to the server's performance since collation defines how the server stores and sorts character data.You would be better off explicitly specifying the database's collation instead of setting it to the default.I'm a light weight yak, so maybe Rob or someone could give us a hand on this one.
No, I dont want to re-set the collation of the server, just set the collation of my database to be 'not specified', so it just uses that of the server that all. |
|
|
|
jason
Posting Yak Master
164 Posts |
Posted - 2005-04-15 : 10:52:51
|
quote:
No, I dont want to re-set the collation of the server
quote:
just set the collation of my database to be 'not specified'
quote:
just uses that of the server
If you want the database to have a collation that matches that of the server, then change the database collation to default. If you want the database collation to be independent of the server, then explicitly define the collation.In other words, 'not specified' is not an option. At least, it's not documented. There are other work arounds for mixed collation environments though. Perhaps if you were to tell us what those problems were in more detail we could help you. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2005-04-15 : 11:23:26
|
| Further to what James is saying, my uderstanding is as follows:Create a database - if you explicitly use a collation, that database uses it, otherwise it uses default.That collation is now how the data is stored - change server collation, the database collation will ALWAYS stay what it was, UNTIL you EXPLICITLY change it (per the SQL scripts already mentioned in above posts) .... the collation is part of how the data is stored, and cannot be dynamically deteremined from the current server collation - it is a "at point of creation" activity. At least, that's how I've always understood it...HTH*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
roger_james_jr
Starting Member
7 Posts |
Posted - 2005-04-15 : 21:06:58
|
quote:
If you want the database to have a collation that matches that of the server, then change the database collation to default.
Jason, thanks - this is what I would like to be able to do - set the collation to default, so not a specific collation, but back to default. |
|
|
|
|
Collation