Post reply

change collation of the temp db

  • February 15, 2006 at 4:34 am

    #171141

    Using MSDE2K

    getting the error

    'cannot resolve collation conflict for equal to operation'

    when a stored procedure is being run on a new databse.... looking around it seems the problem is to do with the tempdb not having the same collation as the user databases..... joins using temp tables

    can someone help me with how to change the collation of the tempdb to match the user databases ....

    or

    tell me how to install it in the first place correctly.... transferring the databases by using Backup Database.

    thanks

  • February 15, 2006 at 5:11 am

    #620968

    Hello,

    the tempdb database is created each time you start SQL Server and is based on master database collation. You need to change the collation of the master database and it should be ok.

     

     

  • February 15, 2006 at 7:43 am

    #621037

    but how do you change the master collation to match the others.... I have no idea on commands or anything

  • February 16, 2006 at 4:48 am

    #621305

    Not sure if it changed from SQL2K to 2K5 but the 2K5 doco shows tempdb being based on model (like all other user DB's) (from BOL -> The tempdb database is built every time SQL Server is started and has the same default collation as the model database. )

    If this holds true for SQL 2K, you should be able to modify model's collation.

    Also, I haven't tested it but I thought with SQL2K to change the collation of master required a re-install?

    Steve.

  • February 21, 2006 at 8:18 pm

    #622497

    I am trying to change model's collation

    ALTER DATABASE model

    COLLATE Cyrillic_General_CI_AS

    and have an error

    Server: Msg 3708, Level 16, State 5, Line 1

    Cannot alter the database 'model' because it is a system database.

    Could you suggest any workaround without re-install?

  • March 2, 2007 at 8:42 am

    #692865

    I know this is from over a year ago, but I've hit the same problem myself

    Anyway, the brutal fix for this is described here:

    http://support.microsoft.com/kb/325335

    ...or reinstall SQL Server using the right collation.

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

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