This is a batch process to create credit card and ACH transactions. The transactions are derived from an account table and inserted into a temporary transaction table (tblTempTranactions, not a SQL #temp table) for validation purposes, and to create so other associated transaction information such as discount fees for credit cards. There may be 50,000 ACH or CC transactions and with the additional transactions that are needed, we end up with (assuming 50,000 transactions) between 100,000 and 150,000 transactions in this temporary table.

Once the batch is verified as accurate, these transactions need to be inserted into tblTransaction. In some cases we are reprocessing previously failed transactions and updates are performed to tblTransaction.

Two sprocs are used to do the inserts and updates. When the sproc is called that inserts the transactions, it in turn calls the sproc that performs the update. These sprocs are called as pass-through queries from an Access frontend. If the process timesout, no transactions are ever committed, either inserts or updates, leading me to assume that Access must be using Transactions to control the pass-through query. There are no Transactions statements in the sprocs. Correct me if I am wrong, but there is no default usage of Transactions in SQL Server.

Tomorrow I plan to separate the sprocs into two calls by Access and add in some logging to follow this process a little more closely.