Running Stored Procudures from MSSQL server in DataStage

samu · Post by **samu** » Tue Jan 30, 2007 11:51 am

Hi,

Just wanted to know is there any way we can run the stored procedures sitting on MS SQL server 2000 on Datastage. Any suggestions are most welcome.

Thanks in advance.

I_Server_Whale · Post by **I_Server_Whale** » Tue Jan 30, 2007 11:54 am

Yes! Use the ODBC stage to run SQL Server stored procedures. This has been covered many a time. Search the forum for more info.

Whale.

DSguru2B · Post by **DSguru2B** » Tue Jan 30, 2007 12:24 pm

Even the before/after sql tab in the DRS stage.

samu · Post by **samu** » Tue Jan 30, 2007 1:04 pm

DSguru2B wrote:Even the before/after sql tab in the DRS stage.

Hi Dsgurus,

You had mentioned we can run the stored procedures in Before/After SQL tab in DRS stage, I had tried this but i am getting an error message. And the SQL i had used is 'EXECUTE sp_name'. So could you please let me know exact SQL for calling the stored procedure in the before/after SQL tab that would be great. Iam fairly new to this running the stored procedures so any help is much appreciated. My stored procedure doesn't return any values it just updates the columns that are there is the table.

Thanks in advance

DSguru2B · Post by **DSguru2B** » Tue Jan 30, 2007 1:08 pm

What error message are you getting.
Also try

Code: Select all

call <schemaname>.storedProcName()