{"id":869,"date":"2013-10-01T15:19:18","date_gmt":"2013-10-01T12:19:18","guid":{"rendered":"http:\/\/www.fatihacar.com\/blog\/?p=869"},"modified":"2013-10-01T15:22:48","modified_gmt":"2013-10-01T12:22:48","slug":"create-linked-server-for-oracle-in-sql-server-2008-r2","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/create-linked-server-for-oracle-in-sql-server-2008-r2\/","title":{"rendered":"Create Linked Server for Oracle in SQL Server 2008 R2"},"content":{"rendered":"<p>If you want to fetch data from Oracle to SQL Server, you can use Linked Servers features in SQL Server Database. Linked Server is like a database link (Dblink).<\/p>\n<p><strong>Installation steps<\/strong><\/p>\n<blockquote>\n<ul>\n<li>Install Oracle Client on SQL Server database server<\/li>\n<li>Restart SQL Server database service<\/li>\n<li>Configure OraOLEDB.Oracle provider in SQL Server database<\/li>\n<li>Create user for remote connection in Oracle database<\/li>\n<li>Create new linked server in SQL Server database<\/li>\n<li>Add remote login for connect Oracle database in SQL Server<\/li>\n<li>Query Example<\/li>\n<\/ul>\n<\/blockquote>\n<p><strong>Install Oracle Client on SQL Server database server<\/strong><\/p>\n<blockquote><p>You can find Oracle client application on Oracle.com. You can install Oracle client application after download. I will not tell how to install.<\/p><\/blockquote>\n<p><strong>Restart SQL Server database service<\/strong><\/p>\n<blockquote><p>You can restart SQL Server database service on services.msc<\/p><\/blockquote>\n<p><strong>Configure OraOLEDB.Oracle provider in SQL Server database<\/strong><\/p>\n<blockquote><p>After Oracle client install, you have to find Oracle provider in SQL Server database Linked Servers as <strong>OraOLEDB.Oracle<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.fatihacar.com\/blog\/resimler\/14.jpg\" alt=\"SQL Server Database\" width=\"236\" height=\"211\" class=\"aligncenter size-full wp-image-870\" \/><\/p>\n<p>OraOLEDB.Oracle properties configure like below;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.fatihacar.com\/blog\/resimler\/21.jpg\" alt=\"SQL Server Database\" width=\"482\" height=\"171\" class=\"aligncenter size-full wp-image-871\" srcset=\"http:\/\/www.fatihacar.com\/blog\/resimler\/21.jpg 482w, http:\/\/www.fatihacar.com\/blog\/resimler\/21-300x106.jpg 300w\" sizes=\"auto, (max-width: 482px) 100vw, 482px\" \/>\n<\/p><\/blockquote>\n<p><strong>Create user for remote connection in Oracle database<\/strong><\/p>\n<blockquote><p>SQL> create user oraconnuser identified by oraconnuser;<br \/>\nSQL> grant create session to oraconnuser;<br \/>\nSQL> grant select on table1 to oraconnuser;<\/p><\/blockquote>\n<p><strong>Create new linked server in SQL Server database<\/strong><\/p>\n<blockquote><p>exec sp_addlinkedserver @LinkName, @SrvProduct, @Provider, @ConnectionString <\/p>\n<p>exec sp_addlinkedserver &#8216;ORALINK&#8217;, &#8216;Oracle&#8217;, &#8216;OraOLEDB.Oracle&#8217;, &#8216;OraIP:1521\\DBNAME&#8217; <\/p><\/blockquote>\n<p><strong>Add remote login for connect Oracle database in SQL Server<\/strong><\/p>\n<blockquote><p>exec sp_addlinkedsrvlogin @LinkName, @useself, @LocalLogin, @OraUser, @OraPassword<\/p>\n<p>exec sp_addlinkedsrvlogin &#8216;ORALINK&#8217;, &#8216;FALSE&#8217;, NULL, &#8216;oraconnuser&#8217;, &#8216;oraconnuser&#8217;<\/p><\/blockquote>\n<p><strong>Query Example<\/strong><\/p>\n<blockquote><p>select * from openquery(ORALINK,&#8217;select * from table1&#8242;)<br \/>\nwhere table1column = &#8216;example&#8217;<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>If you want to fetch data from Oracle to SQL Server, you can use Linked Servers features in SQL Server Database. Linked Server is like a database link (Dblink). Installation steps Install Oracle Client on SQL Server database server Restart SQL Server database service Configure OraOLEDB.Oracle provider in SQL Server database Create user for remote&#8230;<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_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":[9,11],"tags":[14,96,90,32,46,47],"class_list":["post-869","post","type-post","status-publish","format-standard","hentry","category-administration","category-administration-mssql-server","tag-database-administration","tag-mssql-server","tag-oracle","tag-oracle-administration","tag-sql-server-administration","tag-sql-server-to-oracle-dblink"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-e1","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/869","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=869"}],"version-history":[{"count":2,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/869\/revisions"}],"predecessor-version":[{"id":873,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/869\/revisions\/873"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=869"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=869"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=869"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}