Connect SQL Servers without using Linked Servers – OPENROWSET and OPENDATASOURCE

Question:  Assuming a SQL Server Policy doesn’t allow Linked Servers, what are alternatives to using Linked Servers? The Senior DBA insists a permanent connection is a risk.

Answers:  Use the OPENROWSET and OPENDATASOURCE functions .  Before applying , think about the advantages \ disadvantages. There are some differences between OPENROWSET and OPENDATASOURCE.

OPENDATASOURCE function returns a datasource reference. It’s used as part of a four part naming convention.”This is how you can connect to a server”

OPENROWSET executes a query on a OLEDB provider , you supply the connection details and the query.”This  is how you connect to a server and here is the query I want to execute”

Syntax for OPENROWSET

SELECT  Location,Longitude, Latitude FROM     OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=Server1;UID=Tom;PWD=MyPW',  Customers.dbo.Locations)

Syntax for OPENDATASOURCE

SELECT Location,Longitude,Latitude FROM OPENDATASOURCE('SQLNCLI', 'Data Source=Server1;Integrated Security=SSPI') .Customers.dbo.Locations  

Notes

1) OPENROWSET requires the password to be supplied in plain text. There are security and code management considerations

2) OPENDATASOURCE requires ad-hoc queries to be enabled

3) OPENROWSET supports bulk operations

Read More

Linked Server migration from sql server 2000 to 2005 - SQL Server ...

SQL Server - How to setup ODBC for IBM AS/400 - SQL Server DBA