SQL Server stored procedure

xch2005 · Post by **xch2005** » Mon Sep 26, 2011 5:36 am

Hi,

We have been converting the Sybase source to SQL Server source in our DS parallel jobs (DataStage 7.5.1) and we encountered an issue when tried to convert the stored proc. used earlier in Sybase to now with the SQL Server ones.

It was used with Stored procedure stage (STP) in the jobs, unfortunately the STP does not support SQL Server. We can use the ODBC stage (server job) to execute the stored proc but there is a limitation that if there is return value from the proc.

We did try to use ODBC and was successful when there are no input/output parameters but failed when there are these parameters.

Please let us know if any of you had workaround for this in DS 7.5.1.

Though in couple of post i saw that it was mentioned as possible but the steps were not there.
Appreciate your help on this.

Thanks

chulett · Post by **chulett** » Mon Sep 26, 2011 7:39 am

As noted, you can use ODBC if there are input parameters but there's no way to return anything more than a status to the job. Seems to me you need to look into doing something from the command line, a script to call whatever CLI you have for SQL Server. This will be complicated by the fact that your server is on UNIX while the procedure in question will be running on a Windows server.

Hopefully someone has done this before and has some words of wisdom for you. I haven't.

xch2005 · Post by **xch2005** » Tue Apr 17, 2012 2:45 pm

To achieve we moved on to DataStage 8.5 and now it is all set.

Thanks