Knowledge Base

FIX: Memory Leak in MDAC SQLOLEDB Provider on SERVERDATAONINSERT and Column Size That Is Larger Than 255

Article ID: 302995

Article Last Modified on 9/26/2005

APPLIES TO

Microsoft Data Access Components 2.1
Microsoft Data Access Components 2.1 Service Pack 2
Microsoft Data Access Components 2.1 Service Pack 1
Microsoft Data Access Components 2.1 Service Pack 2
Microsoft Data Access Components 2.5
Microsoft Data Access Components 2.5 Service Pack 1
Microsoft Data Access Components 2.5 Service Pack 2
Microsoft Data Access Components 2.6

This article was previously published under Q302995

SYMPTOMS

A memory leak occurs in Microsoft OLE DB Provider for SQL Server (SQLOLEDB) with SERVERDATAONINSERT when the column size is larger than 255.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft MDAC 2.5. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

293312INFO: How to Obtain the Latest MDAC 2.5 Service Pack

You may also resolve this problem by obtaining the latest service pack for SQL Server 2000, the latest service pack for MDAC 2.6, or the hotfix referenced below. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

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

300635 INFO: How to Obtain the Latest MDAC 2.6 Service Pack

Hotfix

MDAC 2.5

The English version of this fix should have the following file attributes or later:

   Date         Version    Size           File name     Platform
   -------------------------------------------------------------
   04-Jun-2001  7.1.9.99   545,040 bytes  Sqloledb.dll  x86

MDAC 2.6

The English version of this fix should have the following file attributes or later:

   Date          Version        Size           File name     Platform
   ------------------------------------------------------------------
   11-Jun-2001   2000.80.301.0  491,584 bytes  Sqloledb.dll  x86
   11-Jun-2001   2000.80.301.0   61,440 bytes  Sqloledb.dll  x86

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft MDAC 2.5 Service Pack 3. This problem was first corrected in SQL Server 2000 Service Pack 2 and MDAC 2.6 Service Pack 2.

MORE INFORMATION

Steps to Reproduce Behavior

In Microsoft SQL Server 2000, create the following tables:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[STATEMIN1]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[STATEMIN1]<BR/>
GO

CREATE TABLE [dbo].[STATEMIN1] (
    [messagekey] [int] IDENTITY (1, 1) NOT NULL ,
    [message] [varchar] (2000) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[STATEMIN1] WITH NOCHECK ADD 
    CONSTRAINT [PK_STATEMIN1] PRIMARY KEY  CLUSTERED 
    (
        [messagekey]
    )  ON [PRIMARY] 
GO

Paste the following code into a Microsoft Visual C++ console application, and then compile the code.

NOTE: You must change the datasource name, userid, and password.

#include <afx.h>
#include <assert.h>
#include <iostream>
#include <atldbcli.h>
#include <objbase.h>

const ULONG nInitProps = 4;
const ULONG nPropSet = 1;
const ULONG nParams = 3; //Number of parameters in the command
const ULONG nProps = 5;

struct StateIn
    {
    UINT uiMessageKey;
    TCHAR pszMessage[257];
};

int main(void)
{
    CCommand<CManualAccessor,CRowset,CNoMultipleResults> rs;

    CDataSource m_connect;
    CSession m_session;
    
    DBPROP    InitProperties[nProps];
    DBPROPSET   rgInitPropSet;

    USES_CONVERSION;
    HRESULT   hr;

    hr = CoInitialize(NULL);

    for (ULONG i = 0; i < nProps; i++ )
    {
        VariantInit(&InitProperties[i].vValue);
        InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
        InitProperties[i].colid = DB_NULLID;
    }

    InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT;
    InitProperties[0].vValue.vt = VT_I2;
    InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;

    InitProperties[1].dwPropertyID = DBPROP_INIT_DATASOURCE;
    InitProperties[1].vValue.vt = VT_BSTR;
    InitProperties[1].vValue.bstrVal = 
         SysAllocString(OLESTR("servername"));                            // Server Name

    InitProperties[2].dwPropertyID = DBPROP_INIT_CATALOG;
    InitProperties[2].vValue.vt = VT_BSTR;
    InitProperties[2].vValue.bstrVal = SysAllocString(L"database");    // Database Name
    InitProperties[2].dwOptions = DBPROPOPTIONS_REQUIRED;
    InitProperties[2].colid = DB_NULLID;

    InitProperties[3].dwPropertyID = DBPROP_AUTH_USERID;
    InitProperties[3].vValue.vt = VT_BSTR;
    InitProperties[3].vValue.bstrVal = SysAllocString(OLESTR("sa"));    // User Id.

    InitProperties[4].dwPropertyID = DBPROP_AUTH_PASSWORD;
    InitProperties[4].vValue.vt = VT_BSTR;
    InitProperties[4].vValue.bstrVal = SysAllocString(OLESTR(""));      // Password

    
    rgInitPropSet.guidPropertySet = DBPROPSET_DBINIT;
    rgInitPropSet.cProperties = nProps;
    rgInitPropSet.rgProperties = InitProperties;

    char szProgID[100] = {0};
    strcpy(szProgID,"SQLOLEDB");
    CLSID clsid;
    WCHAR   wszProgId[100] = {0};
    MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, szProgID, -1,   wszProgId, 100);
    hr = CLSIDFromProgID(wszProgId, &clsid);
    hr = m_connect.Open(clsid,&rgInitPropSet);
    hr = m_session.Open(m_connect);

    SysFreeString(InitProperties[1].vValue.bstrVal);
    SysFreeString(InitProperties[2].vValue.bstrVal);
    SysFreeString(InitProperties[3].vValue.bstrVal);
    SysFreeString(InitProperties[4].vValue.bstrVal);

    DWORD dwStatus;
    dwStatus = DBSTATUS_S_IGNORE;

    CDBPropSet propset(DBPROPSET_ROWSET);
    propset.AddProperty(DBPROP_IRowsetChange, true);
    propset.AddProperty(DBPROP_IRowsetUpdate, false);
    propset.AddProperty(DBPROP_UPDATABILITY,
        DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
    propset.AddProperty(DBPROP_SERVERDATAONINSERT,true);

    StateIn rowValue_StateIn;

    rs.CreateAccessor(2, &rowValue_StateIn, sizeof(rowValue_StateIn));

    rs.AddBindEntry(1, DBTYPE_I4,sizeof(rowValue_StateIn.uiMessageKey), 
          &rowValue_StateIn.uiMessageKey,NULL,&dwStatus);
    rs.AddBindEntry(2, DBTYPE_STR, sizeof(rowValue_StateIn.pszMessage),
          &rowValue_StateIn.pszMessage,NULL);

    TCHAR * m_strQuery = _T("select * from statemin1");
    hr = rs.Create(m_session,m_strQuery);
    hr = rs.Open(&propset);
    
    dwStatus = DBSTATUS_S_IGNORE;
    TCHAR szInsStr[50];
    strcpy(szInsStr,"test1");

    strcpy(rowValue_StateIn.pszMessage, szInsStr);

    hr = rs.Insert(0,1);

    for(int j = 0;j < 20000;j++)
    {   
        strcpy(rowValue_StateIn.pszMessage, szInsStr);
        hr = rs.Insert(0,1);

    }

    return S_OK;
}

Start Performance Monitor, and watch the Private Bytes counter for the process while you are running the code.

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

301953 SQLOLEDB: Unenlisting from Active Transaction Against SQL Server 7.0 Causes Hang and Drain Abort

Keywords: kbbug kbfix kbqfe kbsqlserv2000presp2fix kbmdac260sp2fix kbmdac250sp3fix kbhotfixserver KB302995

Microsoft KB Archive/302995

Contents