Linked Server Doesn't Work, Openrowset Does

The simple ODBC test connection works. The simple linked server test connection works. I can get a list of tables using that linked server. Outside of SQL Server, I can write a powershell script that does a select * from sometable.

I cannot get a Select * from [whateverlinkedtable] to work. (The exact syntax is generated by SQL Server when I click on a table and Script Table as Select To... New Query Editor Window

It generates this error:
whateverlinkedtable contains no columns that can be selected or the current user does not have permissions on that object.

I have tried the current Data Direct ODBC driver and the current driver that comes with the student version of the current version of OpenEdge. I have tried DSN type and DSN-less connections. I doubt it matters, but the SQL Server version is SQL 2014 SP2.

If I had no choice, I'll use the tedious to write OpenRowSet version, but I would like to be able to just click on things in the SQL Server Management Studio environment and write much shorter query statements by using Linked Servers.

FWIW, I read all the threads in this subforum with Linked Server in the title, and looked at the webpages listed here
Progress KB - Home

So I have tried a number of things, and almost surely all the normal/likely problem solutions.
 
Hmmm. SQL Server generated the SQL statement, I just clicked in the UI to get it to show me data. I will double check to see what SQL it is generating. Thanks for the link.
 
Since it is clear to me that making use of ODBC will be a long term good thing for us, I am now installing the licensed ODBC driver for Windows that comes with with our QAD product and then I will try following up on your idea about the linked server generated SQL.
 
The code the linked server-based UI is using must not be generating the right syntax, because when I write my own query manually that uses the 4 part table naming (the first part being the linked server name I created) then it does retrieve data as expected. Well, that's certainly far less typing then using the queryrowset() function. Thanks again.
 
Top