I have a SQL Server database and a Firebird database as a linked server. I'm trying to select a table in the linked server, but I get the following error:
OLE DB provider "MSDASQL" for linked server "FIREBIRD" returned message "[ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 22
.".
Msg 7306, Level 16, State 2, Line 4
Cannot open the table ""de_ib"."ROHSTOFF"" from OLE DB provider "MSDASQL" for linked server "FIREBIRD".
I have synonyms for the linked tables set up in my SQL database. The following three statements all return this error:
SELECT * FROM ROHSTOFF
SELECT * FROM [dbo].[ROHSTOFF]
SELECT * FROM [FIREBIRD].[de_ib]..[ROHSTOFF]
But the following returns the data as expected:
SELECT * FROM OPENQUERY(FIREBIRD, 'SELECT * FROM ROHSTOFF')
If I right-click a linked table and select Script Table as > INSERT To > New Query Editor Window, the following appears in the query editor:
-- [FIREBIRD].[de_ib]..[ROHSTOFF] contains no columns that can be selected or the current user does not have permissions on that object.
I'm logged in with credentials that match the owner of the Firebird database.
Why am I getting this error and how can I select data from my linked server without using OPENQUERY? I have a legacy application that selects from the Firebird database, but I need it to select seamlessly from the synonyms instead.