Executing Stored Procedures Remotely

You can't use a linked server to execute an Oracle stored procedure from SQL Server. Even OPENQUERY and OPENROWSET can't get around that limitation. But you can update an Oracle table from a linked-server query. And if that Oracle table has an UPDATE trigger associated with it, any update will cause the trigger to fire. Furthermore, the trigger can execute a stored procedure. Consequently, by issuing an UPDATE statement, which is a permissible operation, you can indirectly make a stored procedure execute. The code in Listing A demonstrates this process.

First, the code creates an Oracle table. Next, it initializes the table by inserting a row and issuing a COMMIT. Then, the code creates an AFTER UPDATE trigger on the table. From Query Analyzer, you can then execute the following code to trigger the execution of the stored procedure:

UPDATE ORADB..SCOTT.EXEC_PROC_AFTER_UPDATESET UPDATE_THIS_TO_EXEC = 1