Issues With Missing Collate In Sql Server

I'm trying to perform the below query, but, I'm getting the error: Msg 457, Level 16, State 1, Line 73 Implicit conversion of varchar value to varchar cannot be performed because t

Solution 1:

Working across databases is a common source of collation issues. For example, suppose I have two databases, DB1 and DB2. DB1's default collation is Latin1_General_CI_AS, and DB2's is SQL_Latin1_General_CP1_CI_AS.

If I do the following I will get a collation conflict:

use DB1;
go

createoralterfunction dbo.foo() returnsvarchar(10) asbeginreturn'foo';
end
go

use DB2
go

declare@barvarchar(10) ='bar'+ DB1.dbo.foo();

The error is:

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

To resolve this error, I can force the collation I want it to use for my expression:

set@bar='bar'+ DB1.dbo.foo() collate SQL_Latin1_General_CP1_CI_AS;

The way explicitly setting collation works in expressions can be unintuitive. For example, this would also have worked:

declare@barvarchar(10) ='bar'collate sql_latin1_general_cp1_ci_as  + DB1.dbo.foo();

That's weird, I'm in DB2, and DB2 already has a collation of sql_latin1_general_cp1_ci_as, so why would setting the literal 'bar' to that make any difference? Well, because I'm being explicit.

In your code you are doing the same kind of thing as I am in my sample problem. You are declaring the variable @SQL_STR and assigning a value to it. You then try to append the result of ETL1.dbo.BI_GetSybaseIQDateExtent to that variable. That function is in a different database. It is highly likely that the database you are in, and database ETL1, have different collations.

To resolve the problem, set an explicit collation on your string concatenation, instead of inside the strings being concatenated (which is what you are doing now). To make it easier to read you can do it right up front:

SET@SQL_STR =''collate latin1_general_ci_as +'
    SELECT
        A_REC,
        B_REC,
        CALL_SOURCE,
        -- ...

Solution 2:

This issue happens when your database and master database collation differs.

I have solved the issue with the below link.

https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/

Option#2 is useful in the above link for a change of collation in master db.