{"id":509,"date":"2015-10-06T07:30:45","date_gmt":"2015-10-06T07:30:45","guid":{"rendered":"http:\/\/www.dbafox.com\/?p=509"},"modified":"2018-09-18T06:59:11","modified_gmt":"2018-09-18T05:59:11","slug":"openrowset-query-oracle-sql-server-using-linked-server","status":"publish","type":"post","link":"https:\/\/dbafox.com\/?p=509","title":{"rendered":"OPENROWSET – Query Oracle from SQL Server using a linked server"},"content":{"rendered":"

Query Oracle from SQL Server using a linked server<\/h2>\n

This post describes the basics of querying an Oracle database from Microsoft SQL Server.<\/p>\n

Oracle data can be queried from SQL Server via a linked server defined in Microsoft SQL Server<\/p>\n

Install oracle client on the SQL Server Database Machine<\/h3>\n

The Oracle client is required to expose the OraOLEDB Oracle provider and for subsequent connections to Oracle databases via OracleNet – Oracle transparent network substrate (tnsnames etc.)<\/p<\/p>\n

Create an entry for the Oracle database in the tnsnames.ora Oracle Net File<\/h3>\n
\r\nORCL=(DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)   (SERVICE_NAME = ORCL)))\r\n<\/pre>\n
Create a linked server in SQL Server<\/h3>\n
\r\nUSE [master]\r\nGO\r\nEXEC master.dbo.sp_addlinkedserver @server = N'ORCL', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORCL'\r\nGO\r\nEXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORACLE_ORCL',@useself=N'False',@locallogin=NULL,@rmtuser=N'orcl_user',@rmtpassword='orcl_pwd'\r\nGO\r\n<\/pre>\n
Query the Oracle data<\/h3>\n
Grant the Oracle user “select any dictionary” privileges for test purposes. oracle data can be queried using “openrowset” or directly in a SQL statement using the linked server<\/p>\n
\r\n-- using openrowset\r\nselect * from openrowset('OraOLEDB.Oracle','ORCL';'orcl_user';'orcl_pwd','select * from dual')\r\n\r\n-- direct sql using [&lt;LINKED_SERVER&gt;]..[OWNER].[OBJECT_NAME]\r\nselect * from [ORCL]..[SYS].[DUAL]\r\n<\/pre>\n
Provider Properties<\/h3>\n
SQL queries can be executed “inprocess” or not “inprocess”.<\/p>\n
\"\"<\/p>\n
\"\"<\/p>\n
“inprocess” may cause sql server crash if there are issues with the provider, so it is recommended to execute “out of process”. To run “out of process”, permissons are required on the MSDAINITIALIZE DCOM component, to initialize the provider.<\/p>\n
Run dcomcnfg from cmd<\/h4>\n
\"\"<\/p>\n
Navigate to MSDAINITIALIZE component<\/h4>\n
\"\"<\/p>\n
 <\/p>\n
\"\"<\/p>\n
Edit the permissions to allow groups or specific users to use the provider according to your requirements.<\/p>\n
Ad-Hoc Distributed Queries<\/h3>\n
If there is an error reported when the “openrowset” sql statement is executed, as follows:<\/p>\n
Msg 15281, Level 16, State 1, Line 3\r\nSQL Server blocked access to STATEMENT 'OpenRowset\/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.<\/pre>\n
The use of ‘Ad Hoc Distributed Queries’ can be enabled by an administrator as decribed im the error message.<\/p>\n
\r\nsp_configure 'Ad Hoc Distributed Queries',1  \r\nreconfigure \r\n<\/pre>\n
Or use SSMS GUI, right-click on instance, select Facets, navigate to the “Surface Area Configuraion in the Facet drop-down list, and set Facet property “AdHocRemoteQueriesEnabled” to true”<\/p>\n
Add User to DCOM Users Group<\/h3>\n
\"\"<\/h2>\n
 <\/p>\n
