Linked Server setup with Oracle

Linked Server
Setup with Oracle in SQL Server:

Suppose you want to access data from

oracle or other database products using T-SQL via SQL server database Engine

then you need to configure Linked Server.

You can learn about Linked Server from

MSDN .

Here I show an example to setup linked

server with oracle.

Oracle Configuration on Client machine

First install oracle client on machine

where you want to configure linked server.

Go to [Oracle

Client Installation Directory]\product\11.2.0\]dbhome_1\NETWORK\ADMIN

Open tnsnames.ora file.(It is Network

configuration file for oracle)

Add TNS setting in this file as

Example here

PROD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = 192.168.10.10)

(PORT = 1521)

)

(CONNECT_DATA =

(SERVICE_NAME = PROD)

)

See more details of tnsnames.ora file.

Test oracle connection:

Open cmd

Enter SQLPLUS

Enter USERNAME: username@PROD

Enter PASSWORD: password

Type any select query to confirm

connection.

Now come on SQL Server.

In object explorer, Expand Server

Object tree Node and see linked server

Right click on linked server and

select New Linked Server and Enter

required information

Select Security option and enter

credentials

Click Ok.

If all things are right linked server

configured now.

Now Test your linked server.

Run query as

SELECT

a.*

FROM

OPENQUERY(linkedservername,'SELECT *from oracledatabasename.table_or_view') a;

Basic

Syntax of OPENQUERY

OPENQUERY

( linked_server ,'query' )

Using

OpenQuery you can INSERT,DELETE , UPDATE,DELETE.

Example
Select Query:

SELECT

a.*

FROM

OPENQUERY(PRODNEW,'SELECT *from Apps.customer_details') a;

Example INSERT Query:

INSERT

OPENQUERY (PRODNEW, ‘SELECT ID,name FROM Apps.customer_details’)

VALUES

('C121’,’Uma Shankar');

Example UPDATE Query:

UPDATE

OPENQUERY (PRODNEW, 'SELECT name FROM Apps.customer_details

WHERE id = ''C121''')

SET

name = 'Uma Shankar

Patel';

Example DELETE Query:

DELETE

OPENQUERY (PRODNEW, 'SELECT ID FROM Apps.customer_details

WHERE ID = ''C121''');

This query

fetch data from oracle via SQL server database engine.

It is the

little about linked server.I have learned this when my client require to

develop a web site that fetch data from sql server but customer data of client

comes from oracle.

Insert data from Oracle to SQL Server over linked server

INSERT INTO sqldatabasename.dbo.tablename

(
col1,
col2,
col3
)
SELECT a.*
FROM OPENQUERY(likedservaername,'SELECT
col1,
col2,
col3
from oracletablename') a;

Insert data from SQL server to Oracle over linked server
INSERT INTO OPENQUERY(linkedservername,'select
col1,
col2,
col3
from
oracletablename')

   SELECT
   col1,
   col2,
   col3
FROM sqldatabase.dbo.tablename;

Delete data from Oracle over linked server
DELETE OPENQUERY (linkedserver, 'Select from oracetablename');

In the same way you can create linked server with oracle,mysql and other rdbms and can use above sample query as required.
Update oracle table from SQL Server
UPDATE u
SET u.oraclefieldname = ‘value’
from
openquery(likedservername,'select from oracletable') as u
where u.hdr_id = @rid

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

Linked Server setup with Oracle