Re: Database collation error after moving to a remote sql server

Steve,

Thanks for the input. I think the phrase "This can be a complex operation"
may be something of an understatment...

I think I'm going to try plan B: Install a second instance of SQL 2000 onto
the remote server and set its default collation to the same as my SMS
database, then move my db to the new instance. I'll have to run the SMS Setup
Wizard again to point SMS at the new instance but hopefully all will be
well...

Since I have other non-SMS db's that I have to move from my original SMS\SQL
server I'll probably run into the same problem with them so the second SQL
instance offers a solution to my current and possible future woes.

Thanks again

Andrew S


"Steve Thompson" wrote:

> "Andrew S" <Andrew S@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:3F45CA58-3429-4C1D-B0CD-C37B910B8CFD@xxxxxxxxxxxxxxxx
> > I've recently my SMS 2003 database to a remote sql server (it was
> initially
> > installed on the Site server). Whilst it appears to be working I'm getting
> a
> > large number of errors from the SMS_Hierarchy_Manager with the following
> > content:
> >
> > Severity Type Site code Date / Time System Component Message ID
> Description
> > Error Milestone A01 27/10/2005
> > 15:42:13 CLUKSMS1 SMS_HIERARCHY_MANAGER 620 Microsoft SQL Server reported
> SQL
> > message 446, severity 16: [42000][446][Microsoft][ODBC SQL Server
> Driver][SQL
> > Server]Cannot resolve collation conflict for equal to operation. Please
> > refer to your SMS documentation, SQL documentation, or the Microsoft
> > Knowledge Base for further troubleshooting information.
> >
> > My sms database has a collation of Latin1_General_Cl_AS
> > My tempdb database and the new SQL server have a collation of
> > SQL_Latin_General_CP1_CI_AS
> >
> > I have tried changing the collation of my SMS database SMS_A01 using the
> SQL
> > command
> > ALTER DATABASE SMS_A01 COLLATE SQL_Latin1_General_CP1_CI_AS
> >
> > but it errors saying that
> >
> > Server: Msg 5075, Level 16, State 1, Line 1
> > The column 'SecurityObjInstances.ReadFlag' is dependent on database
> collation.
> > Server: Msg 5072, Level 16, State 1, Line 1
> > ALTER DATABASE failed. The default collation of database 'SMS_A01' cannot
> be
> > set to SQL_Latin1_General_CP1_CI_AS.
> >
> > Any ideas on what I should do now?
>
> Hmmm, that does not sound good. According to SQL Server BOL, changing the
> database collation does not change the collation of the tables...
>
> From BOL:
> Altering the default collation of a database does not change the collations
> of the columns in any existing user-defined tables. These can be changed
> with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:
>
> a.. The default collation for the database. This new default collation is
> applied to all columns, user-defined data types, variables, and parameters
> subsequently created in the database. It is also used when resolving the
> object identifiers specified in SQL statements against the objects defined
> in the database.
>
>
> b.. Any char, varchar, text, nchar, nvarchar, or ntext columns in system
> tables to the new collation.
>
>
> c.. All existing char, varchar, text, nchar, nvarchar, or ntext parameters
> and scalar return values for stored procedures and user-defined functions to
> the new collation.
>
>
> d.. The char, varchar, text, nchar, nvarchar, or ntext system data types,
> and all user-defined data types based on these system data types, to the new
> default collation.
> After a collation has been assigned to any object other than a column or
> database, you cannot change the collation except by dropping and re-creating
> the object. This can be a complex operation. To change the default collation
> for an instance of Microsoft® SQL ServerT 2000 you must:
>
> a.. Make sure you have all of the information or scripts needed to
> re-create your user databases and all of the objects in them.
>
>
> b.. Export all of your data using a tool such as bulk copy.
>
>
> c.. Drop all of the user databases.
>
>
> d.. Rebuild the master database specifying the new collation.
>
>
> e.. Create all of the databases and all of the objects in them.
>
>
> f.. Import all of your data.
> Steve
>
>
>
.

Relevant Pages

  • Re: Change field collation
    ... constraints and statistics on the columns before you can alter ... There are quite a lot of caveats and you use this script at your own risk. ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.programming)
  • Re: Changing collation -- URGENT
    ... You can use the following script to change the collations of your columns, ... duplicate primary key constraints create statements, ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.server)
  • Re: Case Insensitive lookup in DB
    ... Is your SQL Server installed with a case sensitive collation? ... And while you might be able to reset the collation of a database by using ...
    (microsoft.public.inetserver.asp.general)
  • Re: Database collation error after moving to a remote sql server
    ... > I've recently my SMS 2003 database to a remote sql server (it was ... > Server]Cannot resolve collation conflict for equal to operation. ... The char, varchar, text, nchar, nvarchar, or ntext system data types, ...
    (microsoft.public.sms.setup)
  • RE: New Paper: Microsoft SQL Server Passwords
    ... Unfortunately changing the collation to case sensitive means that all the ... stored procedures and direct sql queries need to be case sensitive as well. ... are checked on non-production database before proceeding. ... > Subject: New Paper: Microsoft SQL Server Passwords ...
    (Bugtraq)