SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Anyway to connect to Oracle on the fly without tnsnames entry?


Anyway to connect to Oracle on the fly without tnsnames entry?

Author
Message
Mr Corn Man
Mr Corn Man
SSC Eights!
SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)

Group: General Forum Members
Points: 856 Visits: 350
As the title indicates, I'm curious if anyone knows of a way to connect to an Oracle database from SQL 2008 SSMS, on the fly, using code logic as opposed to a tnsnames entry?

It is a ridiculous process here to get a simple change made to the tnsnames file on the SQL server, it can take a week to get done for work that takes 30 seconds. Therefore, I'm looking for a way to fill the void between requesting a change to be made and the change being implemented.

I'd like to be able to create a temporary Oracle connection directly within the SQL code to be used just for the current query being written.

Thanks for your help!
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100852 Visits: 14051
Regardless of method, first the Oracle client, at least the Instant Client, must be installed on the SQL Server box.

For a linked server connection, you'll need a TNSNAME. However, have you investigated either of these two options? This would be my first attempt, but I'm not sure if it will work.

Reply back if you find a solution and get it working. I could use it myself on occasion, but am too busy to dick around with it.

OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

https://msdn.microsoft.com/en-us/library/ms190312(v=sql.105).aspx


OPENDATASOURCE ( provider_name, init_string )

https://msdn.microsoft.com/en-us/library/ms179856(v=sql.105).aspx


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Mr Corn Man
Mr Corn Man
SSC Eights!
SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)

Group: General Forum Members
Points: 856 Visits: 350
Here is what ended up working:

SELECT *
FROM OPENROWSET('OraOLEDB.Oracle'
,'server_ip_address:port_number/database'
;'your_user_name'
;'your_password'
,'your_query, for example, SELECT * FROM DB.SCHEMA.TABLE')

In addition to this query, part of the solution required me to add a registry DWORD value for the OraOLEDB.Oracle provider. That registry value was DisallowAdHocAccess with a value of '0'. Without this registry addition non-SA logins received an error:

Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'OraOLEDB.Oracle' has been denied. You must access this provider through a linked server.

Once the registry was updated, everything works like a charm.

Thanks!
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100852 Visits: 14051
Mr Corn Man (5/20/2015)

...
In addition to this query, part of the solution required me to add a registry DWORD value for the OraOLEDB.Oracle provider. That registry value was DisallowAdHocAccess with a value of '0'.
...

You had to make this registry entry on the Windows server box? How so without being an Admin ?


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Mr Corn Man
Mr Corn Man
SSC Eights!
SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)

Group: General Forum Members
Points: 856 Visits: 350
Yes, the registry change was on the server. I have access to do that, but not to the tnsnames. Go figure.
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100852 Visits: 14051
Mr Corn Man (5/20/2015)
Yes, the registry change was on the server. I have access to do that, but not to the tnsnames. Go figure.

It's neat that you got openrowset on Oracle working without going through TNSNAMES. You should submit an article here on sqlservercentral describing how to setup Oracle client connectivity, required server settings, and everything, so those of us who occasionally need to pull data from Oracle can ramp up quickly.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
net.flux
net.flux
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 6
I just logged in to say, thank you. This is the only place I found on the internet with the correct answer. I used to connect to a Oracle instance on AWS. I ran a, 10 minute, complicated query and dropped the results neatly into MS Sql server, without having to learn SSDT.
kevaburg
kevaburg
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15452 Visits: 1280
Eric M Russell (5/20/2015)
Mr Corn Man (5/20/2015)

...
In addition to this query, part of the solution required me to add a registry DWORD value for the OraOLEDB.Oracle provider. That registry value was DisallowAdHocAccess with a value of '0'.
...

You had to make this registry entry on the Windows server box? How so without being an Admin ?


Hi,

to be honest this answer and solution is more complicated than the normal solution of creating a TNS_ADMIN System variable and installing the instant Client.

All that needs to be done in addition to that is to create a linked Server in SQL Server and run the query with OPENQUERY.

Regards,
Kev
maudzedunjp
maudzedunjp
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 0
Good advices, thx for help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search