Unanswered: Pulling information from an oracle database into my SQL Server database
I have been tasked with pulling information from a clients Oracle database in a stored procedure to pass out information. This needs to be a simple stored procedure but i am not sure as to how to point it to pull from a different server and use the ODBC to connect and pull this information. Any suggestions??
I took a look at this but was told by my supervisor we are not creating a linked server he just wants to use the ODBC to go from SQL to Oracle, and get the info need. So i am basically looking for a script on how to access the ODBC to perform this.
If you don't (or can't) use Linked Servers then look at OPENROWSET. All you need is the ODBC driver for Oracle.
Use OPENROWSET with an object and the OLE DB Provider for ODBC
This example uses the OLE DB Provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized with a provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog.schema.object syntax is used to define the row set returned.
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname