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!

Please print the ``@sql`` and post it to see the generated query. — sa-es-ir, Mar 08 '21 at 07:27
The whole print is too long to put on here, so here is just the first part: `SELECT ST_ALOC_POINTER, ST_TRANTYPE, ST_HEADER_REF, ST_GROSS , ST_CURRENCYCODE , ST_CURR_VALU , ST_DESCRIPTION FROM [MADAccountsLive].dbo.SL_TRANSACTIONS WHERE ST_YEAR = 'N' and ST_PERIODNUMBER = '1' and ST_ALOC_POINTER like '%LC%' and ST_GROSS <> '0' UNION ALL` — PeterK, Mar 08 '21 at 07:32
This is straightforward SQL debugging... first print the SQL and run it as static SQL... much easier to debug than dynamic. Second remove all columns and add back 1 at a time until the error occurs, correctly collate the columns causing the error... put the fix back into the dynamic SQL. This isn't something we can look at and answer because we don't know the collation of your columns. — Dale K, Mar 08 '21 at 09:32
@DaleK thank you for the advise and have done this with the help from the below and now works perfectly! — PeterK, Mar 09 '21 at 01:48

score 1 · Answer 1 · answered Mar 08 '21 at 07:48

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

i'm now getting an `Expression type float is invalid for COLLATE clause.` using this? — PeterK, Mar 08 '21 at 08:08
@PeterK Only use the `COLLATE` for string column like `varchar` etc — Squirrel, Mar 08 '21 at 08:30
thank you so much for the help with this....i have now got this working — PeterK, Mar 09 '21 at 01:49

sa-es-ir · Accepted Answer · 2021-03-08T08:19:57.443

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

edited Mar 08 '21 at 08:19

answered Mar 08 '21 at 07:53

sa-es-ir

3,722
2
13
31

i still get the same implicit conversion error when i try this :( if i combine this with @venkataraman solution, i get the same expression type float error – PeterK Mar 08 '21 at 08:09
I use this in my code and It's fine however answer updated, in @venkataraman answer use collate just for varchar columns. – sa-es-ir Mar 08 '21 at 08:17
thank you for the help with this, greatly appreciated.....i have used this with @venkataraman and now all works – PeterK Mar 09 '21 at 01:48
one thing to add is that i needed to give each column an alias otherwise i would get a 'no column name' result – PeterK Mar 09 '21 at 01:50

Implicit conversion - SQL

2 Answers2