2

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?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Will_C
  • 108
  • 5
  • Interesting. Can you select a single column rather than `*`? Any computed columns on that table? It's a table and not a view, right? – Blorgbeard Sep 09 '15 at 21:15
  • http://stackoverflow.com/a/496840/1821329 – FutbolFan Sep 09 '15 at 21:26
  • @Blorgbeard Even if I specify a singular column, I still get the same message. It is a table and I don't believe there are any computed columns. – Will_C Sep 10 '15 at 15:12
  • I should mention SERVERA is 2012 and SERVERB is 2008 R2, and the Charge_Type column is a tinyint data type. – Will_C Sep 10 '15 at 15:24
  • @FutbolFan Thanks for the response. I *kind of* understand the collation issue. Is there a way to resolve it? – Will_C Sep 10 '15 at 15:45
  • @Will_C Personally, I haven't had to deal with this kind of situation before. I apologize, but I thought the link would help a little bit. Although I would try to change the collation on `SERVERA` and see if it works. – FutbolFan Sep 10 '15 at 15:55
  • I was able to get around this by creating a view on SERVERB that excluded Charge_Type and querying the view, but that was really just a Band-Aid. If anyone can help with this, I'd still like to figure it out. – Will_C Sep 14 '15 at 13:24

1 Answers1

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

Community
  • 1
  • 1
Goldfish
  • 624
  • 6
  • 11