Passing Stored Procedure from SQL Server to ORACLE linked server

Question

I'm trying to pass the following SQL query as a linked server stored procedure, but I get an error, please advise, thanks in advance!

Msg 7215, Level 17, State 1, Line 17
Could not execute statement on remote server 'CAFUAT'.

Explanations:

CAFUAT = Oracle Linked server
CDBWEB = User
STP_CAF_PERSON = stored procedure

Code:

DECLARE @AVEMAIL VARCHAR(255) 
DECLARE @ANCDPXID NUMERIC
DECLARE @AVCDPURN VARCHAR(12) 

SELECT @AVEMAIL  = '[email protected]' 
SELECT @ANCDPXID  = null 
SELECT @AVCDPURN  = null 

EXECUTE ('BEGIN CAFUAT.CDBWEB.STP_CAF_PERSON(?,?,?,?); END;', @AVEMAIL,@ANCDPXID,@AVCDPURN) AT CAFUAT

DECLARE @PrintVariable VARCHAR (4000)

SELECT @PrintVariable =  'ANCDPXID ='  +  CONVERT(VARCHAR(23), @ANCDPXID) 
PRINT @PrintVariable

SELECT @PrintVariable =  'AVCDPURN ='  +  ISNULL(CAST(@AVCDPURN AS VARCHAR(8000)), '' ) 
PRINT @PrintVariable

Ha. That unformatted GO at the end of your post makes it sound like you're ordering us to get to work :) — sstan
– sstan, Commented Aug 18, 2015 at 14:47
Just a thought, you've got four question marks in your statement, but only 3 parameters listed afterwards; are you missing a parameter? Also, is "CDBWEB" the schema user or a package name? — Boneist
– Boneist, Commented Aug 18, 2015 at 14:56
Hi Boneist, you are correct, i have removed the extra question mark. CDBWEB is the user which has privileges to execute the SP on the linked ORACLE server. — Rishi Bhachu
– Rishi Bhachu, Commented Aug 18, 2015 at 15:01
AVCDPURN = Msg 7215, Level 17, State 1, Line 16 Could not execute statement on remote server 'CAFUAT'. — Rishi Bhachu
– Rishi Bhachu, Commented Aug 18, 2015 at 15:34

Rishi Bhachu · Accepted Answer · 2015-09-03 08:47:16Z

1

just to update, below is the working query, plus i resintalled the 32-bit Oracle driver;

EXECUTE('Call STP_CAF_PERSON(?,?,?)', @AVEMAIL OUTPUT, @ANCDPXID OUTPUT, @AVCDPURN OUTPUT) AT [CAFUAT]

answered Sep 3, 2015 at 8:47

Rishi Bhachu

1722 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing Stored Procedure from SQL Server to ORACLE linked server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related