Run SQL Server Stored Procedure

mikesherm · Post by **mikesherm** » Tue Jan 19, 2010 10:33 am

I'm trying to use a Stored Procedure Stage in a Job Sequence to run a SQL Server Stored Procedure to truncate tables before individual Job Sequences are processed. There are no parameters to send to the Stored Procedure. I'm having trouble configuring the Stage to work properly. It keeps failing with the following error:
Caught unknown exception from runLocally().
I'm assuming this is a pretty generic error message.
I've tried procedure call syntaxes such as {CALL [stored procedure]} and {Exec [Stored Procedure]}. I've searched documentation and the forums but can't seem to determine what I'm doing wrong.

Any advice in configuring the stage properly would be helpful.

Thanks.

Mike

mikesherm · Post by **mikesherm** » Tue Jan 19, 2010 10:38 am

Sorry, I misspoke. I realize that the Stored Procedure Stage is used for Parallel Jobs, not Job Sequences. Is it possible to create a Parallel Job that would have as its only stage a Stored Procedure Stage? If so, I still need to figure out the configuration.

Otherwise, is it possible to call it from a Job Sequence. I don't have a client SQL Server app on the Server to I can't call a Command Line to run the procedure.

Thanks and sorry for the confusion.

Mike S.

Sreenivasulu · Post by **Sreenivasulu** » Tue Jan 19, 2010 12:14 pm

Use the rowgenerator stage as a dummy and plug it to the SP stage

Regards
Sreeni

mikesherm · Post by **mikesherm** » Tue Jan 19, 2010 4:27 pm

Sreenivasulu,

The client I'm working at has only purchased QualityStage. It appears that the Rowgenerator Stage is a Datastage specific stage. Is there another stage I can use as a dummy stage.

Mike

dganeshm · Post by **dganeshm** » Tue Jan 19, 2010 4:43 pm

mikesherm wrote:Sreenivasulu,

The client I'm working at has only purchased QualityStage. It appears that the Rowgenerator Stage is a Datastage specific stage. Is there another stage I can use as a dummy stage.

Mike

Try using a sequential file and use it to trigger the SP stage.

chulett · Post by **chulett** » Tue Jan 19, 2010 5:32 pm

mikesherm wrote:The client I'm working at has only purchased QualityStage. It appears that the Rowgenerator Stage is a Datastage specific stage.

In that case, you should probably post in the QualityStage forum, that would probably help keep your answers confined to the proper product.

mikesherm · Post by **mikesherm** » Thu Jan 21, 2010 9:14 am

Chulett,

I would generally agree with you, although it appears that with the combining of the 2 products into one interface, the lines have gotten a bit blurred. Since I'm not using any of the QS specific components, I had assumed that the content of the post would be more relevant to DS users. If I assumed wrong, my bad.

Thanks for your response.

Mike