Chapter 11: Data Access Tips and Techniques | Ado Examples and Best Practices

Team-Fly

OVER THE LAST FEW YEARS, I'VE accumulated a lot of suggestions, tips, and reviews, as well as complaints about my treatment of Cleveland. Some of these came to me while writing examples or in the classroom as I taught my classes, while others were filtered from the mountains of e-mail I get on a daily basis. While many of these ideas have already made their way into earlier chapters, the ones described here have not. Instead of leaving out these orphans, I decided to bundle them together into their own chapter—so here they are.

Referencing Data on Other Servers

If you have a query that needs to include data joined from another server, you can easily do so by adding a reference to the server in your SELECT statement. As long as the server is preregistered, SQL Server (7.0) takes care of the details. Getting your remote server registered is easy. Start SQL Server Enterprise Manager and click on Security/Remote Server. You'll get a dialog that looks like Figure 11-1.

click to expand
Figure 11-1: Using SQL Enterprise Manager to add a new remote server

Using the dialog box, point to the remote server. Yes, this can be accomplished through system stored procedures too, but this is far easier. Next, you have to reconfigure the remote server so it knows to accept remote procedure calls from other servers. To do this, execute the sp_serveroption system stored procedure, as shown next. You only have to do this once, but you'll need SA permissions to do so.

 EXEC sp_serveroption 'BETAV2', 'data access', 'true'

Okay, we're ready to try a multisystem join. For example, to join a table on BetaV2 (a server marked as remote on the server doing the join) with tables located on the server doing the join, code the following SQL Statement:

 rs.Source = "SELECT Author, Year_Born " _     & " FROM BETAV2.Biblio.dbo.Authors A, Title_Author TA, Titles T" _     & " Where TA.au_id = A.au_id and ta.isbn = t.isbn and t.Title like 'Hi%' " rs.Open , cn, Options:=adCmdText

Note how the name of the table is predicated with the server name, database name, and owner name, as in server.database.owner.table, this is documented in SQL Server Books Online. Of course, you can simply reference other databases on the same server by using the database.owner.table notation—but you knew that.

Team-Fly