What am I doing wrong: executed an oracle stored procedure from sql server using the open query function.

Question

syntax: EXECUTE ( begin (LUMD_REP_APPGRC.peopledb_T2t.collect_all(SYSDATE - 40, SYSDATE ); end;) AT LUMD;

error: Incorrect syntax near the keyword 'begin'.

Answer 1

If the code between the brackets in EXECUTE () is meant to be PL/SQL then it should read:

EXECUTE ( begin LUMD_REP_APPGRC.peopledb_T2t.collect_all(SYSDATE - 40, SYSDATE ); end;) AT LUMD;

However, I'm pretty sure the EXECUTE argument should be a string:

EXECUTE ( 'begin LUMD_REP_APPGRC.peopledb_T2t.collect_all(SYSDATE - 40, SYSDATE ); end;') AT LUMD;

Answer 2

Finally tracked down a few solutions – the key to the problem (for us) is that by default RPCs are disabled for linked servers. The parameters for Rpc, Rpc Out, and Use Remote Collation need to be set to true. More info:

http://blog.sqlauthority.com/2007/10/18/sql-server-2005-fix-error-msg-7411-level-16-state-1-server-is-not-configured-for-rpc/

The solution you use will depend upon the procedure output requirements. The first example returns an output value. The second example no output values are returned (data is collected in a subsequent query).

Example 1

The procedure T2T_collect_all has two input parameters (start and end dates) and one output parameter (row count).

DECLARE @l_i_parameter1 varchar(10)

DECLARE @l_i_parameter2 varchar(10)

DECLARE @l_i_parameter3 varchar(10)

DECLARE @l_i_parameter4 varchar(10)

DECLARE @l_o_parameter1 integer

SET @l_i_parameter1 = '2009/10/01'

SET @l_i_parameter2 = 'yyyy/mm/dd'

SET @l_i_parameter3 = '2009/12/31'

SET @l_i_parameter4 = 'yyyy/mm/dd'

SET @l_o_parameter1 = 0

EXECUTE ( 'begin T2T_collect_all(to_date(?, ?), to_date(?, ?), ? ); end;',

      @l_i_parameter1, 

      @l_i_parameter2,

      @l_i_parameter3, 

      @l_i_parameter4,

      @l_o_parameter1 OUTPUT

) AT ORA_DB;

More Info: http://blogs.msdn.com/joaquinv/archive/2008/10/23/execute-oracle-stored-procedure-in-sql-server.aspx

Example 2a

The procedure T2T_collect_allx has only two input parameters (start and end dates).

EXECUTE ('begin T2T_collect_allx (SYSDATE - 40, SYSDATE); end;') ORA_DB;

Example 2b

SELECT * FROM OPENQUERY(ORA_DB, 'begin T2T_collect_allx (SYSDATE - 40, SYSDATE ); end;')