Linked server reference "Invalid Column Name"

Question

In SSMS 2012, I have created a linked server in SERVERA to SERVERB from which I have successfully written queries to multiple tables within the DBB database using a four part reference.

When I try to reference the 'Charge' table in the 'DBB' database with a simple select statement:

SELECT * FROM [SERVERB].[DBB].dbo.Charge

I get the following message:

Msg 207, Level 16, State 1, Line 1 Invalid column name 'charge_type'.

This column exists in the DBB database as 'Charge_Type', however, the collation of SERVERB is case insensitive, whereas the collation of SERVERA is case sensitive (which is where, I believe, my problem lies).

Does anyone have experience with this issue?

Answer 1

(For the people who might end up here)

You can change the collation on the fly. In this case, you have to write the name of the column names in the select query. What I mean is, rather than writing query like this:

SELECT * FROM [SERVERB].[DBB].dbo.Charge

write the query like this:

SELECT Charge_Col1, Charge_Col2, Charge_Type COLLATE Latin1_General_CI_AS FROM [SERVERB].[DBB].dbo.Charge

There is another post similar to this: how we can select two columns having different collation