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
Calling a MSSQL Stored procedure
Moderators: chulett, rschirm, roy
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
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
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
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
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 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
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
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
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