Linked Server collation type error 1

[ChrisDodgson]

Hi,

I'm a bit stuck and any and all advice / help would be greatly appreciated.

I have a server (called for arguments sake: server1), this server and all the tables in all the databases on it have the following collation type: SQL_Latin1_General_CI_AS.

Server1 is linked to Server2. The server collation type of Server2 is also SQL_Latin1_General_CI_AS. However, for some reason unknown to me, the collation type on the tables in the relevant database on Server2 is set to Latin1_General_CI_AS.

So now, when I try to run union queries (for example) between Server1 and Server2 tables I get the following error:

Cannot resolve collation conflict for UNION operation.

The query I'm using runs as follows:


select * from [server2].reports.dbo.specialties
union
select * from server1.reports.dbo.specialties_local

(The two tables are identical in structure apart from collation type and I am not allowed to change the collation type of the tables on either server!)

Any and all ideas greatly appreciated.

Many thanks,

Chris

 

[mrdenny]

You can use the COLLATE statement in the query to change the collation type of the columns. You'll need to specify all the columns in the select statements.

For example I create two tables on my server named #test1 and #test2. Test one has a collation of greek_ci_as and #test2 is SQL_Latin1_General_CI_AS. Each table has a column called C1.

select C1 from #test1
union all
select C1 COLLATE greek_ci_as from #test2

You'll need to put the collate statement at each of the char, varchar, text, ntext, nvarchar, nchar, etc collums in the table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ChrisDodgson]

Many thanks that works a treat!

 

[mrdenny]

no problem

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)