Search

Wednesday, November 24, 2010

Microsoft SQL Server Error 5030

So, if you are trying to change the collation of a database and getting error 5030, this is much likely because you cannot change the collation of a database when it is in Multi_User mode. In this case, you should try to run the following query.

-- the following line sets the database to "Single User" mode

ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- the following line sets the new collation

ALTER DATABASE DBNAME COLLATE COLLATIONNAME

-- the following line sets the database back to "Multi User" mode

ALTER DATABASE DBNAME SET MULTI_USER

DBNAME: Database name

COLLATIONNAME: New collation’s name. E.g.: Latin1_General_CI_AI

4 comments:

  1. It worked 100%.
    Thanks

    ReplyDelete
  2. It can not done if Server location is: Japanse, Database collation is Japanse_Unicode_CI_AS but backup database is "SQL_Latin1_General_CP1_CI_AS"

    I have tried to convert and do not get any good result

    ReplyDelete