Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Posts
    26

    Unanswered: Collation error after OS Upgrade

    We have a server which we needed to upgrade the OS on it from NT 4 Server to Windows 2000 Server.

    As the system disks are seperate from the data disks. We did a fresh install of Windows, then installed SQL Server, applied service pack version 3, and then restored the master database.

    From Query Analyser everything appears fine. I can query all the databases and tables, see them all, the web applications talk the database fine so all the users have recreated OK.

    However .... if we try and look at the database through management console we get the following error.

    A connection could not be established to ....

    Reason: Cannot resolve collation conflict for like operation..

    This happends logged in locally to the machine as administrator as well as connecting remotely. Does anybody have any idea what is happening?
    Nicky Jones

  2. #2
    Join Date
    Feb 2003
    Posts
    26

    Re: Collation error after OS Upgrade

    A little more info - I have run a trace to see what happens when enterprise manager connects, and the last thing it tries to run (which generates the collation error) is:

    exec sp_MSdbuserpriv N'ver'
    Last edited by njjones; 03-22-04 at 07:08.
    Nicky Jones

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You either have to support all collations or force your clients to reinstall SQL Server with the collation you chose.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Feb 2003
    Posts
    26
    I am sorry but I don't understand what you mean by this?

    Originally posted by Satya
    You either have to support all collations or force your clients to reinstall SQL Server with the collation you chose.
    Nicky Jones

  5. #5
    Join Date
    Feb 2003
    Posts
    26
    Even more info:

    Looking at the query and playing a bit the following query if I run the following query against the master database, I get the "Cannot resolve collation conflict for like operation"

    declare @mode nvarchar(10)
    set @mode = N'ver'

    if lower(@mode) like N'serv%'
    select 1 as result


    NB if this run against another database (on the same server) it runs fine.


    HELP!
    Nicky Jones

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    What is the default collation choose when you installed SQL Server?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Feb 2003
    Posts
    26
    Unfortunately I didn't run the install - I asked the person running it to take the default collation which was offered.

    Originally posted by Satya
    What is the default collation choose when you installed SQL Server?
    Nicky Jones

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You can run SERVERPROPERTY (Collation) to get it.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Feb 2003
    Posts
    26
    Thanks for that:

    The server collation is set to: Lation1_General_CI_AS
    The master database collation is set to: SQL_Latin1_General_CP1_CI_AS

    I guess this is the root of the problem. Is there anyway I can change the server default collation without re-installing SQL Server?
    Nicky Jones

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •