SideBar    Sidebar: An ORDB Hybrid

Shortcuts to data from multiple sources

Microsoft's catch phrase in the early days of Windows was "Information at your fingertips." That phrase comes to mind when I think about SQL Server 7.0's support for distributed heterogeneous queries.

What Is a Distributed Heterogeneous Query?
A distributed heterogeneous query lets you access data from multiple sources in one step. You can dissect distributed heterogeneous query into distributed, referring to data disbursed on multiple machines, and heterogeneous, referring to diverse data sources. Say you have data stored in databases on SQL Server, Microsoft Access, and Oracle. With a distributed query, you can create two linked servers in your SQL Server system that represent the Oracle and Access data sources. Now you can reference tables within those two distributed heterogeneous data sources as if they were local tables within SQL Server. You can issue a single query to SQL Server, which can then retrieve the Access and Oracle data.

SQL Server packages all the data into a single result set and returns the answer to the end user at a client PC. This approach is similar to using attached tables with Access.

Data Sources
SQL Server supports distributed queries through OLE DB, Microsoft's new API standard for Universal Data Access (UDA). You can understand OLE DB by comparing it with Open Database Connectivity (ODBC), which has become the primary universal data-access mechanism for relational data sources.

ODBC has simplified access to heterogeneous databases—particularly when you're accessing a relational database. However, much useful data, including audio, video, and full text, isn't in relational databases—or any database management system (DBMS). OLE DB lets you access this non-relational data.

SQL Server speaks OLE DB natively. OLE DB is the primary means for accessing SQL Server data from an application, and components of SQL Server use OLE DB to speak to each other. Microsoft designed distributed queries to work with any OLE DB provider. However, the initial release has been tested only with selected OLE DB providers including SQL Server, ODBC, Access (i.e., the Jet database engine), and Oracle.

Why would Microsoft release a native OLE DB API for Oracle before fully supporting Microsoft products? Microsoft knows that Oracle is numero uno in the database world. Microsoft must ensure that a stable and fast OLE DB interface exists for Oracle data sources; the development community is more likely, then, to accept the new API standard.

Also, OLE DB provides an alternative to the use of object-relational databases (ORDBs): universal servers. The sidebar "An ORDB Hybrid," page 29, explains Microsoft's ORDB management system (ORDBMS) strategy, which includes universal server functionality.

The examples in this article focus on distributed queries running against Access. (This focus simplifies the article for readers who don't have multiple SQL Server 7.0 boxes.) SQL Server-to-Access queries are useful, but I stress that SQL Server-to-SQL Server queries constitute a powerful way to use SQL Server 7.0's new distributed-queries technology. Microsoft will keep pre-SQL Server 7.0 remote server capabilities for backward compatibility, but you should use the new query features for data spread across multiple SQL Server systems.

EXAMPLE 1
Let's get our hands dirty with a simple example that walks you through creating a linked server connected to the sample Northwinds Access database. Next, you'll join a table from the Northwinds Access server to a local table in SQL Server. (By the way, Northwinds ships with SQL Server 7.0 as a sample database.)

The following command creates a distributed server entry called Northwinds. The command uses the Microsoft.Jet.OLEDB.3.51 OLE DB provider linking to an Access database located at c:\brian\writing\sqlservermag\nov98\nwind.mdb:

sp_addlinkedserver
	@server = "Northwinds",
	@srvproduct = "Access 97",
	@Provider = 
		"Microsoft.Jet.OLEDB.3.51",
		@datasrc =
		"c:\brian\writing\sqlservermag\nov98\nwind.mdb"

Example 1 uses data from this Access .mdb file. However, linking to other distributed data stores is just as simple. The sp_addlinkedserver reference in Microsoft's Books Online (BOL) shows the complete syntax for identifying a linked server to Access, SQL Server 7.0, Oracle, and the generic OLE DB for ODBC provider. The OLE DB for ODBC provider lets you create a linked server to any ODBC-compliant data store, including SQL Server 4.x and SQL Server 6.x.

When you set up a linked server, you're registering the connection information and data source information with SQL Server so that the server knows how to find the data when you need it. In ODBC terms, think of this process as creating a Data Source Name (DSN) for the client to use.

Referencing remote tables in a linked server is simple; it's similar to referencing a local SQL Server table. Each local table in SQL Server has a four-part, fully qualified name consisting of server, database, object owner, and object. Distributed queries use a similar four-part naming scheme, but the terms are slightly different. You specify distributed query four-part names in the following form: server_name.catalog.schema.object_name. Server_name is the logical name you specified in the sp_addlinkedserver call. Catalog and schema are ANSI terms that mean database and object owner in SQL Server terms, and object_name refers to a table or whatever object type works for the OLE DB provider you're using. When using the Access OLE DB provider, you don't need to specify all the pieces of the four-part naming scheme. If you specify all four parts, the system will send you the following error: Server: Msg 7312, Level 16, State 1: Illegal use of schema and/or catalog for OLE DB provider 'Microsoft.Jet.OLEDB.3.51'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

Providers, which expose differing levels of OLE DB functionality, have different rules governing the way you access data and name data stores. You'll probably discover them through trial and error. Microsoft didn't adequately explain the differences in Beta 3's BOL.

   Prev. page   [1] 2 3     next page



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Good article .... I have one question ... Does the openrowset execute the stored procedures addlinkedserver, addlinkedsrvlogin, etc ... I need to insert into sql 7 tables from access and the access mdb files will change. I have written a stored procedure that reads a list of tables and can change the linked server info thru the sp_xxx only when the mdb file changes. If I use openrowset and the stored procs are executing behind the scenes then they will run every time which isn't necessary. Any thoughts would be appreciated. Thanks Joe

Joe Finnerty- May 17, 2001

I don't believe that the following line from the article is correct 'Until NT 5.0 ships, this approach is the only option available if your users are connecting through integrated security. ' It should rather read 'Until NT 5.0 ships, this approach is the only option available if your users are connecting through SQLServer authentication'. Otherwise the writer is contradicting themself in the paragraph below. Using sp_addlinkedsrvlogin with @useself set to TRUE is only valid for SQLServer authentication unless the O/S is Win2000. Further sp_addsrylogin should read sp_addlinkedsrvlogin. The writer should not be addressing 7.0 security as Integrated security. This is a 6.5 feature and they should be discussing in terms of the authentication model. Also, the writer discusses three security options but essentially the first and the third paragraphs are discussing the same ie impersonation.

Paul Williams- November 23, 2001

Just one question: how can I access e.g. Excel or txt files on remote machines?

Anonymous User- October 29, 2004

No cacho ni una guea esta caga de arti culo

Anonymous User- November 08, 2004

Article Rating 1 out of 5