SQL stored procedure with 'Stored Procedure' stage

[vish08]

Hi,
StoredProcedure on SQL server with parameters need to be executed which creates a tabel with data. Hoping SP executes sucessfully, bring that data into Oracle database.
I am thinking
Stored Procedure Stage as source --->checkforSTPreturncode
Another job,read data from the tabel, and load into Oracle
But I am getting errors. First, is there better approach to achieve this?
Thank you for your response.

[ray.wurlod]

Reading from a table you've just created? Why not just use a Row Generator stage in a job, or the same SELECT with which you would have populated the table in your Stored Procedure? That is, is creating the table really necessary? I suspect not.

[Sreenivasulu]

Use the rowgenerator stage as a 'dummy' stage to call a SP

Regards
Sreeni

[asorrell]

Actually, the answer depends on the type of parameters you have on your SQL Server stored procedure. If all the parameters are "output" parameters - in other words, you don't send any information to the procedure, you just get data out, then set it up as a "Source" type using the Stored Procedure Stage. It will only require an output link. If it returns multiple rows of data remember to check "returns multiple rows" on the Output tab.

If it has a mix of input and output parameters, then set the Stored Procedure up as a "Transform" type and you'll need to feed in the required information to the input parameters via some mechanism (sequential file or row generator) through the input link to the stage.

Regardless of what the documentation says, I've never been able to get the imported Stored Procedure table definitions to work. It marks all parameters as Input / Output, which is incorrect. I've always had to setup the metadata for the stage manually.

Oh - and a final note on SQL Server with Stored Procedures - the documentation has a bug in it. The ProcCode (return code) is all that is ever returned from the procedure (if you check that "return status" box). The ProcMessage does NOT work for SQL Server (only), I've raised a PMR with IBM and it will be removed from the documentation in the next revision.

If this doesn't address your question - please supply specific errors and I'll take a look at it later today. If it did fix it, please mark this thread "resolved" at the top of the screen.

[asorrell]

I found another bug (just reported it to IBM this morning). If the stored procedure outputs PRINT messages or Warnings when it is run, that causes problems with the Stored Procedure Stage.

Make sure your stored procedure isn't outputting anything to the screen other than the "row(s) affected" messages...