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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |