Wednesday, July 27, 2011

Query Oracle from SQL Server w/o a permanent Linked Server entry

Need to query a table on an Oracle box from SQL Server?

If your DBA won't let you create a linked server, this option may work for you. You'll need hard code your credentials in :(.



SELECT *
FROM OPENROWSET('MSDAORA',
'DATABASE_NAME';
'USER_TO_CONNECT_AS';'USER_PASS', 'select * from dual')
AS test

2 comments:

  1. pweatherbyJuly 27, 2011 at 3:49 PM

    You would think a DBA would prefer creating a Linked-server over standardizing hard-coding credentials into dynamic queries.

    ReplyDelete
  2. SteveJuly 27, 2011 at 3:50 PM

    A smart DBA yes.

    ReplyDelete