PRB: Installing DA SDK Causes SQL Distributed Queries to Fail

• Microsoft Data Access SDK, versions 2.0, 2.1
• Microsoft SQL Server version 7.0
• Microsoft Data Access Components version 2.1 SP2

SYMPTOMS

After installing the Microsoft Data Access 2.0 SDK, the following errors may occur when trying to perform a SQL Server 7.0 distributed query:

Server: Msg 7302, Level 16, State 1 Could not create an instance of OLE DB provider '<the_ole_db_provider>'

-or-

Server: Msg 7373, Level 16, State 2
Could not set the initialization properties for the OLE DB Provider '<the_ole_db_provider>'.

-or-

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 7 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

CAUSE

Microsoft SQL Server installs a proxy/stub DLL for the OLE DB interfaces called Msdaps.dll. This is used to marshal OLE DB interfaces between processes/apartments. If the OLE DB Provider is invoked out of process or is marked as apartment model, this proxy-stub DLL gets used. By default, if the OLE DB provider is a known Microsoft provider, SQL Server 7.0 will invoke the OLE DB provider in-process and the problem mentioned in this article will not occur. If the provider is third party, it will be invoked out-of-process.

When installing the Microsoft Data Access SDK, a different proxy/stub DLL gets registered for use of ITestSpy. The file is called Oledbproxy.dll. This replaces the Msdaps.dll proxy-stub DLL and causes OLE DB providers to fail when invoked out of process or marked as apartment model.

RESOLUTION

To get SQL Server 7.0 distributed queries to work when the OLE DB Provider is invoked out of process or is marked as apartment model, re-register the Msdaps.dll located in the "\Program Files\Common Files\System\ole db" folder using Regsvr32.exe.

MORE INFORMATION

Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked-server name. For example,

sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'

SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')

REFERENCES

For a discussion on OLEDB provider configuration for Distributed Query, see the following topic in SQL 7.0 Books Online: "Configuring OLE DB Providers for Distributed Queries."

For more details on setting up and using Distributed Queries, see sp_addlinkedserver, OpenQuery, OpenRowset, and related topics in SQL 7.0 Books Online.

Additional query words:

Keywords : kbDatabase kbOLEDB kbGrpMDAC kbDSupport kbMDAC210SP2 kbGrpODBC
Version : WINDOWS:2.0,2.1,2.1 SP2; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbprb