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.

Execute xp_cmdshell from store procedure (called from aspx)

Hi, I have been searching for an answer to allow me to execute xp_cmdshell from withing store prcedure by calling the store procedure from an aspx via click on a button. This is what I found from my reserch but was not able to know where to set the rights and what each of them means:
"To run xp_cmdshell for a non-system administrator user, you must grant the following rights.
MSSQLServer and SQLServerAgent Services
Act as part of the Operating System.
Increase Quotas.
Replace a process level token.
Log on as a batch job."

The above quote was from the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264155

The bottom line is to be able to call a store procedure from an aspx page to execute the code which contain xp_cmdshell command, an example of such command is like:
------------------
EXEC master..xp_cmdshell 'dir d:\BT_import\Data\sales_option_price_report.csv'

Thanks for your help
ehx5The "rights" refer to the user account that is running the extended stored proc, namely the user account for teh MSSQLServer service and the SQLServerAgent service. Once you know who they are you can go to the security policy editor and grant them the said rights. However, this is *VERY* dangerous and I really would recommend that you do NOT take this path. If someone gets onto to DB the xp_xmdShell 'Format c'. You get the idea. Much, much better to let some Business logic component sort this out, or even your own extended proc.