I have a SQL Server 2008. I have created an ODBC connection to a Firebird database using the Firebird/InterBase(r) driver.
The connection works ok.
Then I created a linked server in SQL Server 2008 using the Microsoft OLE DB Provider for ODBC diver.
Everything seems to work fine. I can pull data from tables using
SELECT * FROM OPENQUERY(LINKEDSERVERNAME, 'SELECT * FROM TABLENAME')
with success.
My problem is remote store procedure call. On the Firebird side there is a stored procedure that has one input parameter. Sometimes I get the result, but most of the time I get an error. I am not sure why it is happening, but I don't understand much from the error.
I call the procedure like this:
SELECT * FROM OPENQUERY(LINKEDSERVERNAME, 'SELECT * FROM GET_DOCUMENT_S(05011002766916)'
Its either I get the result (1 row) or I get an error. For id 05011002766916i always get the result, but for some id I never get an result, even tho the Firebird admin claims that the same procedure call returns result normally on his client.
Here are the errors I am getting:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "LINKEDSERVERNAME" reported an error. The provider did not give any information about the error.
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT * FROM GET_DOCUMENT_S(04170200133A120)" for execution against OLE DB provider "MSDASQL" for linked server "LINKEDSERVERNAME".
Or:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "LINKEDSERVERNAME" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "LINKEDSERVERNAME".
Can someone please explain what could be the issue, as currently I dont know if its a problem in ODBC, linked server or Firebird side...
Thank you.
The problem was on Firebird side. I used Flamerobin client and it produced IBPP:SQLException