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());
}
