CODEWITHSUNDEEP
sql-serveroracle-databasestored-procedures
Rishi Bhachu
Rishi Bhachu

Reputation: 172

Passing Stored Procedure from SQL Server to ORACLE linked server

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:

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

Upvotes: 0

Views: 1571

Answers (1)

Rishi Bhachu
Rishi Bhachu

Reputation: 172

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]

Upvotes: 1

Related Questions