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?

Answer 1

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.