How to solve memory leak when I use BCP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

How to solve memory leak when I use BCP?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-06 : 10:07:04

Carl Dan writes "When I used BCP to BULK INSERT data into SQL Server table from program varibles,I found memory leak with SQL Server service(Sqlservr.exe).How can I tackle the problem?

Here is BULK INSERT Thread(10 seconds scan):

...

SQLHENV henv = SQL_NULL_HDBC;
SQLHDBC hdbc = SQL_NULL_HDBC;

int nRowsProcessed = 0 ; // row processed

char szDataSource[MAXNAME]; // maxname =129;
char szUser[MAXNAME];
char szPassWord[MAXNAME];
char szTableName[MAXNAME];

// find table name, it no, create it;
CString sTempName;
sTempName = g_arrBufState[nBufIndex].sTableName; // Get table Name

Get_Char(g_tIniFile.sDataSource, szDataSource);
Get_Char(g_tIniFile.sDbUser,szUser);
Get_Char(g_tIniFile.sDbPsw, szPassWord);
Get_Char(sTempName, szTableName);

bool bHasODBCError = false;
bool bHasOtherError = false;

CString sErrorStr = _T("Eorror1£¬ File£º[") + g_arrBufState[nBufIndex].sFileName + _T("]£¬Cause£º\n");

while (true) // Main Loop
{
if (SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

// Notify ODBC that this is an ODBC 3.0 application.
if (SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

// Allocate an ODBC connection handle and connect.
if (SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

// Enable bulk copy prior to connecting on allocated hdbc.
if (SQLSetConnectAttr(hdbc, SQL_COPT_SS_BCP, (SQLPOINTER) SQL_BCP_ON, SQL_IS_INTEGER) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

//Use ODBC connection
if (SQLConnect(hdbc, (LPCBYTE)szDataSource, (SWORD)strlen(szDataSource),
(LPCBYTE)szUser, (SWORD)strlen(szUser),
(LPCBYTE)szPassWord, (SWORD)strlen(szPassWord)) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

//////////////////////////////////////////////////////////
// var of col name
char szType[2];
char szPhoneNumber[16];
char szCalledNumber[21];
char szCallDate[9];
char szCallTime[7];
char szCallDuration

;
char szCallFee[14];
char szCallType[2];
char szCityCode[3];
char * terminator = "\0";

// Initialize bcp.
if (bcp_init(hdbc, szTableName, NULL, NULL, DB_IN) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

// Set the count of rows per batch.
/*if (bcp_control(hdbc, BCPBATCH, (void*)g_tIniFile.nBufSize) == SQL_ERROR)
{
bHasODBCError = true;
break;
}
*/

/////////////////////////////////////////////////////
// Bind program variables to table columns. /////////
if (bcp_bind(hdbc, (LPCBYTE)szType, 0, SQL_VARLEN_DATA,
(LPCBYTE)terminator, 1, SQLCHARACTER, 1) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

if (bcp_bind(hdbc, (LPCBYTE)szPhoneNumber, 0, SQL_VARLEN_DATA,
(LPCBYTE)terminator, 1, SQLCHARACTER, 2) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

if (bcp_bind(hdbc, (LPCBYTE)szCalledNumber, 0, SQL_VARLEN_DATA,
(LPCBYTE)terminator, 1, SQLCHARACTER, 3) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

if (bcp_bind(hdbc, (LPCBYTE)szCallDate, 0, SQL_VARLEN_DATA,
(LPCBYTE)terminator, 1, SQLCHARACTER, 4) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

if (bcp_bind(hdbc, (LPCBYTE)szCallTime, 0, SQL_VARLEN_DATA,
(LPCBYTE)terminator, 1, SQLCHARACTER, 5) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

if (bcp_bind(hdbc, (LPCBYTE)szCallDuration, 0, SQL_VARLEN_DATA,
(LPCBYTE)terminator, 1, SQLCHARACTER, 6) == SQL_ERROR)
{
bHasODBCError = true;
break;
}

if (bcp_bind(hdbc, (LPCBYTE)szCallFee, 0, S

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-01-06 : 10:12:02

From your code snippet (well, snippet usually implies short

) it's not clear what you are doing. Perhaps your code was truncated.

I would advise you try to run bcp.exe against an import file and see what happens.

Jonathan
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources