March 2, 2014

Linked Server CRUD Operation

Using OPENQUERY


OPENQUERY function can be used to execute the pass-through queries on the linked server.

For example: Let postgres be the linked server name.

Select Statement

select * from openquery('postgres', 'select * from dbo.table')

Update Statement

update openquery(postgres, 'select column_name from table where id = 1')
set column_name = 'abc'

Insert Statement

insert openquery(postgres, 'select column_name from table')
values ('abc')

Delete Statement

delect openquery(postgres, ''select column_name from table where column_name = ''abc''')

Without Using OPENQUERY


There is also another method to run the pass-through queries on the linked server without using OPENQUERY.

LINKEDSERVERNAME.DATABASENAME.SCHEMANAME.TABLENAME

Instead of above select statement, it can be written as:

select * from postgres.databasename.dbo.table

In order to execute the query without using OPENQUERY, you need to query through Remote Procedures Calls (RPC ). This RPC can be found while creating the linked server on the server option.

RPC is to allow remote procedures calls from linked server.
RPC Out is to allow remote procedures calls to linked server.

RPC and RPC Out both should be true.

Even when RPC and RPC Out is false. You can execute the query without using OPENQUERY.

Example:

select * from postgres...dbo.table

It will run successfully.

No comments: