Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2013-10-04 : 10:50:32
|
I have a simple Oracle SP, with no parameters which returns nothing, setup like:
CREATE OR REPLACE PROCEDURE MYSCHEMA.MYPROC AS BEGIN
INSERT INTO MYSCHEMA.MYTABLE SELECT ...; INSERT INTO MYSCHEMA.MYTABLE SELECT ...;
COMMIT; END;
GRANT EXECUTE ON MYSCHEMA.MYPROC TO MSUSER;
I also have a few views with SELECT granted to MSUSER on the Oracle box.
The SP works fine when executed through Oracle's SQLDeveloper. (It does come with the message 'anonymous block completed' - whatever that means.)
I then setup a Linked Server, ORACLEBOX, from a SQL2008R2 instance to the Oracle machine connecting as MSUSER.
I have no problems using the four part naming convention to select from the Oracle views.
When I run EXEC ORACLEBOX..MYSCHEMA.MYPROC from Management Studio,
If ORACLEBOX has Server Options of RPC True and RPC Out False I get:
Msg 7411, Level 16, State 1, Line 1 Server 'ORACLEBOX' is not configured for RPC.
If ORACLEBOX has Server Options of RPC True and RPC Out True I get:
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLEBOX" returned message "Unspecified error". Msg 7323, Level 16, State 2, Line 1 An error occurred while submitting the query text to OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLEBOX".
I am not an Oracle expert, but I would have thought this should have been straight forward as there are no IN or OUT parameters and no result set requiring a cursor.
Does anyone have an idea on how to get this to work?
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2013-10-07 : 05:35:30
|
If anyone is interested, I managed to get the proc to work by going:
EXEC ('CALL MYSCHEMA.MYPROC()') AT ORACLEBOX
I suspect there are better ways but this is good enough for me now. |
 |
|
|
|
|