SQL Server 2008 - Linked Firebird Server - not working properly

1
votes

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.

1
sql-serversql-server-2008odbcfirebirdlinked-server
Does the stored procedure include the SUSPEND keyword? - Mark Rotteveel
Marc thank you for the edit. Mark: I am not sure since i am not Firebird admin. They just provided me with the procedure name. Now they dont really care to help me out. I need to find the error or prove that the error is on their side. I cant even test if the result is really returned as they claim... how can i check for suspend keyword? - no9
You need to look at the source of the stored procedure. If it contains SUSPEND it means it is a selectable stored procedure, which is different from a normal stored procedure. What happens if you execute this directly to Firebird (eg using an admintool like FlameRobin)? - Mark Rotteveel
Just execute the select statement (SELECT * FROM GET_DOCUMENT_S(05011002766916)) - Mark Rotteveel
Thank you Mark. Yes, FlameRobin also throws exception (IBPP:SQLException) so it looks like it is a problem on the Firebird side. If you want you can write an anwser in the manner (install Flamerobin and check) and I will accept it. - no9

1 Answers

0
votes

The problem was on Firebird side. I used Flamerobin client and it produced IBPP:SQLException