Functions to access cross server data

Functions to access cross server data

OPENQUERY / OPENROWSET / OPENDATASOURCE

All the above mentioned are SQL Server inbuilt functions where are introduced from SQL Server 2005.

These functions use the ODBC drivers to work and talk to destination server for data loading/reading/writing/.

Every function serves the same purpose but are different with one another in the way the work.

All the functions need Inputs like PROVIDER Name, Database Name , Server Name, User ID, Password etc.

OPENQUERY:

OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

OPENQUERY needs two inputs.

Syntax:  OPENQUERY (linked server, ’query’)

Linked server needs to get created first in order to make this function work, where as in the

other two functions linked server creation is not necessary.

Example:

select * from openquery(LINKED_SERVER_NAME,'select @@servername')

OPENROWSET:

OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.

OPENROWSET needs 3 inputs.

Syntax:  
OPENROWSET(‘PROVIDER NAME’,’Data source;Userid;Password;Provider_string’,’QUERY’)

Example:1

select * from openrowset('SQLNCLI','Server=SERVERNAME;TRUSTED_CONNECTION=YES;','Select @@servername')

 Example:2

select * from openrowset('SQLNCLI','SERVER=SERVERNAME;USER ID=sarmadh;PASSWORD=sarmadh;TRUSTED_CONNECTION=YES','Select * from test_123.dbo.sqldb_info')

OPENDATASOURCE:

Provides ad hoc connection information as part of a four-part object name without using a linked server name.

OPENDATASOURCE Needs 2 inputs.

Syntax: OPENDATASOURCE(‘PROVIDERNAME’,’Connection_string’)

Example:

select * from OPENDATASOURCE('SQLNCLI','DATA SOURCE=SERVERNAME;USER ID=sarmadh;PASSWORD=sarmadh').test_123.dbo.sqldb_info

Generally every object name is 4 part name.

Servername.catalog_name.schema_name.object_name

In the above syntax: “Servername” is replaced by “OPENDATASOURCE” function.

Hope the above information helps

Thanks