Sunday, February 26, 2012

Getting intermittent error with SQLDMO objects

In an application that uses SQLDMO object to BCP data into a empty table in a database I get the following error only occassionally on one or two machines out of hundreds. I would like to understand why and resolve the issue.

From the application log file.

Bulk Copying data
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Error Description
-2147220299 Error Error
IDispatch error #693 Error Message
An error occurred while processing [RMS_EDM_TUTORIAL] Batch Copy:[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.RmsDbManager::Process

The Code involved.

The error is thrown at the the following line in the code below.

dmoTable = dmoTables->Item((LPCTSTR)sTableName); // Error thrown I think by ODBC driver but not sure. The error indicates locked locked resources but which resources (no query involved that I see) and on the machine where the error occurrs it happens every time. Help would be greatly appreciated.

bool

MyClassSqlServer::BatchCopy( const char *sDBName, const char *sDir, const char *sFileSpec, MyClassFile& logfile)
{
MyClass_ASSERT_VALID;
MyClassBool bRet = MyClass_FALSE;
ASSERT(sDBName);
ASSERT(sDir);
ASSERT(sFileSpec);
ASSERT(m_dmoServer.GetInterfacePtr());
CString sPath(sDir), sDataFile, sTableName;

try
{
sPath += PATH_SEP;
sPath += CString(sFileSpec);

CFileFind fileFind;
BOOL bContinue = fileFind.FindFile(sPath);

SQLDMO::DatabasesPtr dmoDbs;
SQLDMO::_DatabasePtr dmoDb;
SQLDMO::TablesPtr dmoTables;
SQLDMO::_TablePtr dmoTable;
dmoDbs = m_dmoServer->GetDatabases();
dmoDb = dmoDbs->Item(sDBName);
dmoTables = dmoDb->GetTables();

while ( bContinue )
{

bContinue = fileFind.FindNextFile();
sDataFile = fileFind.GetFilePath();
sTableName = fileFind.GetFileTitle();
sTableName = "[" + sTableName + "]";
CString msg;

if (!fileFind.IsDirectory())
{
dmoTable = dmoTables->Item((LPCTSTR)sTableName);
msg.Format("BCP file is: %s\n", sTableName);
logfile << msg;
BulkCopy ( dmoTable, sDataFile);
}
}
fileFind.Close();
bRet = MyClass_TRUE;
}
catch(_com_error &err)
{
TRACE0("An error occurred in MyClassSqlServer::BatchCopy()");
logfile << err.Description() << " Error Description \n";
logfile << err.Error() << " Error Error\n";
logfile << err.ErrorMessage() << " Error Message\n";
throw Except(Except::se_BatchCopy, err.Description(), err.ErrorMessage());
}

I moved this thread to data access as this issue does not seem to be rooted in DMO. I hope that someone in this group can help out.

No comments:

Post a Comment