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)

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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>

Answer 6

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

Answer 7

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'

Answer 8

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

Answer 9

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..

Answer 10

I prefer to use COLLATE DATABASE_DEFAULT.  Let us know if works.

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 11

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.