<\/h3>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Query Oracle from SQL Server using a linked server This post describes the basics of querying an Oracle database from Microsoft SQL Server. Oracle data can be queried from SQL Server via a linked server defined in Microsoft SQL Server … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"pgc_sgb_lightbox_settings":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[24,28],"tags":[],"class_list":["post-509","post","type-post","status-publish","format-standard","hentry","category-ms-sql-server","category-oracle-technical"],"yoast_head":"\nOPENROWSET - Query Oracle from SQL Server using a linked server - dbafox<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbafox.com\/?p=509\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"OPENROWSET - Query Oracle from SQL Server using a linked server - dbafox\" \/>\n<meta property=\"og:description\" content=\"Query Oracle from SQL Server using a linked server This post describes the basics of querying an Oracle database from Microsoft SQL Server. Oracle data can be queried from SQL Server via a linked server defined in Microsoft SQL Server … Continue reading →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbafox.com\/?p=509\" \/>\n<meta property=\"og:site_name\" content=\"dbafox\" \/>\n<meta property=\"article:published_time\" content=\"2015-10-06T07:30:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-09-18T05:59:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbafox.com\/wp-content\/uploads\/provider_properties.png\" \/>\n<meta name=\"author\" content=\"Ray Fox\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ray Fox\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbafox.com\/?p=509\",\"url\":\"https:\/\/dbafox.com\/?p=509\",\"name\":\"OPENROWSET - Query Oracle from SQL Server using a linked server - dbafox\",\"isPartOf\":{\"@id\":\"https:\/\/dbafox.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbafox.com\/?p=509#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbafox.com\/?p=509#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbafox.com\/wp-content\/uploads\/provider_properties.png\",\"datePublished\":\"2015-10-06T07:30:45+00:00\",\"dateModified\":\"2018-09-18T05:59:11+00:00\",\"author\":{\"@id\":\"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb\"},\"breadcrumb\":{\"@id\":\"https:\/\/dbafox.com\/?p=509#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbafox.com\/?p=509\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/dbafox.com\/?p=509#primaryimage\",\"url\":\"https:\/\/dbafox.com\/wp-content\/uploads\/provider_properties.png\",\"contentUrl\":\"https:\/\/dbafox.com\/wp-content\/uploads\/provider_properties.png\",\"width\":240,\"height\":200},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbafox.com\/?p=509#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbafox.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"OPENROWSET – Query Oracle from SQL Server using a linked server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbafox.com\/#website\",\"url\":\"https:\/\/dbafox.com\/\",\"name\":\"dbafox\",\"description\":\"DBA Technical Information\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbafox.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb\",\"name\":\"Ray Fox\",\"sameAs\":[\"http:\/\/www.dbafox.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"OPENROWSET - Query Oracle from SQL Server using a linked server - dbafox","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbafox.com\/?p=509","og_locale":"en_GB","og_type":"article","og_title":"OPENROWSET - Query Oracle from SQL Server using a linked server - dbafox","og_description":"Query Oracle from SQL Server using a linked server This post describes the basics of querying an Oracle database from Microsoft SQL Server. Oracle data can be queried from SQL Server via a linked server defined in Microsoft SQL Server … Continue reading →","og_url":"https:\/\/dbafox.com\/?p=509","og_site_name":"dbafox","article_published_time":"2015-10-06T07:30:45+00:00","article_modified_time":"2018-09-18T05:59:11+00:00","og_image":[{"url":"https:\/\/dbafox.com\/wp-content\/uploads\/provider_properties.png","type":"","width":"","height":""}],"author":"Ray Fox","twitter_misc":{"Written by":"Ray Fox","Estimated reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/dbafox.com\/?p=509","url":"https:\/\/dbafox.com\/?p=509","name":"OPENROWSET - Query Oracle from SQL Server using a linked server - dbafox","isPartOf":{"@id":"https:\/\/dbafox.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbafox.com\/?p=509#primaryimage"},"image":{"@id":"https:\/\/dbafox.com\/?p=509#primaryimage"},"thumbnailUrl":"https:\/\/dbafox.com\/wp-content\/uploads\/provider_properties.png","datePublished":"2015-10-06T07:30:45+00:00","dateModified":"2018-09-18T05:59:11+00:00","author":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"breadcrumb":{"@id":"https:\/\/dbafox.com\/?p=509#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbafox.com\/?p=509"]}]},{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/dbafox.com\/?p=509#primaryimage","url":"https:\/\/dbafox.com\/wp-content\/uploads\/provider_properties.png","contentUrl":"https:\/\/dbafox.com\/wp-content\/uploads\/provider_properties.png","width":240,"height":200},{"@type":"BreadcrumbList","@id":"https:\/\/dbafox.com\/?p=509#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbafox.com\/"},{"@type":"ListItem","position":2,"name":"OPENROWSET – Query Oracle from SQL Server using a linked server"}]},{"@type":"WebSite","@id":"https:\/\/dbafox.com\/#website","url":"https:\/\/dbafox.com\/","name":"dbafox","description":"DBA Technical Information","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbafox.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":"Person","@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb","name":"Ray Fox","sameAs":["http:\/\/www.dbafox.com"]}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3ecMb-8d","_links":{"self":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/509","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=509"}],"version-history":[{"count":22,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/509\/revisions"}],"predecessor-version":[{"id":1256,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/509\/revisions\/1256"}],"wp:attachment":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=509"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=509"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=509"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}