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