Execute Oracle Stored Procedure/Query FROM SQL Server

Execute Oracle Stored Procedure/Query FROM SQL Server Most of the time your project requirement to work with two databases, In some scenarios you want to execute one database stored procedure from another database. Here I am giving the example to execute Oracle stored procedure from SQL server database. To achieve this first you have to set up link from SQL server to Oracle How to link Oracle Database from SQL server http://blogs.techrepublic.com.com/datacenter/?p=133 http://support.microsoft.com/kb/280106 http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx To execute an Oracle Stored Procedure from SQL Server you can try the following: You can make a sample package named Project_Package with the procedure named TestProcedure: Package Header: PROCEDURE TestProcedure ( I_Parameter1 IN NUMBER, I_Parameter2 IN NUMBER, O_Parameter1 OUT NUMBER, O_Parameter2 OUT NUMBER ); Package Body: PROCEDURE TestProcedure ( I_Parameter1 IN NUMBER, I_Parameter2 IN NUMBER, O_Parameter1 OUT NUMBER, O_Parameter2 OUT NUMBER ) AS BEGIN O_Parameter1 := I_Parameter1 + 1; O_Parameter2 := I_Parameter2 + 1; END TestProcedure; Then we create a SQL Server Stored Procedure to Wrapp the Oracle Store Procedure Invocation, the T-SQL inside the procedure for this sample is the following: DECLARE @l_i_parameter1 INTEGER DECLARE @l_i_parameter2 INTEGER DECLARE @l_o_parameter1 INTEGER DECLARE @l_o_parameter2 INTEGER SET @l_i_parameter1 = 5 SET @l_i_parameter2 = 10 SET @l_o_parameter1 = 0 SET @l_o_parameter2 = 0 EXECUTE ( 'begin Project_Package.TestProcedure(?,?,?,?); end;', @l_i_parameter1, @l_i_parameter2, @l_o_parameter1 OUTPUT, @l_o_parameter2 OUTPUT) AT DBLINK_NAME; SELECT @l_o_parameter1, @l_o_parameter2 I Hope this sample illustrate the concept. Also I want to mention that this sample only works with simple output parameters, if you are trying to query for result sets I recommend trying the OPENQUERY statement instead. OR EXEC mySchema.myPackage.myProcedure@myRemoteDB( 'someParameter' ); Using OPENQUERY If exists (select * from master..sysservers where srvname = 'OracleDemo') exec sp_dropserver 'OracleDemo' go exec sp_addlinkedserver @server = N'OracleDemo', @srvproduct = N'', @provider = N'SQLOLEDB', @datasrc = @@servername go select * into #Ta from OPENQUERY(OracleDemo, 'exec yourSproc') select * from #Ta drop table #Ta go USING SP_EXECUTEESQL DECLARE @rc INT DECLARE @v_sql NVARCHAR(4000) DECLARE @vc_servername VARCHAR(100) DECLARE c_myservers CURSOR FOR SELECT servername FROM listservers WHERE active=1 FOR READ ONLY OPEN c_myservers FETCH NEXT FROM c_myservers INTO @vc_servername WHILE (@@FETCHSTATUS = 0 ) BEGIN SET @v_sql = N'SELECT name, id FROM ['+@vc_servername + '].dbname.joe.titles' EXEC @rc = sp_executesql @v_sql -- if @rc <> 0 --- etc... FETCH NEXT FROM c_myservers INTO @vc_servername END CLOSE c_myservers DEALLOCATE c_myservers COMPARISION Rather your choice is between: SELECT @sql = 'SELECT .... FROM ' + @server + 'catalog.schema.tbl' and SELECT @sql = 'SELECT ... FROM OPENQUERY(' + @server + ', ' + '''SELECT ... FROM catalog.schema.tbl'')' That is, accessing the table in four-part notation, or running a pass-through query.