Connect SQL Servers without using Linked Servers – OPENROWSET and OPENDATASOURCE

27 May,2014 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Connect SQL Servers without using Linked Servers – OPENROWSET and OPENDATASOURCE


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer