Linked Servers on MS SQL Part 2 - Page 2

SQL

In this first SQL linked server example, two servers and a windows administrator account are required. From inside Enterprise Manager, Security, Linked Servers, we will again right click to create a new Linked Server. For the linked server name, enter the SQL name of your second server, and then click the server type "SQL Server" radio button. This will gray out the other data source information boxes we used previously.

Now click the Security tab and select the "Be made using the login's current security context."

Click OK to save the new linked server. This security context will pass your current login information to the remote server. The new server should now appear in the Linked Server list. Clicking the Tables icon will display the remote servers Master table list.

Direct Reference

To use the new connection, open Query Analyzer using Windows Authentication. Your windows account must have rights to the remote servers SQL database. From inside Query Analyzer, issue a select to verify connectivity.

SELECT * 
FROM infonet.northwind.dbo.orders

The four-part name includes the Link Name, Database Name, Owner and Object. (For more on four part naming, see Part 1)

The Orders table will be returned:

We can tie our remote server to our local server by using standard joins. This example will join the local Northwind Orders Detail table to the remote Products table. (Change Query Analyzer to be in the Northwinds database.)

SELECT loc.OrderID, loc.ProductID, rmt.ProductName
FROM [Order Details] loc INNER JOIN Infonet.Northwind.dbo.Products rmt
	ON loc.ProductID = rmt.ProductID

We will call the query just issued a "Direct Reference." Four-part naming is used to call the remote reference directly. Four-part naming creates very clean code, easy to follow and create. Unfortunately, this type of query will execute on our local server. Meaning all of the rows will be required to travel across the wire from the remote server to the local server for processing. Not a problem for small retrievals, but may have a negative performance impact with large remote objects.

Open Query

For large remote objects, the solution to performance problems is the OPENQUERY function. With OpenQuery, our fist select example would read:

SELECT *
FROM OPENQUERY(InfoNet, 'SELECT * FROM Northwind.dbo.Orders')

InfoNet is the name of the remote server, followed by the command. The same results are returned as our four-part naming example, but now the processing is being done on the remote server.

In the Join example, the four-part naming changes from:

SELECT loc.OrderID, loc.ProductID, rmt.ProductName
FROM [Order Details] loc INNER JOIN Infonet.Northwind.dbo.Products rmt
	ON loc.ProductID = rmt.ProductID

To:

SELECT loc.OrderID, loc.ProductID, rmt.ProductName
FROM [Order Details] loc INNER JOIN 
OPENQUERY(InfoNet, 'SELECT * FROM Northwind.dbo.Products') rmt
	ON loc.ProductID = rmt.ProductID

Drop Server

To remove our linked server execute:

sp_dropserver 'INFONET', 'droplogins'

The 'droplogins' option will remove any and all logins associated with the linked server.

To drop from Enterprise Manager, right click the server name and select Delete. A dialog box will prompt you to remove any logins.

sp_addlinkedserver

To recreate our link from inside Query Analyzer, enter and run:

EXEC sp_addlinkedserver
	@server = 'TEST',
	@srvproduct = 'SQLServer OLEDB Provider',
	@provider = 'SQLOLEDB',
	@datasrc = 'InfoNet'

@server will be the name of the new link. This does not have to be the same as your actual data source. Although Query Analyzer does not force it, UPPER CASE should always be used for the server name. Enterprise Manager does this automatically.

An additional parameter that can be passed is @catalog. The catalog will be the default database for the Linked Server. If we change our example above to the following:

EXEC sp_addlinkedserver
	@server = 'TEST',
	@srvproduct = 'SQLServer OLEDB Provider',
	@provider = 'SQLOLEDB',
	@datasrc = 'InfoNet',
	@catalog = 'Northwind'

Our table view in Enterprise Manager now shows the Northwind tables rather than Master:

The security context of "Be made using the login's current security context" will be automatically assigned.

OpenRowset

For one time and ad hoc uses, the OPENROWSET function can be used rather than creating a linked server. The function receives all the parameters necessary to create the connection on the fly.

SELECT *
FROM OPENROWSET('SQLOLEDB', 'INFONET' ; 'sa' ; 'password',
   'SELECT * FROM northwind.dbo.orders' )  

Its end result can be used just like a linked server.

Conclusion

Linked servers can provide effective and powerful connection options. Functions and tools provide many features and choices. In Part 3 we'll explore additional security options and connectivity to Oracle.

» See All Articles by Columnist Don Schlichting