Re: Database collation error after moving to a remote sql server
- From: "Steve Thompson" <stevethompson@xxxxxxxxxxxxx>
- Date: Thu, 27 Oct 2005 11:25:31 -0400
"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
.
- Follow-Ups:
- Prev by Date: Re: Site Info Lost
- Next by Date: Re: Site Info Lost
- Previous by thread: Re: Site Info Lost
- Next by thread: Re: Database collation error after moving to a remote sql server
- Index(es):
Relevant Pages
|
