I just upgraded our SQL server to 2008 64bit. We used to be able to connect to oracle via link server using driver MSDAORA to pull the data. Now with 64 bit the drive MSDAORA is no longer available and replaced with OraOLEDB.Oracle. The new driver works but only for simple select statement it no longer works with calling store procedure which I enclosed a code below. I wonder how I can call oracle store procedure with new driver OraOLEDB.Oracle. Basically I just want to know how to call a store procedure with a new driver.


This is a code we used to run

declare @vchrOracleCall Varchar(8000)

SET @vchrOracleCall = 'SELECT * FROM OPENQUERY(TEST , ''{CALL TEST_TABLE(''{resultset 1500,result1, result2})}'')'
EXEC (@vchrOracleCall)