Calling Oracle Stored Procedure from within SQL Server Stored Procedure

Calling Oracle Stored Procedure from within SQL Server Stored Procedure

Post by sdye » Sat, 27 Sep 2003 15:02:47


I am attempting to execute a Oracle stored procedure from with in a
sql server stored procedure via a linked server. The oracle procedure
is expecting two date parameters. I receive the follwing error message
when I execute the below. I have tested the oracle stored procedure
from within VB and returns resultset correctly as expected.

Stuart

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '20030720'.


declare @aStr NVarChar(500), @StartDay datetime, @EndTime datetime
select @Startday = '20-Jul-03' select @Endtime = '26-Jul-03'

set @aStr = 'select * from openquery(INFODEV, ''{Call
ETAPP.ET_SETCPDATA.SetCPData(''' + convert(varchar, @StartDay, 112) +
''', ''' + convert(varchar, @EndTime, 112) + ''', {resultset 2000,
BusUnit, Participant, State, RegionID, Status, PeriodEnd, DB_MW,
RRN_MW})}'')'

exec (@astr)