collation difference

Question

collation difference

maertean esaeasner

Ten Centuries

Points: 1162
More actions
May 12, 2005 at 10:04 am

#102524

Hi Folks,
I have discovered an error on one of my sql server machines. There is a clash of collation between the msdb database and the collation setting on the instance of SQLServer. Since I cannot just alter the collation setting on the database would anyone have any feedback on altering the collation setting of this db. I also need to change the collation of the columns as some of them are set to the new collation. Is the best way to achieve this by writing a query to output a sql command which can then be run to update the declaration of the col. i.e.
SELECT 'ALTER TABLE ' + sysobjects.name + ' ' +
...
Thanks in advance.
M

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 1

Unless collation has been specifically set for the columns, they would be defaulting to the database collation. You might find that after you change the database collation, that all you columns will have changed collation as well.

Just use ALTER DATABASE database COLLATE new_collation

Notes ripped from BOL (see ALTER DATABASE):

Before you apply a different or new collation to a database, ensure the following conditions are in place:

You are the only one currently using the database.
No schema bound object is dependent on the collation of the database.
If the following objects, which are dependent on the database collation, exist in the database, the ALTER DATABASE database COLLATE statement will fail. SQL Server will return an error message for each object blocking the ALTER action:
- User-defined functions and views created with SCHEMABINDING.
- Computed columns.
- CHECK constraints.
- Table-valued functions that return tables with character columns with collations inherited from the default database collation.
Altering the database collation does not create duplicates among any system names for the database objects.
These namespaces may cause the failure of a database collation alteration if duplicate names result from the changed collation:
- Object names (such as procedure, table, trigger, or view).
- Schema names (such as group, role, or user).
- Scalar-type names (such as system and user-defined types).
- Full-text catalog names.
- Column or parameter names within an object.
- Index names within a table.
Duplicate names resulting from the new collation will cause the alter action to fail and SQL Server will return an error message specifying the namespace where the duplicate was found.

Julian Kuiters
juliankuiters.id.au

maertean esaeasner Ten Centuries Points: 1162 More actions · Answer 2

Thanks Julian,

But I tried that yesterday and it doesnt work, because msdb is a system database it won't allow me to just use the 'alter database...' command. Some columns are defined with the old collation, I found this out by generating SQL Script for the database... Anything else I should do?

M

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 3

Unfortunately, you cannot change collation of existing columns without exporting data, droping column, recreating columns with new collation and importing data back.

This is a nasty situation. If I were you, I would - on different machine - install clean sql server with the same collation as your server, apply the same service pack and hotfixes, stop the server and store msdb data files. Now I would pull all data out of your msdb (script out jobs, alerts, operators, save dts packages). Then I would detach bad msdb, and attach stored msdb files with the same collation as other system dbs. The last step would be restoration of jobs, alerts etc. Having never had to do that, I cannot guarantee that this will work. Maybe rebulding master db (all other system dbs are rebuilt with specified collation as well) or reinstallation of the whole server could be more convenient to you. Also check that msdb is the only system db with different collation.

maertean esaeasner Ten Centuries Points: 1162 More actions · Answer 4

Hi Martin,

Thanks for the reply, but I've tried a few things, one of which was to alter the columns and change the collation. I could do that for all the columns except the ones that are declared 'text'. And I have tried to detach the msdb database but am not allowed to due to it being a system database.. I really dont know what I will do to figure this out because it is a live system and there are a lot of other live applications across the country dependant on these databases. Any other guidance is greatly appreciated.

Thanks

M

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 5

In order to detach and attach msdb you must stop SQL Server and run it with trace flag 3608 (skip recovery of all dbs except master) - from command line switch to binn directory of the instance and run sqlservr -m -c /T3608. So you cannot do that without some downtime. Then stop the server and start normally as a service and check that everything is ok (no errors in log and that msdb has dbid 4 in sysdatabases table). But consider everything carefully before you start and backup as much as you can.