Matching collation bet SQL2005 server & user db

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Matching collation bet SQL2005 server & user db

Author

Topic

palmoswince
Starting Member

3 Posts

Posted - 2010-01-31 : 03:56:39

Hi all,

Collation topic looks to pop up here and there but can't really find a similar scenario that i'm currently facing.

This is my scenario
a) My previous SQL2kSP4 was running on SQL_Latin1_General_CP1_CI_AS
b) I did a db dump on all my user db.
c) Uninstall SQL2k
d) Install SQL2005SP2 (same machine) but selected the wrong collation (Latin1_General_CI_AS instead fo SQL_Latin1*)
e) Restore my user db into SQL2005
f) So now my SQL2005 server collation is different from my user db.

What would my options be to get my SQL2005 server into SQL_Latin1_General_CP1_CI_AS collation after backing up db & other objects (user, jobs, etc)
a) Reinstall SQL2005, select the right collation and just restore the db & other objects?
b) Rebuild master using the setup command (specifying the correct collation) and just restore the user dbs?
c) others?

Thanks!

-JT-

Kristen
Test

22859 Posts

Posted - 2010-01-31 : 05:16:41

I use the (c) routine (in fact my application DB is a different collation to the Servers it runs on).

The server collation only matters if you create/alter Varchar/Text columns. The default collation for a new column is the Server collation. This includes any TEMP tables that you create (@TableVariable or #TempTable), and also any new tables you add, or columns that you add/change.

We always explicitly state the COLLATION on all Varchar/Text columns in CREATE/ALTER table statements (including @TableVariable and #TempTable) so that our DB will run on any server, regardless of collation.

The only other time the issue arises is when you compare data between databases where the columns have different collations.

If you only have the one DB running on your own server, and have no intentions of adding any others, or selling you "solution", then this may be a PITA that you would prefer to avoid! in which case (a) or (b) will probably suit you better (sorry, I don't know the pros/cons of A vs. B)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources