PRB: OLE Automation Error Occurs When SQL Server Runs a Stored Procedure (814578)


The information in this article applies to:

  • Microsoft SQL Server 7.0

SYMPTOMS

When you run a stored procedure in Microsoft SQL Server 7.0, the execution might fail, and you receive an error message similar to the following:

-2147467259
OLE Automation Error(s) Information
HRRESULT: 0x
Source : Provider
Description : Unspecified error
procerror

-or-

Server : Msg 7390, Level 16, State 1, Procedure <proc_name>, Line <line number>
The requested operation could not be performed because the OLE DB provider '<Provider Name>' does not support the required transaction interface.
-2147467259
OLE Automation Error(s) Information
HRRESULT: 0x
Source : Provider
Description : Unspecified error
procerror

CAUSE

This behavior can occur when you run a stored procedure that internally calls the sp_oacreate system stored procedure to create an instance of a Component Object Model (COM) object, and the COM component was developed with Microsoft Visual Basic.

RESOLUTION

To resolve the problem, use any one of the following methods:
  • Run the COM object outside the SQL Server memory address space.

    By default, a DLL-based COM object is loaded in the SQL Server process memory address space; therefore it has the potential to overwrite the memory space of SQL Server. To avoid any error caused by the COM object, run the COM object outside the SQL Server memory address space.

    For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

    198891 INF: Enabling DLL-Based COM Object Execution Outside SQL Server

  • Compile the Visual Basic DLL files with the Retained In Memory and the Unattended Execution options selected.

    Compile all the DLL files of the COM component with the Retained In Memory, and the Unattended Execution options selected. To do so, follow these steps:
    1. Open the project that corresponds to the DLL file in Microsoft Visual Basic.
    2. On the Project menu, click to select <Project-Name> Properties.
    3. On the General tab of the <Project-Name> Properties dialog box, click to select the Unattended Execution and the Retained In Memory check boxes.
    4. Click OK.
    5. Compile the project to make a new DLL.

    Microsoft provides a utility that is named VBCHKW2K that you can use to detect all the Visual Basic ActiveX DLL files that are not compiled with the Unattended Execution and the Retained In Memory options enabled.

    For additional information about the VBCHKW2K utility, click the following article number to view the article in the Microsoft Knowledge Base:

    286036 SAMPLE: Using VBCHKW2K to Isolate DLLs Compiled Without Retain In Memory and Unattended Execution


Note: If the problem still persists, restart SQL Server. That might solve the problem.

REFERENCES

For additional information about a memory leak problem with a Visual Basic DLL, click the following article number to view the article in the Microsoft Knowledge Base:

264957 PRB: Visual Basic DLL Has Memory Leaks and Crashes in COM+ If 'Retain In Memory' Is Not Set

Modification Type:MajorLast Reviewed:5/2/2003
Keywords:kbStoredProc kbOLEApp kbDLL kberrmsg kbprb KB814578 kbAudDeveloper
©2002 Microsoft Corporation. All rights reserved.