CODEWITHSUNDEEP
sql-servert-sql
SJedi
SJedi

Reputation: 5

SQL Query with Collation Issues

I am trying to compare data results from views on a test instance and on a live instance. Both instances are on the same server.

I have created a link between them both so I can query between them, but I am receiving a collation error - Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the EXCEPT operation.

I am trying to use this query to compare results because they should be the same results from both instances:

     SELECT * FROM (SELECT * FROM 
     INSTANCE1.dbo.View1) as TEST
     EXCEPT
     SELECT * FROM (SELECT * FROM 
     [SERVER\INSTANCE2].Database.dbo.View1) as LIVE

I have tried to wrap the query in CTE, and have also been trying to specify default collation but everything I try doesn't work.

Is there a way I can query these two views on seperate instances to compare the results without creating a temp table and by getting SQL to ignore the conflict?

Thank you so much, I appreciate any help.

Upvotes: 0

Views: 183

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35553

Well the error message tells you exactly what the problem is, but not which column or columns it belongs to, and because you use select * I have no idea what the columns names are either. You will need to identify columns in those tables that store strings, and then force then to use the same collation, something like this I think (untested):

SELECT stringcol 
FROM (
     SELECT stringcol collate  SQL_Latin1_General_CP1_CI_AS 
     FROM INSTANCE1.dbo.View1) as TEST
EXCEPT
SELECT stringcol 
FROM (SELECT stringcol collate  SQL_Latin1_General_CP1_CI_AS 
      FROM [SERVER\INSTANCE2].Database.dbo.View1
     ) as LIVE

So you will need to specify the columns, and you will need to also specify the collation to be used for string columns.

Upvotes: 1

Related Questions