Database update intermittently fails/not executed

  • I have a really puzzling problem. I have an ASP (not .net) application that has, among other things, two pages. Each page contains a list of transactions that need to have the same action taken on each, and with a check box next to each transaction. The user indicates which transactions need to be acted upon by checking the check box, and then presses the action button. The application reads through the list, and performs the action one-by-one on each selected transaction. After all the selected transactions are complete, the page refreshes, hopefully without the previously selected transactions, because they should no longer meet the criteria for being on the page.

    One page performs a status change. To do this, each selected transaction is updated with the new status by means of a stored procedure. (And yes, I realize this is operating like a cursor, which is frowned upon.) The other page sends an email, then updates the record with the date and time the email was sent, again using a stored procedure. The email is generated using a CDO.Message object (which is created, used, and then set to nothing for each transaction), with some attachments which make it rather large, and goes out to four addresses. If no errors are detected (and you can't detect much from CDO.Message, but you might discover a missing email address), then the database is updated. Again, all of this happens one row at a time.

    So what's the problem? Nothing, in the first instance, where I'm just updating the database. And usually nothing, in the second instance, where I'm querying the database, sending an email, and then updating the database. Because the emails are so large, and going to a number of addresses, I limit the user to sending 25 at a time, just to give the email server a chance to breathe. And because there are usually more than 25 emails to send, the user has to run the process multiple times. And as far as I've been able to determine, this never happens for the first batch, or the second. But run it more than twice, and you may run into the problem, which is all of the emails are sent, but none of them have their corresponding record updated. And what's even more bizarre is that in order to send the email, the application needs to get some data from the database, and it has no problem doing that. So our current workaround is to run a batch of emails, log out of the application, and then log back in for each batch.

    So if you've made it this far, let me explain why I'm posting this in a SQL site, and not an ASP site. I need to see what's happening in the database when this operation is running, and I don't know how to do that. I'm a developer, and we don't have a DBA (please let's not get into that, put it down to 'your tax dollars at work'), so all I know about debugging is what I read in this newsletter. I'm familiar with the terms trace files, and SQL Profiler, but I've never used them, don't know how to (but I know I can learn from BOL), and don't know when they're appropriate.

    The other reason I'm posting this here is because I can tell from reading the postings that there are a lot of different types of data processing professionals who use this site, and maybe something about this problem will ring a bell with one of you. So if you haven't given up on this posting by now, I appreciate both your patience and whatever suggestions you can offer about how to debug this problem.

    Thanks,

    Mattie

     

  • This was removed by the editor as SPAM

  • Just a stab in the dark, but it sounds like a timeout problem in the sql that is causing a rollback of the transcations.

    Not too sure how you would go about checking this type of problem. I would first check to see if the transactions are still open using: DBCC OPENTRAN('database name') .

    If the transactions are being rolled back I cannot give you any tips other than purchasing some software to read the transaction logs.

    I am sure if you go into profiler and monitor the session, you will at least see what is happening in real time.


    ------------------------------
    The Users are always right - when I'm not wrong!

  • Jonathan,

    Thank you so much for responding.  Between now and next month when we run this process again, I will look into the topics you recommended. (Of course I can't get it to fail in test&nbsp  And since part of my database update consists of adding a record to a history table that has a unique identifier as the key, I'll be able to tell if it hit that table by looking for a gap in numbers.

    Just out of curiosity, is it possible to trap a timeout error from a stored procedure?  And am I correct in thinking that I can increase the timeout time with a parameter in my connection string?

    Once again, thanks for the suggestions.  I'll post whatever I find out, or end up doing.

    Mattie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply