DSXchange

How do I call a Stored Procedure in DS. I am trying to update a datetable with the most recent date.

My job uses that date in determining what data to pull from another table. I have included the matching criteria in the selection tab as follows:

sql_simulcast.dbo.Settlement.Location = 'CAL'
and
sql_simulcast.dbo.Settlement.Location = sql_simulcast.dbo.DateLookup.LocationCode
and
sql_simulcast.dbo.Settlement.Date > sql_simulcast.dbo.DateLookup.LkupDate

All the separate tables are defined and this statement works.

Before or after this runs I would like to update the DateLookup table.

Any suggestions?

Thanks

Look at the ODBC stage. For input, there is a stored procedure radio button. For output there is a "Call Stored Procedure" update action.

I am not sure of the options the SQL Server plugin offers, but check the documentation.

There isn't a "SQL Server plugin" for SELECT, so you use ODBC or OLEDB stage type. So you can happily use "Call Stored Procedure" as the method for retrieving data.

Calling a Stored Procedure before retrieving data is a different problem entirely, and not really supported directly by DataStage. Since you're on Windows, and don't have ODBC licensing issues, you could create a before-stage subroutine that uses BCI functions to CALL your stored procedure to update the DateLookup table.

Or you could create a DataStage job that does the same thing. Create a job sequence that calls that one first (to update the DateLookup table), and the other one depending on the successful completion of the first.

Not sure about SQL Server, but with Oracle OCI stage can call SPs and Functions just by embedding the SP call into a select statement of a reference link such as: -

SELECT funcX(:1, :2) REGION,
:1,
:2
FROM dual

Should you wish to call it only once during a job, you could create a "parallel job" inside your main job (or indeed seperate it and call it from some BASIC in a wrapper job) consisting of an OCI stage, a dummy transformer stage and a sequential file stage to output to. The OCI could have something like: -

select SPX(), 'dummy' from sys.dual

Pass the dummy through the transformer.
I have also use this for passing parameters out of jobs into files...such as dates for other jobs to pick up later.

rgds,
B.