One part of the investigations this morning has been looking into how we manage the database access as we add parts of an incoming message to it for later processing. As we have multiple servers processing inbound messages, there was the potential for two threads trying to write to the database simultanously with different parts of the same message. Both could look at the database and think there were no existing parts of a multipart message, and decide to try and insert a new multipart message record.
During this work we found out that SQL Server 2000 will still raise an SQLException error despite error handling put in T-SQL.
Continue reading Stored procedure error handling in SQL Server 2000