Page 1 of 1
Running Stored Procudures from MSSQL server in DataStage
Posted: Tue Jan 30, 2007 11:51 am
by samu
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.
Posted: Tue Jan 30, 2007 11:54 am
by I_Server_Whale
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.
Posted: Tue Jan 30, 2007 12:24 pm
by DSguru2B
Even the before/after sql tab in the DRS stage.
Posted: Tue Jan 30, 2007 1:04 pm
by samu
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
Posted: Tue Jan 30, 2007 1:08 pm
by DSguru2B
What error message are you getting.
Also try
Code: Select all
call <schemaname>.storedProcName()