I am having a problem with a database at a customer site, and cannot replicate it at my office(nothing new, right?) I am running code to produce monthly statements for a health club. The code reads all of the invoices for a customer and writes them to a StatementDetail table, and then stamps the statement date on the invoice so it does not get included in next months statement.

The problem is that when I update the invoice record (ARMAST) with the statement date, it locks the table. This is only happening after a dozen or so customers have been processed. It produces the StatementDetail record fine, but it locks the table when it attempts to stamp the date on the ARMAST record. At this point where I get the error, I already have 72 rows which have sucessfully been stamped with the statement date.

1526
Timeout expired
S1T00

I can no longer update any records in ARMAST at this point -- I tried doing this using Query Analyzer. I have to kill the process entirely, at which the QA update finally processes.

I do the update with the command:

SQLEXEC(lnHandle,"UPDATE ArMast SET dStatementDate = ?this.sdProcDate WHERE nkArMast = ?csrInvoices.nkArmast")

Except for the INSERT statements to the StatementDetail (and StatementHeader) table, all of the rest of my SQL Statements are SELECT... statements that read the data.

I have tried adding WITH ROWLOCKS

UPDATE ArMast WITH (ROWLOCK) SET...

but it does not seem to make any difference.

Is there any switch in SQL that can make this happen? As I mentioned, I have a copy of the same database in my office and it runs with no problems.

Any suggestions would be appreciated!