DSXchange

Hi all !!

I am trying to implement stored procedures (SQL Server) in Datastage..Most of my stored procs update/delete/insert rows into/from multiple tables..and donot take any input parameters. Also the procedures do not give out any output except a return code indicating the success or failure. After spending some time on the documentation , I understood that I need to have atleast one input parameter as input..so I modified the stored procedure so that It takes one input but does nothing with it.. My question is what do I need to have in

1. Input tab
2 Parameter tab

My job flow is something like

ODBC ----> Transformer-------> Seq File

It repeatedly throws me an error that SQL statement doesnot have correct number of columns in the result set ..If someone can throw light on this , that would be a great help

Thanks in advance
Santosh

You must have the same set of columns defined on the Outputs link of the ODBC stage as are delivered by the stored procedure.

DataStage only deals with stored procedures that manipulate rows of data; either deliver them or accept them. In your case DataStage wants the stored procedure to deliver one or more rows of data.

Your description suggests that you are trying to accomplish something that is not returning any data. What was the result when you imported this stored procedure definition? What columns were defined?

Perhaps you can make use of a 'trick' people have been using with Oracle and stored procedures? It's detailed (amongst other places in the forum) here.

You could always take the logic from the stored procedure and build a DataStage job to accomplish the same thing, I would think. That would put the maintenance of it back into the ETL realm and not dependant on someone with PL/SQL (whatever the SQL Server equivalent is) skills.

Thanks for the response ray !!

[quote="ray.wurlod"] What was the result when you imported this stored procedure definition? What columns were defined?[/quote]

When I imported the definition, It said something like

"Stored procedure has failed to report a result set . If this stored procedure accepts parameters you may enter them below and click OK to attempt to generate a result set. Clicking Cancel accepts the current arugement list but doesnot execute the procedure"

Then I entered some value for my dummy input param and it got imported. After importing the definition I added an input column which has exactly same datatype as the input parameter and saved the table definition. Then when I tried to run it, It gave the same error message that SQL statement has incorrect number of result columns.. Do I have to return something from the stored procedure explicitly ? what am I doing wrong here ..

Thanks

[quote]You could always take the logic from the stored procedure and build a DataStage job to accomplish the same thing, I would think. That would put the maintenance of it back into the ETL realm and not dependant on someone with PL/SQL (whatever the SQL Server equivalent is) skills.[/quote]

Craig !! I wish I could ..but the procedures are so big ( abt 10 pages each) that I am a little bit inclined towards calling stroed procs in ETL..also the time constraint..

Thanks

Too bad.

Did you look at the other thread? Any chance of trying the 'CALL' statement as custom SQL or in a 'before' tab? Seems like you could add a bogus select to the actual stage, select 'sysdate' or something out to the flat-file so that DataStage is happy.