FIX: OLE Automation Fails When Calling ADODB.Connection Methods

Article translations Article translations
Article ID: 295046 - View products that this article applies to.
This article was previously published under Q295046
BUG #: 352807 (SHILOH_BUGS)
Expand all | Collapse all

On This Page

SYMPTOMS

Using the sp_OAMethod OLE automation system stored procedure with a connection object ID as an integer datatype may generate an error of type -2146825287 and the corresponding error description:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

Use a connection string instead of a connection object ID:
EXEC @iRetCode = sp_OAMethod @iRSTObjId,'Open',null,'error_log',@sConnString,2,3,2
select @iRetCode
-- or
EXEC @iRetCode = sp_OAMethod @iRSTObjId,'Open',null,'error_log','Data Source=SERVERNAME;Provider=SQLOLEDB; Initial Catalog=pubs; Integrated Security=SSPI',2,3,2
select @iRetCode
				

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Run the following Transact-SQL statements in Query Analyzer:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ln_log_error]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[ln_log_error]
    GO
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    CREATE PROCEDURE ln_log_error
    AS
    DECLARE   @iConnObjId   INT,
              @iRSTObjId	INT,
              @iRetCode	INT,
              @sErrSource   VARCHAR(2550),
              @sErrDesc     VARCHAR(2550),
    	  @sConnString	VARCHAR(1000),
              @returnVal    INT
    -- Initialize variables.
      SET @sConnString = 'Data Source='+@@SERVERNAME+'; Provider=SQLOLEDB; Initial Catalog= pubs; Integrated Security=SSPI'
      select @sConnString
    -- Create the connection and open it.
          EXEC @iRetCode = sp_OACreate 'ADODB.Connection',@iConnObjId OUTPUT
          IF @iRetCode <> 0
          BEGIN
              EXEC sp_OAGetErrorInfo @iConnObjId, @sErrSource OUTPUT, @sErrDesc OUTPUT
              SELECT 'Connection Create Error: ', @sErrSource,@sErrDesc
          END  
          EXEC @iRetCode = sp_OAMethod @iConnObjId,'Open',NULL,@sConnString,'sa',''
          IF @iRetCode <> 0
          BEGIN
              EXEC sp_OAGetErrorInfo @iConnObjId, @sErrSource OUTPUT, @sErrDesc OUTPUT
              SELECT 'Connection Open Error: ', @sErrSource,@sErrDesc
          END
    -- Create a blank record for the error log table.
          EXEC @iRetCode = sp_OACreate 'ADODB.RecordSet',@iRSTObjId OUTPUT
          IF @iRetCode <> 0
          BEGIN
              EXEC sp_OAGetErrorInfo @iConnObjId, @sErrSource OUTPUT, @sErrDesc OUTPUT
              SELECT 'RecordSet Create Error: ', @sErrSource,@sErrDesc
          END  
    -- Open the recordset.
              EXEC @iRetCode = sp_OAMethod @iRSTObjId,'Open',NULL,'error_log',@iConnObjId,2,3,2            
              select @iRetCode
          IF @iRetCode <> 0
          BEGIN
              EXEC sp_OAGetErrorInfo @iConnObjId, @sErrSource OUTPUT, @sErrDesc OUTPUT
              SELECT 'RecordSet Open Error: ',@sErrSource,@sErrDesc
          END  
    -- Clean up.
          EXEC @iRetCode = sp_OADestroy @iRstObjId
          EXEC @iRetCode = sp_OADestroy @iConnObjId
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    					
  2. Execute ln_log_error from Query Analyzer.

Properties

Article ID: 295046 - Last Review: November 5, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000sp1fix KB295046

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com