Creating, Using, and Debugging OLE DB Providers for SQL Server 7.0 Distributed Queries Syed Yousuf T

1
Creating, Using, and Debugging OLE DB Providers
for SQL Server 7.0 Distributed QueriesSyed
YousufTechnical Support LeadData Access Support
TeamMicrosoft Corporation
2
Overview

• Microsoft SQL Server distributed queries
• Provider and data source specific issues
• Developing an OLE DB provider to work with
  distributed queries
• Troubleshooting OLE DB provider with distributed
  queries
• References

3
SQL Server Distributed Queries

• Allows you to set up linked data sources or ad
  hoc queries to access other OLE DB data sources.
• Supports SELECT with join and data modifications
  (need provider support).
• Allows you to work with providers at various
  levels.
• Same mechanism for RDBMS access (SQL Server,
  Oracle) and heterogeneous non-relational data.

4
Setting Up a Distributed Query

• Define a Linked Server in SQL Server using
  sp_addlinkedserver or Enterprise Manager
• Use OpenQuery(ltlinked-servergt, ltquerygt)
• or a Transact-SQL statement with four-part name
• ltlinked-servergt.ltcataloggt.ltschemagt.ltobjectgt
• Use an ad hoc query with OpenRowset() and specify
  the data source information

5
Index Server Pass-Through Query

• sp_addlinkedserver 'Monarch', '', 'MSIDXS',
  'Web', NULL, NULL
• SELECT FROM OpenQuery (Monarch,
• ' Select Directory, FileName, size, Create, Write
• FROM SCOPE() WHERE CONTAINS(' ' "Index Server"
  NEAR()"SQL Server ) gt 0
• AND FileName LIKE ''.htm'' ')

6
Ad Hoc Query to Access (Jet)

• SELECT OrderID,CustomerID,EmployeeID,Freight,Order
  Date,RequiredDate
• FROM OpenRowset ('Microsoft.Jet.OLEDB.4.0',
  'c\DevStudio\VB\nwind.mdb''admin''',
• 'SELECT FROM orders WHERE orderID BETWEEN
  10252 and 10260
• ORDER BY orderId')

7
OLE DB Provider Types

• SQL Query providers
• Support SQL querying in supported dialect
• Index providers
• Support index scan and index seek functionality
• Simple table providers
• Non-SQL command providers

8
Data Source Specific Issues with Distributed
Queries

• Index Server
• Jet (Access)
• Oracle
• FoxPro
• DTS Package

SQL Server DB2 ADSI Visual C ATL Provider
9
Specific Issues Index Server

• MSIDXS provider for file system search
• Non-relational, read-only
• Index Server must be installed on SQL Server 7.0
  computer
• Check the documentation for syntax
• Q220598 - PRB SQL Distributed Query with MSIDXS
  Provider Needs Additional Quoteshttp//support.mi
  crosoft.com/support/kb/articles/Q220/5/98.ASP
• Q201785 - HOWTO Import FileSystem Data Using DTS
  and Index Serverhttp//support.microsoft.com/supp
  ort/kb/articles/Q201/7/85.ASP
• Q198493 - INFO Using NEAR in SQL Full-Text and
  MSIDXS Distributed Querieshttp//support.microsof
  t.com/support/kb/articles/Q198/4/93.ASP

10
Specific Issues FoxPro

• Use MSDASQL and Visual FoxPro ODBC Driver
• UPDATE OpenRowset('MSDASQL', 'DriverMicrosoft
  Visual FoxPro Driver SourceDBe\VFP98\data\
  Testdata.dbc SourceTypeDBC', 'SELECT
  FROM customer WHERE region"WA"') set region
  "Seattle"
• Q207595 - HOWTO Do SQL Server 7.0 Distributed
  Queries With FoxPro .dbf Files
  http//support.microsoft.com/support/kb/articles/Q
  207/5/95.ASP
• Q199131 - HOWTO Add a FoxPro Database to SQL
  Server as a Linked Server http//support.micros
  oft.com/support/kb/articles/Q199/1/31.ASP

11
Specific Issues Oracle

• Install Oracle client and networking components
  on SQL Server 7.0 computer
• Q220915 - PRB SQL Distributed Query with Oracle
  Needs Oracle Client and Networking Components
  
• http//support.microsoft.com/support/kb/article
  s/Q220/9/15.ASP
• Optimize for type conversion issues
• Q197456 - INF Optimizing Distributed Query
  with Numeric Predicates http//support.microsof
  t.com/support/kb/articles/Q197/4/56.ASP
• Use OPENQUERY for data source specific syntax
• Case sensitivity issue
• SELECT FROM OracleServer..DEMO.PRODUCT
• Q240340 - PRB SQL Distributed Query with
  Oracle Causes "Could not open table"
  Errorhttp//support.microsoft.com/support/kb/arti
  cles/Q240/3/40.ASP

12
Using Distributed Queries

• Anywhere a SQL statement can be used
• Script, ADO, OLE DB, ODBC application
• OpenQuery() and OpenRowset() functions can be
  used as table reference in SQL statements
• Q198485 - HOWTO SQL Server 7 Distributed Query
  Using ATL OLEDB Consumerhttp//support.microso
  ft.com/support/kb/articles/Q198/4/85.ASP

13
Example Creating an OLEDB Provider

• 1. Using VC 6.0, create an ATL project using
  the ATL CO AppWizard.
• 2. Create an ATL OLE DB provider On the Insert
  menu, click New ATL Object, select Data Access
  for the category, and then Provider for the
  object. Choose a name.
• 3. Build the ATL OLE DB Provider (registers on
  local computer).
• 4. Register the provider on a computer that has
  SQL Server 7.0 installed.
• 5. Run a query against the provider by using the
  command
• SELECT FROM OpenRowset('YourProviderName',
  '', 'c\.')
• 6. Apply the fixes as described in Knowledge
  Base article
• Q198520 - BUG ATL OLE DB Provider Fails
  When Called from SQL 7.0 Query
  http//support.microsoft.com/support/kb/articles/Q
  198/5/20.ASP

14
Using VC ATL COM AppWizard
15
Creating an ATL OLE DB Provider
16
Setting Up a Linked Server

• EXEC sp_addlinkedserver
• 'ATL_Prov_Link', '',
• 'ATL_prov.ATL_Prov.1', NULL, NULL, NULL
• SELECT FROM OPENQUERY (ATL_Prov_Link,
  'c\temp\.')

17
Executing an Ad Hoc Query
18
Troubleshooting OLE DB Providers for Distributed
Query

• xp_enum_oledb_providers stored procedure
• DBCC TRACEON (7300)
• Reports additional OLE DB errors
• SQL Profiler OLE DB Tracing
• Debugging the provider with VC
• Q216575 - INF xp_enum_oledb_providers Enumerates
  the OLE DB Providershttp//support.microsoft.com/
  support/kb/articles/Q216/5/75.ASP
• Q222123 - HOWTO Debug OLE DB Providers That Are
  Called From SQL Server 7.0http//support.microsof
  t.com/support/kb/articles/Q222/1/23.ASP

19
Listing OLE DB Providers

• ProviderName default value of CLSID key.
• Parsename CLSID (GUID).
• Description friendly name of the provider.

20
SQL Profiler OLE DB Tracing
21
SQL Profiler OLE DB Tracing
22
Debugging a Provider with VC

• Set Allow in-process option in SQL Server
• Use SQL Enterprise Manageror
• Use Registry Editor to add the provider ProgID to
  the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLS
  erver\Providers registry key and then add
  AllowInProcess DWORD under that key with a value
  of 1.
• Attach to SQL Server process
• Add the Provider DLL to the list of DLLs

23
Running the Provider In-Process
24
Attaching SQL Server Process

• On the Build menu, click Start Debug, and then
  click Attach to Process.
• The Show System Processes will show sqlservr.

25
Loading OLE DB Provider DLL

• On the Project menu, click Settings, and then
  click the Debug tab.
• Add your Provider DLL to the list of additional
  DLLs.
• Put a breakpoint in provider source code.
• Invoke the provider through the OpenQuery() in
  Query Analyzer.

26
Supplemental Reading

• SQL Server 7.0 Books Online
• OLE DB white paper
• http//www.microsoft.com/sql/interopmigrate/
  oledb.htm
• Knowledge Base articles at http//support.microsof
  t.com/search/default.asp
• Microsoft SQL Server Web Site
• http//www.microsoft.com/sql/
• Distributed Query Troubleshooter
  http//support.microsoft.com/support/tshoot/sql7di
  stquery.asp
• MSDN Visual C documentation

27
(No Transcript)