how do we execute an Oracle Stored Procedure from SQL Server 2005? As part of a SQL Server scripts we need to execute an Oracle 10g Stored Procedure and download data to SQL Server 2005
+1
A:
I believe you can still use SELECT * FROM OPENQUERY(ORA_SERVER, 'EXECUTE sp;') to acomplish this.
John Gietzen
2009-10-30 19:37:35
+1 Thank you John :) that helps a lot.
Kombucha
2009-10-30 19:56:27
John one quick question. I run the above scripts and it prompt me this error message:Cannot process the object "EXECUTE xxxx(SYSDATE - 40, SYSDATE );". The OLE DB provider "MSDAORA" for linked server "lumd" indicates that either the object has no columns or the current user does not have permissions on that object.How do I link to Oracle Server from SQL Server?
Kombucha
2009-10-30 20:03:24
Can you post the whole query? I would be inclined to believe that the error message is telling the truth. ;)
John Gietzen
2009-10-30 20:07:21
Also, what happens when you execute the query using Oracle SQL Developer?
John Gietzen
2009-10-30 20:08:37
Here:SELECT * FROM OPENQUERY(lumd, 'execute REP.T2t.collect_all(SYSDATE - 40, SYSDATE );')
Kombucha
2009-10-30 20:19:23
Can you confirm if you have successfully executed an oracle stored procedure from sql server using the open query function. If yes, How? because I am having problems executing stored procedures with arguments using the OPENQUERY Function.
Kombucha
2009-10-30 20:34:04
I'm not an oracle guru, but if you can `select 1 "Result" from dual` on success and `select 0 "Result" from dual` on failure, you can just check that.
John Gietzen
2009-10-30 21:22:21