PRB: OpenQuery with ATL Provider Fails When Set Up with SQL Enterprise Manager

ID: Q200921


The information in this article applies to:


SYMPTOMS

SQL Server version 7.0 provides a mechanism for performing distributed queries against an OLE DB provider, by using the new OpenQuery or OpenRowset function. For example, the following SQL statement performs a query against a linked server called MyLinkedServer using the command "Select * from table1":


   Select * from OpenQuery(MyLinkedServer, 'Select * from table1') 
Using the SQL Enterprise Manager to create a linked server that uses an ATL wizard-generated OLE DB Provider causes an error to occur using the following command:

   select * from openquery (MYPROV1, 'c:\*.*') 
The following is the error message:
Server: Msg 7403, Level 16, State 1, Line 1 Could not locate registry entry for OLE DB provider 'MyProv OLE DB Provider'.


CAUSE

The SQL Enterprise Manager uses the OLE DB Root Enumerator when retrieving information about an OLEDB provider. A wizard-generated ATL OLE DB provider has a provider-friendly name as the default value for its CLSID instead of its PROGID. While this complies with OLE DB 2.0, OLE DB 2.1 says that the "Default" value under the CLSID should be the PROGID.

The same problem may occur when using a third-party provider where the default value for its CLSID is the provider friendly name instead of its PROGID.


RESOLUTION

There are three ways to work around this problem:


STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.


MORE INFORMATION

Steps to Reproduce Behavior

  1. Create an ATL project using the ATL COM AppWizard.


  2. Create an ATL OLE DB provider by clicking New ATL Object from the Insert menu, and then selecting Data Access for the category and Provider for the object.


  3. Build the ATL OLE DB Provider.


  4. Register the provider on a machine with SQL Server 7.0 installed.


  5. Set up a linked server MYPROV1 with SQL Enterprise Manager (Server->Security folder) that uses the ATL Provider built above.


  6. Run the following command:
    
       select * from OpenQuery (MYPROV1, 'c:\*.*') 



REFERENCES

For additional information about additional fixes needed to use an ATL OLE DB provider with SQL 7.0 distributed queries, please see the following article in the Microsoft Knowledge Base:

Q198520 ATL OLE DB Provider Fails When Called from SQL 7.0 Query

Additional query words: kbDSupport


Keywords          : kbDatabase kbOLEDB kbSQLServ kbVC600 
Version           : WINDOWS:6.0; winnt:7.0
Platform          : WINDOWS winnt 
Issue type        : kbprb 

Last Reviewed: March 20, 1999