July 29, 2009 at 1:57 am
Hi everyone
I've read through a number of posts on this issue but haven't found anything that has helped me so far and I also tried posting on the Oracle and Microsoft forums directly and no answer so I'm really hoping someone here has had some experience wit this. The machine I'm using is running SQL Server 2008 64bit and the linked server I've created to Oracle 10G is using OraOLEDB.Oracle. If I test the connection in Management Studio it succeeds and for any queries I have using OpenQuery to select from the Oracle DB I have no problem getting results.
The Oracle 32bit client and 64bit client versions are installed on the machine.
My problem is that I need to exec an Oracle SP which in turn inserts into various Oracle tables. I previously had this all working fine on 32bit SQL Server 2000 installation connected to Oracle 10G and when I tested the same code on SQL Server 2008 on 32 bit (both of these used the Microsoft OLE DB Provider for Oracle which isn't available in SQL 2008 64 bit apparently). Using the same code in SQL Server 2008 64 bit on the Oracle Provider for OLE DB I get the following error:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "{CALL INTF.INTF_TW_PO_REQ_INTERFACE.MAIN(NULL, 15.000000, 0.000000, 'BLPO0099998', 'BLPO0099998', '', '', 39804, 21883, 'BLPO0099998', 249, 1, 950, 'Main', 190506, 37336, '1', TO_DATE( '20090706','YYYYMMDD' ), TO_DATE( '20090706','YYYYMMDD' ), 'INCOMPLETE',37336, {RESULTSET 1, P_Success})}". The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" indicates that either the object has no columns or the current user does not have permissions on that object
The call is made by the following piece of code:
Declare @execCMD nvarchar(4000),
@oracleCmd nvarchar(4000)
Select @oracleCmd = '{CALL INTF.INTF_TW_PO_REQ_INTERFACE.MAIN(NULL, 15.000000, 0.000000, ''''BLPO0099998'''', ''''BLPO0099998'''', '''''''', '''''''', 39804, 21883, ''''BLPO0099998'''', 249, 1, 950, ''''Main'''', 190506, 37336, ''''1'''', TO_DATE( ''''20090706'''',''''YYYYMMDD'''' ), TO_DATE( ''''20090706'''',''''YYYYMMDD'''' ), ''''INCOMPLETE'''',37336, {RESULTSET 1, P_Success})}'
SET @execCmd = N'SELECT P_Success FROM OPENQUERY( ORACLE, ''' + @oracleCmd + ''')';
EXECUTE (@execCmd);
I also tested the above on the SQL 2008 32bit instance first with the Microsoft OLE DB Provider for Oracle (and it worked fine) and then with the Oracle Provider for OLE DB (and it didn't work - same error about no columns or not permissions) So I've gotten as far as knowing that the issue is with the provider but I have no idea how to get around it.
Thank you for any help ...
Alida
February 18, 2010 at 6:46 pm
Please check the following Link:
http://forums.oracle.com/forums/thread.jspa?messageID=3278968
I hopw that it helps..
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 18, 2010 at 6:46 pm
Please check the following Link:
http://forums.oracle.com/forums/thread.jspa?messageID=3278968
I hopw that it helps..
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply