Microsoft KB Archive/200921

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

Article ID: 200921

Article Last Modified on 10/24/2003

-

APPLIES TO


 * Microsoft Visual C++ 6.0 Enterprise Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q200921



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:

  Change the value of CLSID key to contain the PROGID. This method makes the provider OLEDB 2.1-compliant. Open the .rgs file in the Visual C++ ATL project and go to the line where the value is supplied for the CLSID key. Change the value to the PROGID. For example:

NoRemove CLSID { ForceRemove {78B9A5CE-9EE4-11D2-A896-00105A9F6419} = s 'MyProv.MyProv'   Use sp_addlinkedserver to set up the linked server, as given below:

EXEC sp_addlinkedserver

'MyProv', '',  'Myprov.MyProv.1', NULL, NULL, NULL

go

select * from openquery (MyProv, 'c:\*.*')   Use OpenRowset in the query rather than using a linked server. Here is an example:

SELECT * from OpenRowset('MyProv.1', '', 'c:\*.*') 



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> Create an ATL project using the ATL COM AppWizard.</li> 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.</li> Build the ATL OLE DB Provider.</li> Register the provider on a machine with SQL Server 7.0 installed.</li> Set up a linked server MYPROV1 with SQL Enterprise Manager (Server->Security folder) that uses the ATL Provider built above.</li>  Run the following command:

<pre class="fixed_text">  select * from OpenQuery (MYPROV1, 'c:\*.*') </li></ol>

<div class="references_section">