SQL Server Linked Server query returns empty rows

Question

I use SQL Server 2014. I have setup a linked server via ODBC to a legacy database system. When I run this query:

select * 
from openquery([MyLink], 'select Ref, FxRate from <table> where Ref=155003')

I get the result with the correct number of records and columns.

But the values are all defaults:

Ref  FxRate
  0       0

In the table there are actual numbers for this. I was able to do a linked server for this same database from another server. I have tried to replicate that the best I could. Any thoughts on how to troubleshoot this?

Have you tried `SELECT Ref, FxRate FROM LinkedServer.DataBase.schema.table WHERE Ref = 155003`? — gofr1, Oct 02 '16 at 10:21
So, whatever you use OPENQUERY, linked_server.database.schema.table you get same zeros in output? Can you run this query directly on this server? — gofr1, Oct 04 '16 at 16:25
I could and the server used to return all the data. I updated with answer. Thank you for trying to help me. This took a day of my life. — user2966852, Oct 04 '16 at 17:54

score 3 · Accepted Answer · answered Oct 04 '16 at 16:28

I am posting this answer here for others. The ODBC driver was incompatible with the new machine's OS. I arrived at this conclusion after verifying that the correct query was executing on the legacy database but only the results were not display properly (they were all blanked out). I then ruled out any encoding issues. And finally upgraded the ODBC driver which fixed it.

SQL Server Linked Server query returns empty rows

1 Answers1