Implicit conversion - SQL

Question

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!

Answer 1

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