Error coding for MS SQL Server activities

[OrthoDocSoft]

Folks,

When I was tyring to get access databases to work (foolishly) I came across some error coding that I though was clever which looked something like this:

On error goto MSSQLErrorHandler

INSERT....

~~

Exit Sub

MSSQLErrorHandler:

Select Case Err

    Case 3197

        (do something)

    Case (other number)

        (do something else)

End Select

If Not TooManyTimes = True then

    Resume

Else

    Exit Sub

EndIf

Even tho I find MSSQL Server to be very sturdy, I'm about to expand my alpha site from 5 stations to 25, and I want to be prepared for the more common errors that may occur, especially collisions, when the more important MS SQL activities (ie "INSERTS" and "UPDATES" may occur. If the insert or update has an error, I want to retry it a few times.

Can anyone provide an simple, easy routine that GETS ME STARTED along this path? Should I stick with the construct shown above?

Much appreciated.

Ortho





"you cain't fix 'stupid'...

 

[JoeAtWork]

My opinion is that if "collisions" happen frequently enough that you feel the need to have special error handling for it, then you need to step back and figure out what is causing the error and fix that. I.e. if one process is blocking another (for example, by locking records) that's a good indication of a design flaw that should be fixed.

My error handling for database operations isn't really different from my general error handling:

    On Error Goto ErrHandler

    'Code here to do stuff

    Exit Sub

ErrHandler:

    'Call a subroutine to log the details of the error
    '(which procedure, the values of certain variables, and
    ' Err.Number and Err.Description)

    'Do whatever else is appropriate for this subroutine,
    'maybe it's OK just to display the error to the user
    'and keep going, or maybe we need to do something 
    'more drastic
End Sub

So now if the users report an error occurring, I would review the log to find out what is happening. You could also turn on SQL Server Profiler to log what is going on. With that you should be able to figure what the problem is and fix it.

The point I'm trying to make is that you shouldn't need a complex error handler to deal with contention issues. Instead, you should design the database and program so there are no contention issues (for the expected workload). The error handler should only kick in for factors beyond your control, such as network communication problems.

If the insert or update has an error, I want to retry it a few times.

I would not recommend that. Often if there is a contention issue, the user will get a timeout error (something like 30 seconds, depending on your database and program settings). "Trying again" will almost never work, and just multiply the annoyance factor for the user of waiting while the program "does nothing".

Should I stick with the construct shown above?

Personnally, I don't like that construct and wouldn't use it anywhere. For any unexpected error (i.e. anything other than # 3197) you apparently just let it go on to the next line of code. But since you have no idea what went wrong in the previous step, you are now in an ambiguous state. Does some variable now have the wrong value? Did a database error occur? You have no idea, so therefore your subroutine should not just continue on its merry way. Show the user an error message, log the details (because most users do not write down the exact error), and then exit.