Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: No error, so no rollback

    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?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Post the code please. It's almost impossible to answer such a question without having a precise idea of how the code is written.

    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    Here you go

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •