Professional Web Applications Themes

Losing uncommitted Data? - Microsoft SQL / MS SQL Server

I have attached a copy of a stored procedure which I am using. This stored procedure contains a Begin Tran and a Commit Tran. However, when we call this stored procedure and the connection is unexpectedly terminated all data being added is rolledback. Yet, The EmployeeScheduleId, PK is being returned to the application as if the add was successfully. When we search the database this Id does not exist and SQL server has skipped this Id. Would putting this statement "SELECT IDENTITY as EmployeeScheduleId" ensure that only the Id is returned if the data is committed or is their a ...

  1. #1

    Default Losing uncommitted Data?

    I have attached a copy of a stored procedure which I am
    using. This stored procedure contains a Begin Tran and a
    Commit Tran. However, when we call this stored procedure
    and the connection is unexpectedly terminated all data
    being added is rolledback. Yet, The EmployeeScheduleId,
    PK is being returned to the application as if the add was
    successfully. When we search the database this Id does
    not exist and SQL server has skipped this Id.

    Would putting this statement "SELECT IDENTITY as
    EmployeeScheduleId" ensure that only the Id is returned if
    the data is committed or is their a way I can return a
    message to the application if the data was not committed?

    I think that a deadlock may be occurring on the database
    at the time of adding and our application server reboots
    thus unexpectedly terminated the connection to sql
    server. All data for that connection is rolled back.
    (Data is only lost when our application server reboots)
    Can anyone help me with what may be happening here.

    Thanks
    Jen


    ALTER PROCEDURE [AddEmployeeSchedule]
    (EmployeePersonId [int],
    LocationId [int],
    StartWorkDay [DateTime],
    EndWorkDay [DateTime],
    Confirmation [bit],
    ActiveFlag [bit],
    UpdateUserId [int])

    AS

    BEGIN TRAN

    INSERT INTO [dbo].[tblEmployeeSchedule]
    ([EmployeePersonId],
    [LocationId],
    [StartWorkDay],
    [EndWorkDay],
    [Confirmation],
    [ActiveFlag],
    [LastUpdateTimestamp],
    [LastUpdateUserId])

    VALUES
    (EmployeePersonId,
    LocationId,
    StartWorkDay,
    EndWorkDay,
    Confirmation,
    ActiveFlag,
    GETDATE(),
    UpdateUserId)

    SELECT IDENTITY as EmployeeScheduleId

    COMMIT TRAN
    Jen Guest

  2. #2

    Default Re: Losing uncommitted Data?

    Jen,

    If you issue IDENTITY that value it generates can not be reclaimed. If
    you need the value to be contiguous then you should generate the id your
    self but consider this. This is a PK and as such it's purpose in life is
    only to be unique, not contiguous. Your using an INTEGER as the datatype
    and can support billions of values. I don't think you should be concerned
    about loosing a number here or there.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Jen" <com> wrote in message
    news:0bb301c34c79$aa8b8550$gbl... 


    Andrew Guest

  3. #3

    Default Re: Losing uncommitted Data?

    At the end of your procedure, replace your Select and Commit statements with
    the following:

    IF ERROR <> 0
    BEGIN
    SELECT IDENTITY AS EmployeeScheduleId
    COMMIT TRANSACTION
    END
    ELSE
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR ('What ever message you want',16,1)
    END

    "Jen" <com> wrote in message
    news:0bb301c34c79$aa8b8550$gbl... 


    David Guest

Similar Threads

  1. Losing Data
    By Longhorngator in forum Coldfusion Database Access
    Replies: 1
    Last Post: June 22nd, 06:49 PM
  2. Losing Data Sourxe
    By arjahan in forum Coldfusion Server Administration
    Replies: 1
    Last Post: May 13th, 04:11 PM
  3. Losing Form data
    By Victor Garcia Aprea [MVP] in forum ASP.NET General
    Replies: 3
    Last Post: October 2nd, 01:54 PM
  4. [PHP] SESSION variables losing data on WinXP?
    By Larry Li in forum PHP Development
    Replies: 4
    Last Post: September 18th, 05:57 AM
  5. Session Losing Data
    By Mark MacRae in forum ASP.NET General
    Replies: 0
    Last Post: July 18th, 07:04 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139