SQL Runing On Several Tables

snassimr · Post by **snassimr** » Tue Jun 21, 2005 7:51 am

Hi !

I have question . I have asked it before but havent got reasonable answer.

After I run 5 Jobs with Sequencer . I want to Enter each of 5 Tables specific row with with specific key. I wrote sp but "Stored Procedure Stage" have to be with Output or Input Link and I want to run the sp and thatis it.

Any ideas how to do it ?

chulett · Post by **chulett** » Tue Jun 21, 2005 8:18 am

Maybe you haven't gotten a 'reasonable answer' because your question is practically incomprehensible.

Going off the "I want to run sp and thatis it" part, why not just write a batch file to call your sql tool of choice and execute the stored procedure? Run it from a Routine Activity stage in the Sequencer job using 'DSExecute'. For example, if Oracle then you would use 'sqlplus' to do the dirty work.

Does that seem reasonable?

snassimr · Post by **snassimr** » Tue Jun 21, 2005 8:53 am

Te sp will be invoked from batch . Is there is way to do it from Datastage ?

ray.wurlod · Post by **ray.wurlod** » Tue Jun 21, 2005 5:06 pm

Depending on what the stored procedure does you may be able to call it from an ODBC stage (or more than one).

snassimr · Post by **snassimr** » Wed Jun 22, 2005 4:54 am

My sp Inserts one row into 5 table . Is there is way to call sp within VB code

ray.wurlod · Post by **ray.wurlod** » Wed Jun 22, 2005 5:37 pm

Is there is way to call sp within VB code?

Probably, but the answer won't be found on a DataStage forum.

chulett · Post by **chulett** » Wed Jun 22, 2005 7:48 pm

Besides, why make it more complicated than it needs to be? What's wrong with scripting something to execute the sp and triggering that inside your Sequencer job via a Routine Activity?

snassimr · Post by **snassimr** » Thu Jun 23, 2005 2:31 am

"scripting something to execute the sp "

How to do this ?