CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
PeterK
PeterK

Reputation: 111

Implicit conversion - SQL

I have the below SQL query

DECLARE @table table(dbname sysname)

INSERT INTO @table(dbname)
SELECT NAME FROM sys.databases where name like '%AccountsLive'

DECLARE @Sql NVARCHAR(MAX) = NULL;

SELECT  @Sql = COALESCE(@Sql + ' UNION ALL ' + CHAR(13) + CHAR(10), '' ) +
'SELECT ST_ALOC_POINTER, ST_TRANTYPE, ST_HEADER_REF, ST_GROSS , ST_CURRENCYCODE , ST_CURR_VALU , ST_DESCRIPTION FROM ' 
+ QUOTENAME(dbname) + '.dbo.SL_TRANSACTIONS
WHERE ST_YEAR = ''N''
and ST_PERIODNUMBER = ''1''
and ST_ALOC_POINTER like ''%LC%''
and ST_GROSS <> ''0''
UNION ALL
SELECT ST_ALOC_POINTER, ST_TRANTYPE, ST_HEADER_REF, ST_GROSS , ST_CURRENCYCODE , ST_CURR_VALU , ST_DESCRIPTION FROM ' 
+ QUOTENAME(dbname) + '.dbo.SL_TRANSACTIONS
WHERE ST_YEAR = ''N''
and ST_PERIODNUMBER = ''1''
and ST_ALOC_POINTER like ''%CR%''
and ST_GROSS <> ''0'''
FROM    @table

EXEC( @Sql );

However, I am getting an implicit conversion error as below:

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in UNION ALL operator.

Does anyone know what this or how to fix my query? Thanks in advance!

Upvotes: 0

Views: 920

Answers (2)

sa-es-ir
sa-es-ir

Reputation: 5042

The error is about different collation on your table and sys.databases table you should use explicit collate in select statement (the problem is dbname) and just chang this line:

SELECT NAME COLLATE Latin1_General_CI_AS FROM sys.databases where name like '%AccountsLive'

And also change your @table to this:

DECLARE @table table(dbname sysname COLLATE Latin1_General_CI_AS)

If not solve the problem please also use this:

... QUOTENAME(dbname) COLLATE Latin1_General_CI_AS ...

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 12959

As we are putting UNION ALL across multiple databases, we are getting collation error, as there is collation conflict between columns in the database tables.

to avoid this, we can modify the query as given below, for the specific column having the issue.

We don't need to do this for every column. But, as I don't know, which column is having this issue, I am putting for all columns in the query.

SELECT  @Sql = COALESCE(@Sql + ' UNION ALL ' + CHAR(13) + CHAR(10), '' ) +
'SELECT ST_ALOC_POINTER COLLATE DATABASE_DEFAULT, ST_TRANTYPE COLLATE DATABASE_DEFAULT,
 ST_HEADER_REF COLLATE DATABASE_DEFAULT, ST_GROSS COLLATE DATABASE_DEFAULT, 
ST_CURRENCYCODE COLLATE DATABASE_DEFAULT, ST_CURR_VALU COLLATE DATABASE_DEFAULT, 
ST_DESCRIPTION COLLATE DATABASE_DEFAULT FROM ' 
+ QUOTENAME(dbname) + '.dbo.SL_TRANSACTIONS

Upvotes: 1

Related Questions