No error, so no rollback - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

No error, so no rollback

Author

Topic

e.heijmans@12move.nl
Starting Member

10 Posts

Posted - 2009-03-27 : 13:55:09

I have a problem with SQL code, which worked fine for over a year, but now all of a sudden behaves strangly in several databases. I cannot reproduce the problem myself what makes it even more difficult.

It's about a site that employees can use to register the hours they work in a week. These records are put in a table ESS_dwwuren. After that, a manager on another part of the site, can approves these hours and the records are then inserted in the tabel Dwwuren. Consequently they are deleted from the original table. All this within a transaction and a try...catch construction.

Now, all of a sudden, it happens more and more that hours are not inserted in the target table, now error is raised, therefore the code runs on and delete the records in the source table. Result: data gone.

What can be the reason that INSERT doesn't work. But, more interestingly, why is no error raised? Many weeks are approved without a problem, and every once in a while this problem arises. It can't have anything to do with locks (I think), because it's an INSERT statement.

At this moment the databases (quite a few) run on a web server with SQL Express installed. Could it be that SQL Express isn't able to deal with all the requests? But than again, why is no error raised.

Who can help me?

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-03-27 : 18:23:06

Hello;

Since you say everything is handled within a try catch block it might interest you to know that not every error that may occur is going to be trapped & handled.

Here is an excerpt on try-catch from Books Online (BOL).


Errors Unaffected by a TRY…CATCH Construct

TRY…CATCH constructs do not trap the following conditions:

    * Warnings or informational messages that have a severity of 10 or lower.
    * Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
    * Attentions, such as client-interrupt requests or broken client connections.
    * When the session is ended by a system administrator by using the KILL statement.

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

    * Compile errors, such as syntax errors, that prevent a batch from running.
    * Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

And here is the link I got it from in case you wish to do more research.
[url]http://msdn.microsoft.com/en-us/library/ms175976.aspx[/url]

I suggest look and see if you code or system has any of the issues listed that are not getting trapped by the syntax.

r&r

e.heijmans@12move.nl
Starting Member

10 Posts

Posted - 2009-03-29 : 17:56:34

But that's not the issue here. The INSERT fails (without raising an erro) while the DELETE that follows is executed. If the INSERT fails because of an error with severity 20 or higher, it should never reach de DELETE statement.

Below part of the code

BEGIN TRY
BEGIN TRAN

SET DATEFIRST 1

--Het wegschrijven van de uren in de PlanRadtabel
INSERT INTO dbo.dwwuren (Personeelsnummer, Projectnummer, Datum, Soort, Uren, Opmerking, ESS_Gewijzigd)
SELECT Personeelsnummer, Projectnummer, Datum, Soort, Uren, Opmerking, 1 FROM dbo.ESS_dwwuren
WHERE Personeelsnummer = @Personeelsnummer
AND (dbo.fn_WeeknummerISO(Datum) = @Week AND YEAR(dbo.fn_BepaalEinddatumWeek(Datum)) = @Jaar)

--Het wissen van de uren uit de brontabel
DELETE FROM dbo.ESS_dwwuren
WHERE Personeelsnummer = @Personeelsnummer
AND (dbo.fn_WeeknummerISO(Datum) = @Week AND YEAR(dbo.fn_BepaalEinddatumWeek(Datum)) = @Jaar)

--De oude status wordt vervangen door de nieuwe
UPDATE dbo.ESS_Weekstatus
SET Status = 3
WHERE Personeelsnummer = @Personeelsnummer
AND WeekJaar = dbo.fn_VersleutelWeekJaar(@Week, @Jaar)
AND Status = 2
AND Soort = @Soort

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRAN

--Wegschrijven van de foutmelding

DECLARE @Foutmelding nvarchar(4000), @Foutnummer as int, @FoutErnst as int

SET @Foutnummer = ERROR_NUMBER()
SET @Foutmelding = ERROR_MESSAGE()
SET @FoutErnst = ERROR_SEVERITY()

INSERT INTO dbo.Logboek (Tijd, Personeelsnummer, [Week], Jaar, Melding)
VALUES (GETDATE(), @Personeelsnummer, @Week, @Jaar, 'Fout ' + CAST(@Foutnummer as nvarchar(400)) + ': ' + @Foutmelding)

RAISERROR(@Foutmelding, @FoutErnst, 1)

END CATCH

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources