FIX: OLE Automation Fails When Calling ADODB.Connection Methods

This article was previously published under Q295046
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 352807 (SHILOH_BUGS)
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 theMicrosoft 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,2select @iRetCode-- orEXEC @iRetCode = sp_OAMethod @iRSTObjId,'Open',null,'error_log','Data Source=SERVERNAME;Provider=SQLOLEDB; Initial Catalog=pubs; Integrated Security=SSPI',2,3,2select @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]GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE ln_log_errorASDECLARE   @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 @iConnObjIdGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO					
  2. Execute ln_log_error from Query Analyzer.
Properties

Article ID: 295046 - Last Review: 01/16/2015 22:18:08 - Revision: 3.2

  • Microsoft SQL Server 2000 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB295046
Feedback