Running sp's through SQL Server

[bangorlad@hotmail.com]

Does the stored procedure stage suepport SQL Server. The only database vendor I can access is ORACLE. If this is the case, is there any other way of running SQL without having to define any input or output.

[chulett]

What version of DataStage? I was under the impression that the 'latest' version had support for more than just Oracle... or is that still coming RSN?

[gateleys]

WHAATTT!! Can't I invoke a stored procedure written in SQL Server from DataStage?? I have an ODBC stage which should be made use of to invoke the SP. If it is not possible, please tell me how I can get around this. And, I don't seem to have a Stored Proc Stage in my version of DataStage (7.1r1).

If it is not possible, can I use the user defined SQL to write SP? My SP contains T-SQL statements and a whole bunch of conditions.

[kcbland]

The ODBC stage supports stored procedures, what's the big deal?

[bangorlad@hotmail.com]

Yes, I know that you can call an sp throuugh an ODBC stage but what if you just want the stage do conduct a piece of SQL work i.e truncate a table, drop a table, create a table, etc rather than using the sp to output data.

[kcbland]

Then I suggest you execute a command line and run the SQL script or PL/SQL script that way. You have infinitely more options for error handling and complicated scripts.

[I_Server_Whale]

Hi,

I had a similar situation where I had execute the sp script. And I did it by executing the SQL script through a batch file in a before/after job subroutine.

But you need to have the OSQL.exe utility for SQL Server. This is very easy.

Thanks,
Naveen.

FYI, version 7.5.1A stored procedure stage supports only Oracle, DB2 and Sybase databases. SQL Server is not supported.

[bangorlad@hotmail.com]

You may have noticed that I am using a UNIX implementation of Datstage and do not have direct access to osql.

Are you suggesting that I write a shell script that remotely calls a script on the database server that runs osql.

If you could tell me how to do this and give me the code, I would be very grateful.

Thanks.

[kcbland]

You can run commands on remote machines using the remsh, rsh, or rlogin commands. It works for running commands on remote Windoze or Unix machines. The issue is permissions.

It may sound like a gimmick, but you can always put triggers on a table that do the things you need done. Then, insert the appropriate row with keywords that activate the trigger and run the logic required. That way you don't do anything fancy like remote commands.

The alternative is to use the ODBC stage to "do" something and stream a single row to the sp.

[bangorlad@hotmail.com]

Thank you, that was very helpful.