none
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

    Question

  • I have faced this error when i am going to run my SQL.

    Msg 457, Level 16, State 1, Line 1
    Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

    Can someone help me on this....


    The SQL code:

    declare @strSQLExp varchar(1000)
    
    if exists(select * from sys.databases where name = 'STDAT1')
      begin
        set @strSQLExp = 'select 1 as CompanyID,CompanyName from [STDAT1].[dbo].[Company]'
      end
    else
      begin
        set @strSQLExp = 'select 1 as CompanyID,''New Company'''
      end
    
    if exists(select * from sys.databases where name = 'STDAT2')
      begin
        set @strSQLExp = @strSQLExp + ' UNION ALL select 2 as CompanyID,CompanyName from [STDAT2].[dbo].[Company]'
      end
    else
      begin
        set @strSQLExp = @strSQLExp + ' UNION ALL select 2 as CompanyID,''New Company'''
      end
    
    if exists(select * from sys.databases where name = 'STDAT3')
      begin
        set @strSQLExp = @strSQLExp + ' UNION ALL select 3 as CompanyID,CompanyName from [STDAT3].[dbo].[Company]'
      end
    else
      begin
        set @strSQLExp = @strSQLExp + ' UNION ALL select 3 as CompanyID,''New Company'''
      end
      
    if exists(select * from sys.databases where name = 'STDAT4')
      begin
        set @strSQLExp = @strSQLExp + ' UNION ALL select 4 as CompanyID,CompanyName from [STDAT4].[dbo].[Company]'
      end
    else
      begin
        set @strSQLExp = @strSQLExp + ' UNION ALL select 4 as CompanyID,''New Company'''
      end
    
    if exists(select * from sys.databases where name = 'STDAT5')
      begin
        set @strSQLExp = @strSQLExp + ' UNION ALL select 5 as CompanyID,CompanyName from [STDAT5].[dbo].[Company]'
      end
    else
      begin
        set @strSQLExp = @strSQLExp + ' UNION ALL select 5 as CompanyID,''New Company'''
      end
    
    
    exec(@strSQLExp)
    
    

    Monday, July 19, 2010 4:48 AM

Answers

  • Look what collation is on your tempdb. You can see it in the properties.

    This collation you have to add to your Statements to change the collation of the field temporary.

    example:

    Your database has a collation "Latin1_General_CS_AS", tempdb's collation is "Latin1_General_CI_AS" your statement has to look like this, because the dynamic SQL will process in tempdb:

    set @strSQLExp = 'select 1 as CompanyID,CompanyName collate Latin1_General_CI_AS  from [STDAT1].[dbo].[Company'

    • Marked as answer by ChewJerry Monday, July 19, 2010 8:08 AM
    Monday, July 19, 2010 8:01 AM
  • I prefer to use COLLATE DATABASE_DEFAULT.  Let us know if works.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by ChewJerry Monday, July 19, 2010 9:03 AM
    Monday, July 19, 2010 8:42 AM

All replies

  • I think, this is not an issue of the variable.

    Have you take the sql-statement in a new query-window and try to process?

    I believe the Collation of your database(s) and the tempdb are different. In this case you have to add the collation (from tempdb) to the statement

    Monday, July 19, 2010 6:21 AM
  • I think, this is not an issue of the variable.

    Have you take the sql-statement in a new query-window and try to process?

    I believe the Collation of your database(s) and the tempdb are different. In this case you have to add the collation (from tempdb) to the statement


    yup, i had tried the statement in a new query-window. And the error above comes out when i try to run. Can u explain clearly how to solve this problem?? I am new to SQL Server.

     

    Thank you.

    Monday, July 19, 2010 6:31 AM
  • Have a look in tempdb for database-collatiopn and then add it to your statement like this:

    if exists(select * from sys.databases where name = 'STDAT1')
      begin
        set @strSQLExp = 'select 1 as CompanyID,CompanyName collate <your tempdb-collation>  from [STDAT1].[dbo].[Company]'
      end
    else
      begin
        set @strSQLExp = 'select 1 as CompanyID,''New Company'''
      end

    please check this BOL:
    http://msdn.microsoft.com/en-us/library/ms179886.aspx

    Monday, July 19, 2010 6:46 AM
  • as said above you are using different collation for differents hence seeing the issue, run the command below find out the collation on your DBs, below example is for "master" you can put your DB name here.

    select name , collation_name from sys.databases where name = 'master'

    or you can find it by right click on DB name and Properties window

    then you can change the collation of DB using alter command as follows

    ALTER DATABASE <YourDBName> COLLATE SQL_Latin1_General_CP1_CI_AS


    Manish Sati
    Monday, July 19, 2010 6:47 AM
  • Have a look in tempdb for database-collatiopn and then add it to your statement like this:

    if exists (select * from sys.databases where name = 'STDAT1' )
      begin
        set @strSQLExp = 'select 1 as CompanyID,CompanyName collate <your tempdb-collation>  from [STDAT1].[dbo].[Company]'
      end
    else
      begin
        set @strSQLExp = 'select 1 as CompanyID,' 'New Company' ''
      end

    please check this BOL
    http://msdn.microsoft.com/en-us/library/ms179886.aspx

    Can I Know what do you mean by <your tempdb-collation>
    Monday, July 19, 2010 7:52 AM
  • I am unable to reproduce this error on SQL Server 2008.

    Can someone provide a simple T-SQL script reproducing this error? Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Monday, July 19, 2010 7:56 AM
  • Look what collation is on your tempdb. You can see it in the properties.

    This collation you have to add to your Statements to change the collation of the field temporary.

    example:

    Your database has a collation "Latin1_General_CS_AS", tempdb's collation is "Latin1_General_CI_AS" your statement has to look like this, because the dynamic SQL will process in tempdb:

    set @strSQLExp = 'select 1 as CompanyID,CompanyName collate Latin1_General_CI_AS  from [STDAT1].[dbo].[Company'

    • Marked as answer by ChewJerry Monday, July 19, 2010 8:08 AM
    Monday, July 19, 2010 8:01 AM
  • then you can change the collation of DB using alter command as follows

    ALTER DATABASE <YourDBName> COLLATE SQL_Latin1_General_CP1_CI_AS

    that will not solve the problem because changing the DB's collation will not change the table's collation
    Monday, July 19, 2010 8:02 AM
  • Look what collation is on your tempdb. You can see it in the properties.

    This collation you have to add to your Statements to change the collation of the field temporary.

    example:

    Your database has a collation "Latin1_General_CS_AS", tempdb's collation is "Latin1_General_CI_AS" your statement has to look like this, because the dynamic SQL will process in tempdb:

    set @strSQLExp = 'select 1 as CompanyID,CompanyName collate Latin1_General_CI_AS   from [STDAT1].[dbo].[Company'


    Thanks for your help.... My problem is solve now..
    Monday, July 19, 2010 8:09 AM
  • I prefer to use COLLATE DATABASE_DEFAULT.  Let us know if works.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by ChewJerry Monday, July 19, 2010 9:03 AM
    Monday, July 19, 2010 8:42 AM
  • I prefer to use COLLATE DATABASE_DEFAULT.  Let us know if works.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    Yup... It works.... thanks for your advice.
    • Marked as answer by ChewJerry Monday, July 19, 2010 9:02 AM
    • Unmarked as answer by ChewJerry Monday, July 19, 2010 9:03 AM
    Monday, July 19, 2010 9:02 AM