May 19, 2015 at 7:22 am
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!
May 19, 2015 at 7:48 am
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
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 20, 2015 at 8:16 am
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!
May 20, 2015 at 8:31 am
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 ?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 20, 2015 at 8:39 am
Yes, the registry change was on the server. I have access to do that, but not to the tnsnames. Go figure.
May 20, 2015 at 8:55 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 11, 2016 at 9:06 am
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.
November 14, 2016 at 6:35 am
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
April 12, 2017 at 5:13 am
Good advices, thx for help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply