Executing DB2 Stored Procedures from Reporting Services

I have this international client using AS/400 with DB2 UDB as the main
database.
They are searching for a reporting tool. They liked the Reporting Services
capabilities and now we are trying to find a way to run DB2 Stored Procedures
from Reporting Services.
The client tried to reach this goal with the following providers with no
success:
1) ODBC for DB2
2) IBM OLE DB Provider for DB2
3) ODBC and OLE DB for iSeries
4) Microsoft OLE DB Provider for DB2.
In my laboratory environment I installed DB2 UDB Enterprise Edition
(evaluation) - V 8.1.7.445 on WIN/XP and on WINDOWS 2003.
I installed SQL SERVER 2005 with Reporting Services on both machines
(WINDOWS 2003, WINDOWS XP).
With "Microsoft OLE DB Provider for DB2, I succeed running the DB2 Stored
Procedures with no problem from both machines and from one machine to the
other as well.
I asked the client to uninstall all the providers and re-install the
"Microsoft OLE DB Provider for DB2".
He has done so and get the following message:
"failed to convert parameter value from a string to a Byte[] (System.Data)"
We tried the same with int parameter and with char parameter. The Stored
Procedure is a very simple one (in the DB2 - declare cursor with a simple
select and then open the cursor). Running the Sp from DB2 and in my lab
succeed.
Does anyone know how can I run DB2 Stored Procedures with no problem?
Which provider should I use?
Are there other clients running reports with MSRS against DB2 UDB on AS/400?
Many thanks,
Michelle.Hi Michelle,
We use DB2 v8.1 on unix and I am able to run stored procedures. It can be
a little qwerky at times. It sounds like you are running fine also. I dont
think I am using the Microsoft OLE DB provider for DB2 ... I believe it is
the IBM OLE DB Provider for DB2. It just says "OLE DB" in the type box and
the connection string looks like this:
Provider=IBMDADB2.1;Data Source=<dbNameHere>;Location=<ipAddressHere>
I know it works because that is what our main database is - DB2
"Michelle" wrote:
> I have this international client using AS/400 with DB2 UDB as the main
> database.
> They are searching for a reporting tool. They liked the Reporting Services
> capabilities and now we are trying to find a way to run DB2 Stored Procedures
> from Reporting Services.
> The client tried to reach this goal with the following providers with no
> success:
> 1) ODBC for DB2
> 2) IBM OLE DB Provider for DB2
> 3) ODBC and OLE DB for iSeries
> 4) Microsoft OLE DB Provider for DB2.
> In my laboratory environment I installed DB2 UDB Enterprise Edition
> (evaluation) - V 8.1.7.445 on WIN/XP and on WINDOWS 2003.
> I installed SQL SERVER 2005 with Reporting Services on both machines
> (WINDOWS 2003, WINDOWS XP).
> With "Microsoft OLE DB Provider for DB2, I succeed running the DB2 Stored
> Procedures with no problem from both machines and from one machine to the
> other as well.
> I asked the client to uninstall all the providers and re-install the
> "Microsoft OLE DB Provider for DB2".
> He has done so and get the following message:
> "failed to convert parameter value from a string to a Byte[] (System.Data)"
> We tried the same with int parameter and with char parameter. The Stored
> Procedure is a very simple one (in the DB2 - declare cursor with a simple
> select and then open the cursor). Running the Sp from DB2 and in my lab
> succeed.
> Does anyone know how can I run DB2 Stored Procedures with no problem?
> Which provider should I use?
> Are there other clients running reports with MSRS against DB2 UDB on AS/400?
> Many thanks,
> Michelle.