none
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operatio

    Question

  • I hit this error when I tried to do something like:

     

    Code Snippet

    create table #tempTable (ID int IDENTITY(1,1) NOT NULL ,
       column1 varchar(200) NOT NULL)
    INSERT INTO #tempTable
    Values('test')
    create table tempTable (ID int IDENTITY(1,1) NOT NULL ,
       column2 varchar(200) NOT NULL)
    INSERT INTO tempTable
    Values('test')

    Select * from #tempTable t
    inner join tempTable p
    on t.ID = p.ID
    where t.column1 = p.column2

     

     

     

    After a thorough search on sysobjects and syscolumns, I found the collation problem on database "temp" is set to "Latin1_General_CI_AS"

     

    since it's an system db, I cannot alter. My db also cannot alter because some SP is encrypted, though I cna de-crypt it.

    Is that anyway to solve it by running the script??

     

    I tried to do something like:

    start /wait setup.exe /qb INSTANCENAME= {my MSSQL 2005 insatnce name} REINSTALL=SQL_Engine REBUILDDATABASE=SAPWD= {my sa password} SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

     

     

    but i get no error at Summamry.txt:

    Setup succeeded with the installation, inspect the log file completely for status on all the components.

     

    while at "SQLSetup0011__Core.log":

     

    Error: Action "LaunchPatchedBootstrapAction" threw an exception during execution.  Error information reported during run:
    "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe" finished and returned: 0
    Aborting queue processing as nested installer has completed
    Message pump returning: 0

     

    Anyone have any idea?

    Tuesday, May 20, 2008 2:57 AM

All replies

  • To change the collation of the system databases you need to either rebuild the system databases or perform uninstall/reinstall of Sql Server. I would go with the rebuild option. But before proceeding with that you need to script all the logins, jobs, take a backup of all your dts packages and to be on the safer side take a full backup of all the user databases as well.

    You can make use of the below command to perform the rebuild.
    Code Snippet

    start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation>

    After rebuilding check the collation of the system databases and see if they are as desired. Then run the scripts to recreate all the logins, jobs etc..

    - Deepak

    Tuesday, May 20, 2008 3:55 AM
    Moderator
  •  gankh wrote:

     

    I tried to do something like:

    start /wait setup.exe /qb INSTANCENAME= {my MSSQL 2005 insatnce name} REINSTALL=SQL_Engine REBUILDDATABASE=SAPWD= {my sa password} SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

     

     

    but i get no error at Summamry.txt:

    Setup succeeded with the installation, inspect the log file completely for status on all the components.

     

    while at "SQLSetup0011__Core.log":

     

    Error: Action "LaunchPatchedBootstrapAction" threw an exception during execution.  Error information reported during run:
    "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe" finished and returned: 0
    Aborting queue processing as nested installer has completed
    Message pump returning: 0

     

    Anyone have any idea?

     

    I did try, even mentioned in my post. You didnot notice??? Anyway, thxs. Any idea?

    Tuesday, May 20, 2008 4:13 AM
  •  gankh wrote:
     gankh wrote:

     

    I tried to do something like:

    start /wait setup.exe /qb INSTANCENAME= {my MSSQL 2005 insatnce name} REINSTALL=SQL_Engine REBUILDDATABASE=SAPWD= {my sa password} SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

     

     

    but i get no error at Summamry.txt:

    Setup succeeded with the installation, inspect the log file completely for status on all the components.

     

    while at "SQLSetup0011__Core.log":

     

    Error: Action "LaunchPatchedBootstrapAction" threw an exception during execution.  Error information reported during run:
    "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe" finished and returned: 0
    Aborting queue processing as nested installer has completed
    Message pump returning: 0

     

    Anyone have any idea?

     

    I did try, even mentioned in my post. You didnot notice??? Anyway, thxs. Any idea?



    I did notice it but rebuilddatabase=1 was missing and hence I thought you tried without that option. After performing that are you able to login to Sql Server and check the collation for system databases ?
    Tuesday, May 20, 2008 4:23 AM
    Moderator
  •  

    sorry, typo during composing. No changes happen. The setup wizard run half way and exit. I showed the log files as above.
    Tuesday, May 20, 2008 4:29 AM
  •  

    Are you using the correct media for the rebuild? You must supply the same version/edition media for the instance. I.e. you cannot use developer edition media on standard edition instance (and vice versa).

     

     

    Tuesday, May 20, 2008 4:54 AM
    Moderator
  • You can mention COLLATION with the column definition.
    e.g

    Code Snippet

    create table #tempTable (ID int IDENTITY(1,1) NOT NULL ,
       column1 varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL)
    INSERT INTO #tempTable
    Values('test')
    create table tempTable (ID int IDENTITY(1,1) NOT NULL ,
       column2 varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL)
    INSERT INTO tempTable
    Values('test')


    Tuesday, May 20, 2008 4:59 AM
  •  oj wrote:

     

    Are you using the correct media for the rebuild? You must supply the same version/edition media for the instance. I.e. you cannot use developer edition media on standard edition instance (and vice versa).

     

     

     

    Same edition.

    Tuesday, May 20, 2008 5:13 AM
  •  Abd_Sheikh wrote:
    You can mention COLLATION with the column definition.
    e.g

    Code Snippet

    create table #tempTable (ID int IDENTITY(1,1) NOT NULL ,
       column1 varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL)
    INSERT INTO #tempTable
    Values('test')
    create table tempTable (ID int IDENTITY(1,1) NOT NULL ,
       column2 varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL)
    INSERT INTO tempTable
    Values('test')


     

    I know, but the but there are too many SPs involved, sicne normally develoeprs does not use default_collate.

     

    I even try to collate my db to Latin one, but too many of them. not suitable for production upgrade.

    Tuesday, May 20, 2008 5:15 AM
  • Since rebuild did not work, I am afraid you have no other choice but to reinstall SQL Server with updates. Make up a plan and post it for us to review.

    The following article offers some interesting option which may work for you: borrow the model database from another system with the desired collation (tempdb is created from model db):

    http://blogs.msdn.com/b/john_daskalakis/archive/2008/12/04/9174867.aspx

    Related article:

    http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    • Edited by Kalman Toth Friday, September 28, 2012 5:47 AM
    Tuesday, May 20, 2008 6:04 AM
  •  SQLUSA wrote:
    Since rebuild did not work, I am afraid you have no other choice but to reinstall SQL Server with updates. Make up a plan and post it for us to review.

     

     

    I will try to figure it out first, it re-install is the last resort, thanks.

    Tuesday, May 20, 2008 10:36 AM
  • Hi

    You can use the reliable code that a friend (Alex Baker) published at CodeProject:

    SQL Server 2000 Collation Changer. (Free source code)

    http://www.codeproject.com/KB/database/ChangeCollation.aspx?fid=257710&df=90&mpp=25&noise=3&sort=Position&view=Quick&fr=76&select=2176451

    Alain

    Note: Always keep a backup of your database when you make major changes.

    Thursday, April 02, 2009 3:22 PM
  • In SQL Server 2005 you should be able to do this -

     

    Select * from #tempTable t
    inner join tempTable p
    on t.ID = p.ID 
    where t.column1 COLLATE DATABASE_DEFAULT = p.column2 COLLATE DATABASE_DEFAULT

     

    I found this here.

     

    Thursday, April 22, 2010 6:34 AM