One step remains before you can run a distributed query against the new Northwinds linked server. By default, distributed queries use current user credentials when connecting to the remote server. You logged in to your server as systems administrator (sa); so SQL Server connects to the Access database as a user called sa. However, the Access database doesn't include an sa user, so the connection fails. The following command solves this problem by telling SQL Server that all local users logged in as sa should connect to the Northwinds server as the remote user called admin, which is the only default user in an Access database:
exec sp_addlinkedsrvlogin
@rmtsrvname = "Northwinds",
@useself = "false",
@locallogin = "sa",
@rmtuser = "Admin",
@rmtpassword = NULL
You're ready to run your first distributed query. Execute the following query, and you'll receive data from the Northwinds database:
SELECT * FROM
Northwinds...customers
Congratulations, you've just written your first distributed heterogeneous query!
Access doesn't support the four-part name, but you must include the periods as placeholders. Otherwise, SQL Server won't realize you're trying to run a distributed query and will think you're trying to access a local table called customers owned by someone named Northwinds.
You can join tables from Access and SQL Server as if the Access table were a local SQL Server object. Suppose you've created a mobile sales-force automation system and need a query to insert new orders into the master order table when salespeople dock their laptops. The following query uses a left outer join to find remote orders not yet posted to the MasterOrders table. This query inserts those rows into the central MasterOrders table. You'll find little difference between referencing a local table and referencing a remote table:
INSERT into MasterOrders
SELECT
RemoteOrders.*
FROM
Northwinds...orders RemoteOrders left join
MasterOrders on RemoteOrders.OrderId =
MasterOrders.OrderId
WHERE
MasterOrders.OrderID = null
Distributed Queries Alternatives
Let's look at OpenRowset and OpenQuery, two Transact-SQL (T-SQL—for an introduction to T-SQL, see Michael D. Reilly, "Managing Data with Query Windows," page 73) functions that you use to execute distributed queries. OpenRowset and OpenQuery are similar; they both support the passthrough of queries to remote data sources. The primary difference between these operations is that OpenRowset requires definition of connection information on the fly, and OpenQuery works with pre-existing linked-server connections.
Distributed Queries Alternatives
The first OpenRowset example, Example 2, issues a simple passthrough using the connection information provided at run time:
SELECT * FROM
OpenRowset("Microsoft.Jet.OLEDB.3.51",
"c:\brian\writing\sqlservermag\nov98\nwind.mdb"; "admin";, "select * from orders")
Figure 1 shows the syntax for an OpenRowset query.
EXAMPLE 3
The second OpenRowset example, Example 3, re-creates the query in Example 1, which you issued using a linked server:
SELECT
RemoteOrders.*
FROM
OpenRowset("Microsoft.Jet.OLEDB.3.51",
"c:\brian\writing\sqlservermag\nov98\nwind.mdb"; "admin"; ,
"select * from orders") RemoteOrders
left join MasterOrders on
RemoteOrders.OrderId = MasterOrders.OrderId
WHERE
MasterOrders.OrderID = null
Prev. page
1
[2]
3
next page 
