Stored procedure

subbareddy · Post by **subbareddy** » Thu Jun 29, 2006 9:37 am

Hi,

I want to call a Store Procedure in a datastage ETL job. The store procedure is in SQL Server database. I want to pass some job parameters for some of the SP parameters and for the remaining SP parameter I want to use default values. I also have to perform some calculations on the result set of SP. I tried using Server job -ODBC stage but facing some errors like columns dont match etc.

I would greatly appreciate if anyone can help me in calling the SP (which is in SQL Server database) in ETL Datastage Job.

Thanks.

kcbland · Post by **kcbland** » Thu Jun 29, 2006 9:51 am

Welcome aboard! STP stage and ODBC/OCI stage SP usage is meant to stream data either in or out of a database. If the SP "does something" like grant permissions or juggle data around, then you want to use a command line execution to invoke the SP. Since you're on Unix, the ODBC driver will establish a connection for you, but I don't know about isql/osql on Unix.

If you want to rig your SP to work, it's going to require that you setup columns and stream at least a row back.

subbareddy · Post by **subbareddy** » Thu Jun 29, 2006 10:02 am

Thanks. I had given the column names and datatypes (precision etc) in outputs - columns tab. I had called the SP using call statement in outputs - SQL Query - SQL Query primary inputs. But when I run the job, its aborting and giving the error - statement has incorrect number of result columns. What does this mean. My output coulumns match to the number of columns returned by the SP.

Please let me know.

Thanks.

kcbland · Post by **kcbland** » Thu Jun 29, 2006 10:08 am

First import the metadata definitions for the SP using DS Manager and then load into the stage.

subbareddy · Post by **subbareddy** » Thu Jun 29, 2006 12:16 pm

I have imported the Stored procedure metadata in DS Manager. (DS Manager - Import - Table Defintions - Store procedure Definition and then pop up import metadata (stored procedure ) gave procedure name and user id/pwd, dsn name). I have imported the same in the ODBC stage ( outputs - columns - load - sp columns). Still receiving the same error - Statement has incorrect number of result columns. MY SP returns about 15 columns. Entered the same in Sp metadata and in stage too. Still the same error. Please let me know how to resolve this.

Thanks.

kcbland · Post by **kcbland** » Thu Jun 29, 2006 12:19 pm

You're going to have to figure out the discrepancy in your SP. Maybe start by writing a simple SP that returns 1 columns and testing from there.