1

I have an existing stored procedure in SQL Server 2005. This stored procedure is used by a SSRS report. It displays following columns from SQL Server – Emp Name, Emp ID, Dept ID, Dept Name.

Now I need to add another column in the report – named Business Unit. But this column is in an Oracle 8i database.

What is the best way to retrieve this column information in the SQL Server stored procedure?

It seems like OPENROWSET - SQL Server 2005 may not be a good option.

OPENROWSET: Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead.

The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.

Reference

  1. Insert results of a stored procedure into a temporary table
  2. Executing an Oracle Stored Procedure from SQL Server 2005
  3. executed an oracle stored procedure from sql server using the open query function
Community
  • 1
  • 1
LCJ
  • 22,196
  • 67
  • 260
  • 418
  • 1
    `linked server` is where you should start with. – Rahul Aug 04 '14 at 21:07
  • 1
    I'd create an organizational database that brings together all the information about your employees in one database if this is something that needs to be done regularly. – Sam Aug 04 '14 at 21:17

0 Answers0