CODEWITHSUNDEEP
sqlsql-server
NebDaMin
NebDaMin

Reputation: 658

How to use EXCEPT clause in multiple databases

I have a query with an except clause. Underneath the except, my 2nd query is coming from a different database than the one that the first query is using.

It looks something like this

SELECT field1 as a
FROM table 1
EXCEPT
USE differentdb
SELECT field2 as a
FROM table 2

I have also tried this

SELECT field1 as a
FROM table 1
EXCEPT
USE differentdb
SELECT field2 as a
FROM differentdb.dbo.table2

I realize that this is not allowed in SQL because I get the following error:

Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

I am wondering if there is another way to write this query and accomplish a cross-db EXCEPT clause.

Upvotes: 2

Views: 2485

Answers (2)

Andrey Korneyev
Andrey Korneyev

Reputation: 26876

Us can use collate clause to convert collation from other database.

SELECT field2 collate SQL_Latin1_General_CP1_CI_AS as a
FROM table 2

Here you should use correct collation - because it is not enough information what is collation of your first and second databases.

Or you can simply use

SELECT field2 collate database_default as a
FROM table 2

This will cause the collate clause to inherit the collation of the current database

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Change the collation of column on the fly like:

SELECT field1 COLLATE SQL_Latin1_General_CP1_CI_AS as a
FROM table 1
EXCEPT
SELECT differentdb.SchemaName.field2 as a
FROM table 2

Upvotes: 4

Related Questions