Calling a DB2 SP from Reporting Services

This is not a post about animals,

this is not a post about trees.

This is a post I wrote especially for those who are wanting to call a stored procedure with parameters in a DB2 database from SQL Server Reporting Services 2008 using the IBM ODBC DB2 9.5 driver.

When defining your Dataset, the BIDS let’s you specify the query type.   The possible values are Text, Table and Stored Procedure.  Which one would you choose?  Indeed, Stored Procedure seems like a good choice.

So I filled it all out as in the screenshot above.  When clicking the Refresh Fields button, the BIDS was able to read the required parameter from the stored procedure.

I gave it a valid value and clicked OK.  And guess what?  Then it stopped working, showing me the following (quite common) unexpected token error (when connecting to DB2):

ERROR [42601] [IBM][CLI Driver][DB2/NT] SQL0104N  An unexpected token “END-OF-STATEMENT” was found following “MYSP”.  Expected tokens may include:  “JOIN <joined_table>”.  SQLSTATE=42601

The full screenshot:

I’m quite sure that this is something going wrong with the ODBC driver.  I also tried this scenario using the following OLE DB drivers:

• Microsoft OLE DB Provider for DB2
• IBM OLE DB Provider for DB2

The Microsoft OLE DB driver for DB2 is one of the Feature Pack downloads.

The best results were achieved using the IBM OLE DB driver.  I was able to successfully call the stored procedure.

I’d like to mention that the Connection Strings website is a very useful resource in the context here.  With the information available there I was able to quickly form the connection strings that I needed to provide to the drivers.

But for my project the requirements tell me to use ODBC.  So I found the following solution to my problem.

Instead of selecting Stored Procedure as query type for the dataset, I chose Text and typed the following statement:

call MYSCHEMA.MYSP(?)

Or to say it with an image:

This worked quite well, I got results from the stored procedure as expected.

One additional tip that I’d like to mention here is that the BIDS is not always happy about using the Query Designer in combination with the stored procedure.  Not sure what exactly is going wrong but sometimes the Dataset Properties window stops responding when coming back from the Query Designer.  Maybe something to do with the population of the Fields list?

I did find that it would be less unstable if the fields were already defined.  So if possible and you’re having this issue, try to get a similar resultset as the stored procedure returns by using a SELECT statement.  When that is working fine, your fields collection is defined as the stored procedure expects it.  Then swap the SELECT statement with the CALL statement.

Have fun!