Re: Collation problem

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 12:00:50 -0500

Rob,

Unfortunately changing the server collation isn't going to help you much at
this point -- as you noticed, your databases won't be updated. There is,
AFAIK, no good way to change the collation of an entire database, due to the
fact that every object maintains the database's collation -- rules,
defaults, columns, etc. The easiest way to change collation of a database,
IMO, is to script out the DDL via EM, re-create an "empty" version using the
script (be sure to change collation on all the objects to whatever you
want), and then DTS the data in from the old database.

As TEXT columns, rules, and defaults become deprecated, this will luckily be
much easier in the future, but until then we're kind of stuck... 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rob Meade" <robert.meade@NOSPAMubht.swest.nhs.uk> wrote in message
news:%23BSv0VL%23EHA.2180@TK2MSFTNGP10.phx.gbl...
> Hi all,
>
> Ahh, the on going saga of moving databases...
>
> Ok - having installed SQL Server 2000 on the new server and copied across
> approximately 10 database I've since learnt that we did NOT use the
default
> collation on the original server, instead we have case sensitive, accent
> insensitive, we've looked into it a bit to decide whether we want to leave
> it as it is - but this is not really what we want to do.
>
> So....I had a looky in the BOL, and it suggests the server collation can
be
> changed by rebuilding the master database, what this doesn't say (until
you
> start doing it) is that all user databases will be lost...this is a bit of
a
> problem...
>
> So - can anyone tell me the best approach - we need to change the
collation
> for the 'server' (not just one or two databases) to something else, and
> ideally without having to recreate all of the databases again that we've
> moved across.
>
> Some help/advice/info would be appreciated - kinda feeling the fool here
at
> the moment - only plus side is that the requirement wasn't documented -
but
> still it needs sorting..
>
> Cheers
>
> Rob
>
>

Relevant Pages

  • Re: How do I change the default collation string of an installation
    ... different collation name in order to be able to accept the character set ... the DTSs successfully copy the Hebrew letters when I copy data with OSQL ... refers to specific databases and uses the destination database's ... OSQL refers to the remote server and destination server ...
    (comp.databases.ms-sqlserver)
  • Re: Different Collation designator Settings
    ... You can have databases with different collations on SQL ... That wasn't possible on SQL Server 7. ... values in temporary tables while assuming that tempdb has the same collation ...
    (microsoft.public.sqlserver.odbc)
  • Re: Different Collation designator Settings
    ... You can have databases with different collations on SQL ... That wasn't possible on SQL Server 7. ... values in temporary tables while assuming that tempdb has the same collation ...
    (microsoft.public.sqlserver.connect)
  • Re: Different Collation designator Settings
    ... You can have databases with different collations on SQL ... That wasn't possible on SQL Server 7. ... values in temporary tables while assuming that tempdb has the same collation ...
    (microsoft.public.sqlserver.server)
  • Re: Different Collation designator Settings
    ... You can have databases with different collations on SQL ... That wasn't possible on SQL Server 7. ... values in temporary tables while assuming that tempdb has the same collation ...
    (microsoft.public.sqlserver.setup)
Quantcast