Batch Transactions

Corinna John

My boss called me by my private phone number in the evening, because somebody else had corrupted the content of an MS Exchange public folder, so that my windows service that transferred the content into an better-to-query SQL Server database crashed. The strange part: It did not crash with an constraint violation exception, but with some transaction error message. Here is the critical loop:

while (!recordset.EOF)
{
if (batchCounter == 0)
{
sqlTran = connection.BeginTransaction();
insertCommand.Transaction = sqlTran;
}

// ... three screen pages below ...

try
{
	insertCommand.ExecuteNonQuery();
	batchCounter++;
}
catch (Exception ex)
{
	Common.WriteTrace(TraceType.Error, ComponentName.SqlCacheManager, message);
}

if (batchCounter == batchSize)
{
	sqlTran.Commit();
	batchCounter = 0;
}

recordset.MoveNext();

}

When the constraint violation occured in the very first record of a batch transaction block, batchCounter was still 0 after the catch block, so the next loop tried to open a new transaction before the current transaction was closed. The problem could be solved by swapping two lines:

try
{
	**batchCounter++;
	insertCommand.ExecuteNonQuery();**
}
catch (Exception ex)
{
	Common.WriteTrace(TraceType.Error, ComponentName.SqlCacheManager, message);
}

____________________________________ There is no proof for this sentence.

Mike Dimmick

Yuck. I have a contact in Exchange support, and as I recall, public folders are slated to be removed in the next version, as they're one of the highest causes of support calls. Better make that whatever-it-is a standalone application, rather than using public folders.

Stability. What an interesting concept. -- Chris Maunder

Corinna John

Public folders are pure horror. Anyway, our boss loves them and he wants us to use them for storing shared Outlook tasks (yes, it's true: we have to store IPM.TASK in public folders). That's why we store all shared tasks and mails in a public folder. But we cannot work with those objects, because public folders are slow and don't allow descent queries. So we built a horrible "solution". There is a windows service running on the Exchange server that copies the public folder into an SQL Server 2005. For better performance, the SQL Server is running on the same machine. All Exchange items have a custom field with an GUID that corresponds with the primary key in the SQL database. now that we have all data in an SQL database, we could throw away the Exchange server ... but there's one man in the company who really loves Exchanges ... :rolleyes: You see, we have a lot of fun at work :rolleyes:

____________________________________ There is no proof for this sentence.

Vikram A Punathambekar

Corinna John wrote:

that transferred the content into an better-to-query SQL Server database

I'm sorry if this is a stupid question, but what does that mean? :confused:

Cheers, Vikram.

"But nowadays, it means nothing. Features are never frozen, development keeps happening, bugs never get fixed, and documentation is something you might find on wikipedia." - Marc Clifton on betas.

Join the CP group at NationStates. Password: byalmightybob

Tim Carmichael

The query interface for Exchange is not very good; actually, it is very painful at times... Putting the data into SQLServer should provide a) a better query interface b) faster queries. Tim

Vikram A Punathambekar

Ah, so she was comparing how easy it is to query SQL Server vs. Exchange. That phrase struck me as odd. :doh:

Cheers, Vikram.

"But nowadays, it means nothing. Features are never frozen, development keeps happening, bugs never get fixed, and documentation is something you might find on wikipedia." - Marc Clifton on betas.

Join the CP group at NationStates. Password: byalmightybob

Mike Dimmick

Exchange is a property database, designed to be good for retrieving BLOB data. It isn't any good at fixed-format data. SQL Server is a fixed-format relational database - it's not so good at BLOBs. Unsurprisingly this mismatch means that Exchange is quite good at your email but pretty poor at line-of-business applications, while SQL does outstanding work on OLTP and OLAP workloads but is hard to set up for data which has a very flexible structure (like email where the headers, body and attachments don't have bounded lengths and there are a variable number of headers and attachments, with very few headers being required). My contact in Exchange support has told me before about companies using public folder replication as a critical business tool, and invariably replication seems to go wrong.

Stability. What an interesting concept. -- Chris Maunder

Corinna John

Mike Dimmick wrote:

replication seems to go wrong

Replication and MAPI access seem to destroy the BLOB field that's called "text-description" in WebDAV and "message.Body" in CdoEx. Whenever we move a public folder to another server, we loose some message bodies. The solution seems to be: Use only the content class "message" and nmessage type "IPM.Note". All other message classes get broken at random.

____________________________________ There is no proof for this sentence.