Calling a MSSQL Stored procedure

aluthra48 · Post by **aluthra48** » Fri Jun 26, 2009 1:41 pm

I want to execute a stored procedure using the STPPX stage and I'd like some help with the following 2 questions:

The STPPX stage requires an input link. This is fine if the sp requires parameters, as then I can use the input link to provide the parameters to the sp. But what if no parameters are required by the sp? Do I then construct the sp with a dummy parameter, so that I can use the input link, even though the parameter is never used in the sp?

Second: The stored procedure I have constructed deletes/inserts rows. The parallel job returns an abort if there are no rows that end up being deleted or inserted. I have used test data to assure that the job works fine if the there are 1 or more rows that end up being deleted/inserted. How should I set up the STTPX stage to do so.

I have experimented with unchecking the "generate procedure call" in the Stage===>syntax tab, and to provide more syntax as exec spname ( ? )

Thanks

chulett · Post by **chulett** » Fri Jun 26, 2009 2:08 pm

The answer to the first question is 'yes'. For the second, not sure what you mean by "to do so? You can't abort on zero rows as nothing would be triggered then. Check 'after job' instead.

aluthra48 · Post by **aluthra48** » Fri Jun 26, 2009 2:20 pm

What I meant by "to do so" was how do I configure the stppx stage so that the sp does not abort if there are no rows that can be deleted or inserted? I have selected procedure type "Transform" as I am providing parameters to the sp.
Here's the coding for the procedure call syntax: Exec spname ( ? )
and here is how I have specified the procedure name: spname;1
I have unchecked the "generate procedure call" check box.

Thanks

chulett · Post by **chulett** » Fri Jun 26, 2009 2:30 pm

What exactly happens right now when "there are no rows that can be deleted or inserted"? What causes the abort?

aluthra48 · Post by **aluthra48** » Fri Jun 26, 2009 2:50 pm

Actually, with the stppx configured as mentioned above I get a syntax error. I am sorry, I have tried several permutations that I have lost sight of what configuration gave what error message in the logs. The error message is:

APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'.

I then changed the configuration to enable "generate procedure call", and now the error message I get is:

Stp_Set_Active_Tables,0: Failure during execution of operator logic.
APT_CombinedOperatorController,0: Fatal Error: Fatal: CLI_NO_DATA

Sreenivasulu · Post by **Sreenivasulu** » Sun Jun 28, 2009 6:33 am

HI,

We are done this.
1. Use the row generator stage to generate a 'dummy record'
2. Use the SP stage to call MSSQL Stored procedure
3. Always return '0' since the SP wanta a Zero as the return value

Regards
Sreeni

aluthra48 wrote:Actually, with the stppx configured as mentioned above I get a syntax error. I am sorry, I have tried several permutations that I have lost sight of what configuration gave what error message in the logs. The error message is:

APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'.

I then changed the configuration to enable "generate procedure call", and now the error message I get is:

Stp_Set_Active_Tables,0: Failure during execution of operator logic.
APT_CombinedOperatorController,0: Fatal Error: Fatal: CLI_NO_DATA

aluthra48 · Post by **aluthra48** » Mon Jun 29, 2009 11:16 am

Thanks, I will try this.

To make it work, I made the following changes:
1. Changed the stored proc to bypass the insert/delete logic, if there are no rows to insert/delete
2. Create an output parameter in the sp, and write it out to a peek stage from the stppx stage.

While this works, I will try the option you suggested.
Thanks

aluthra48 · Post by **aluthra48** » Fri Aug 21, 2009 3:45 pm

IBM issued a patch for this