How to call sqlservers stored proc?

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
ds_ashish
Participant
Posts: 15
Joined: Thu Nov 23, 2006 12:30 am

How to call sqlservers stored proc?

Post by ds_ashish »

Hi All,

i want to call sqlserver stored prco in Datastage, but the problem is STP stage allows anly oracle,DB2 and sybase stored proc. :shock: and ODBC stage don't have the outlink in it.

My requirement is to set some parameter to stored proc as input and take some return parameter as output.

What needs to be done. please please reply soon your best comments to me. :cry:

Thanks in Adv. :wink:
Top
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:
Contact kcbland

Post by kcbland »

Sequence job, command stage, ssh to SQL-Server host, isql/osql command line, SQL script, evaluate results.
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Re: How to call sqlservers stored proc?

Post by chulett »

ds_ashish wrote:What needs to be done. please please reply soon your best comments to me. :cry:
And here I was hoping I could wait for awhile and then reply with something completely off-topic and useless. :roll:

Your 'and take some return parameter as output' requirement means your only choice is to follow some form of Ken's plan. Either that or something totally InSAnE like rewriting the SP functionality in a DataStage job! :shock: [gasp!]

Never did see the fascination with stored procedures that some places have... grumble... grumble...
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Post by kumar_s »

ODBC stage don't have the outlink in it.
What you mean by this? If you drag a output link from a ODBC, you should get one. If you need to pass some parameter, drag a link from a transformer stage placed upfront. The value can be Job parameter. Make sure you limit it number of row to 1.
And why you need to create a duplicate topic for the same?
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Interesting... I thought they meant the ODBC stage did not support the capture of returned result sets from a stored proc. I've never had a reason to look into this as it's not something I generally need to do or do for that matter.

However, in reading the online Help it does say the ODBC stage supports this functionality. It does look like you have to import the metadata first and there may be restrictions (ala the STP stage) on the nature of the SP and how exactly it returns rows, but it allegedly can be done.

So, rethink your 'ODBC stage don't have the outlink on it' comment and give it a shot. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
Post Reply

Return to “IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)”