Database Collation

  • Unable to alter the collation to Latin1_General_CP1_CI_AS

    Steps followed:

    1. Open Sql Query Analyzer.

    2. enter: alter database AmeyDB Collate SQL_Latin1_General_Cp1_CI_AS

    msgs Received

    Server: Msg 5030, Level 16, State 2, Line 1

    The database could not be exclusively locked to perform the operation.

    Server: Msg 5072, Level 16, State 1, Line 1

    ALTER DATABASE failed. The default collation of database 'AmeyDB' cannot be set to SQL_Latin1_General_CP1_CI_AS.

    Can anybody help me out.

    Thanxs in Advance

    Amey Naware

  • Someone else has a DB connection open i.e. Query Analyzer or an instance of Enterprise Manager.  Make sure there are no connections open to that database and try again



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • Ok, thanxs.

    But can admin work on server i.e ent. mgr running on SQL Server?

    Regards.

    Amey

  • Beware of changing the database collation - SQL 200 very helpfully does NOT change the collation of individual columns when you do this - they all remain as the old database collation and then as you add new columns (or of course columns in new tables) they get the new collation so unless you are careful you will end up with mixed collation problems in the same database.

    You can use the ALTER COLUMN command to change the collation of existing columns but if there are any indexes on the columns these will first need dropping and then will need to be recreated after the collation change

    I have a Micky Mouse bit of VB 6 code that writes out a SQL script to do a full collation change (yes I have had this problem too!) but it would be supplied unsupported if you are interested ....

  • Check out

    Knowledge Base Article Number:  Q711843

    HTH,

    Mark Owens

     

  • Hi,

    Could you send me the program that generates the script?

    thanks!

    reydeval@gmail.com

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

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