Cannot resolve collation conflict for UNION operation 1

[ASPVBNerd]

When I try to test my this sql query I get an error message
Cannot resolve collation conflict for UNION operation

SELECT * FROM(SELECT 'ARTSTATDB' AS DB, [NAME] FROM ARTSTATDB..SYSOBJECTS WHERE [NAME] IN ('ARTIKEL_STAT')) A
Union
(SELECT 'STOREPOS' AS DB, [NAME] FROM STOREPOS..SYSOBJECTS WHERE [NAME] IN ('STORECREDIT', 'GIFTCERTIFICATE'))
ORDER BY DB

The collation of database ARTSTATDB is FINNISH_SWEDISH_CI_AS
The collation of database STOREPOS is SQL_LATIN1_GENRAL_CP1_CI_AS


Is it possible to ignore collation in my sql or something like that, just to make my sql to work?


George

 

[SQLDenis]

what happens when you do this?

SELECT * FROM(SELECT 'ARTSTATDB' COLLATE SQL_LATIN1_GENRAL_CP1_CI_AS AS DB, [NAME] COLLATE SQL_LATIN1_GENRAL_CP1_CI_AS
FROM ARTSTATDB..SYSOBJECTS WHERE [NAME] IN ('ARTIKEL_STAT')) A
Union
(SELECT 'STOREPOS' AS DB, [NAME] FROM STOREPOS..SYSOBJECTS WHERE [NAME] IN ('STORECREDIT', 'GIFTCERTIFICATE'))
ORDER BY DB

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ASPVBNerd]

SQLDenis, I have tried your sql but I got the error message
No column was specified for column 2 of 'a'.

George

 

[SQLDenis]

change [NAME] COLLATE SQL_LATIN1_GENRAL_CP1_CI_AS
to
[NAME] COLLATE SQL_LATIN1_GENRAL_CP1_CI_AS AS [NAME]

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ASPVBNerd]

Thank you Denis

George

 

[SQLDenis]

No problem, thanks for the star

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/