records disappearing after insert, occasionally of course

  • Thank you all very much, we will take several of the suggestions and try them out to see if we can nail this down.

  • eshulman (3/28/2012)


    Thank you all very much, we will take several of the suggestions and try them out to see if we can nail this down.

    What suggestions?

    1. Change @@identity to scope_identity()? Seems irrelevant now that you can confirm sequence, yet gaps in your log table.

    2. Monitor for DEADLOCKs and ROLLBACKs? I thought you were already doing that?

    Can you give us some insight as to what you are going to try? Again, DDL and DML would really help us to help you.

    Jared
    CE - Microsoft

  • Thank you all very much, we will try a couple things that were suggested to see if we can figure this out...

  • If there are any check constraints that may have been violated (my testing had that reported to SSMS), the @@IDENTITY will return NULL and the indentity value still gets incremented.

  • Lynn Pettis (3/28/2012)


    If there are any check constraints that may have been violated (my testing had that reported to SSMS), the @@IDENTITY will return NULL and the indentity value still gets incremented.

    YES! I just verified the same thing, even with the most basic constraint of data type (although not classically defined as a constraint).

    USE tempdb

    CREATE TABLE test (id int identity(1,1), something int)

    SELECT * FROM test

    SELECT @@IDENTITY

    SELECT SCOPE_IDENTITY()

    INSERT INTO test

    SELECT 'Hello'

    SELECT @@IDENTITY

    SELECT SCOPE_IDENTITY()

    SELECT IDENT_CURRENT('test')

    INSERT INTO test

    SELECT 'Hello'

    SELECT @@IDENTITY

    SELECT SCOPE_IDENTITY()

    SELECT IDENT_CURRENT('test')

    --DROP TABLE test

    Jared
    CE - Microsoft

  • Yes, we were going to try SCOPE_IDENTITY but sounds like that will not make any difference since we see gaps.

    ALso was going to try the trace again - it is running now with nothing in it yet...

    I will collect come code and post it but as I said it is just an INSERT and SELECT @@IDENTITY.

    Some of our colleagues suggested that SQL Server is batching statements together and a totally unrelated compilation error or update/insert etc. could roll back the entire batch. Is that true?

    We tried to put Begin Transaction, Commit around our inserts but that did not make any difference at all.

  • eshulman (3/28/2012)


    Yes, we were going to try SCOPE_IDENTITY but sounds like that will not make any difference since we see gaps.

    ALso was going to try the trace again - it is running now with nothing in it yet...

    I will collect come code and post it but as I said it is just an INSERT and SELECT @@IDENTITY.

    Some of our colleagues suggested that SQL Server is batching statements together and a totally unrelated compilation error or update/insert etc. could roll back the entire batch. Is that true?

    We tried to put Begin Transaction, Commit around our inserts but that did not make any difference at all.

    SQL Server isn't batch requests together.

    This is in a SQL Server 2008 forum, just to be sure you are running SQL Server 2008?

  • how about adding another server side trace that captures all errors and warnings , and limit it to just the database in question.

    http://support.microsoft.com/kb/199037

    then you might see that some basic error, like a not null constraint or other constraint like Lynn mentioned is rolling back a transaction, but the application is not prepared to pass on/raise the error, so it goes by unnoticed.

    that might get you exactly what the issue really is.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (3/28/2012)


    how about adding another server side trace that captures all errors and warnings , and limit it to just the database in question.

    http://support.microsoft.com/kb/199037

    then you might see that some basic error, like a not null constraint or other constraint like Lynn mentioned is rolling back a transaction, but the application is not prepared to pass on/raise the error, so it goes by unnoticed.

    that might get you exactly what the issue really is.

    I second this... The example I posted fails without rolling back a transaction. So, if your insert is failing before the transaction is created, you will not see rollbacks. However, it apparently increments the identity anyway. I would throw a TRY CATCH block around you INSERT statement to see what is the culprit. I would guess that your inserts are failing before the transaction actually starts.

    Jared
    CE - Microsoft

  • Yes, 2008 R2...

  • we will try that, thank you much...

  • Below is our VB.NET code that we use to insert reservations into the XDispTrips table. Let me know if you would rather see an actual INSERT statement after it goes through this code.

    sqlTrip += "INSERT INTO XDispTrips (" 'Start building our SQL INSERT statement.

    If dsTrip.Tables.Count > 0 Then 'Make sure we have data to build from.

    dtTrip = dsTrip.Tables(0) 'Get the first table from the dataset. It is the XDispTrips table.

    If IsNothing(dtTrip) = False Then 'If we have a record then..

    '--------------------------------------------------------------------------------------------------

    'Loop through the first XDISPTRIPS_MAX_FIELD_INDEX columns of the XDispTrips table and add the

    'column names to the INSERT statement. Notice that we skip the first column. The first column is

    'trp_rec_id and it will be set automatically by the database.

    '--------------------------------------------------------------------------------------------------

    For iCount = 1 To XDISPTRIPS_MAX_FIELD_INDEX

    '---------------------------------------------------------------------------------------------------------

    'EXCLUDE FIELDS

    'There are some fields that we don't want TDS to set. These fields need to be set by the dispatch system.

    'Only the dispatch system is smart enough to know what these fields should be set to, plus with the

    'TDS_Dispatch system you will screw up dispatching by setting the status here (because you will overwrite

    'a status change made by TDS_Dispatch while the user had the trip open).

    '---------------------------------------------------------------------------------------------------------

    If bExcludeStatusUpdate = False OrElse _

    (dtTrip.Columns(iCount).ColumnName <> "trp_status" AndAlso _

    dtTrip.Columns(iCount).ColumnName <> "trp_priority_status") Then

    If iCount > 1 Then

    sqlTrip = sqlTrip & ", "

    End If

    sqlTrip = sqlTrip & dtTrip.Columns(iCount).ColumnName

    End If

    Next

    sqlTrip = sqlTrip & ") VALUES ("

    '------------------------------------------------------------------------------------------------------------------------------------------

    'Loop through the first XDISPTRIPS_MAX_FIELD_INDEX columns of the XDispTrips table and add the column values to the INSERT statement.

    'Notice that we skip the first column. The first column is trp_rec_id and it will be set automatically by the database.

    '------------------------------------------------------------------------------------------------------------------------------------------

    For iCount = 1 To XDISPTRIPS_MAX_FIELD_INDEX

    '----------------------------------------------------------------------------------------------------------------------------------------

    'EXCLUDE FIELDS

    'There are some fields that we don't want TDS to set. These fields need to be set by the dispatch system. Only the dispatch system is

    'smart enough to know what these fields should be set to, plus with the TDS_Dispatch system you will screw up dispatching by setting the

    'status here (because you will overwrite a status change made by TDS_Dispatch while the user had the trip open).

    '----------------------------------------------------------------------------------------------------------------------------------------

    If bExcludeStatusUpdate = False OrElse _

    (dtTrip.Columns(iCount).ColumnName <> "trp_status" AndAlso _

    dtTrip.Columns(iCount).ColumnName <> "trp_priority_status") Then

    If iCount > 1 Then

    sqlTrip = sqlTrip & ", "

    End If

    If (dtTrip.Columns(iCount).DataType Is GetType(System.String)) _

    Or dtTrip.Columns(iCount).DataType Is GetType(System.Char) Then

    sqlTrip = sqlTrip & sParm(Convert.ToString(dtTrip.Rows(0).Item(iCount)))

    ElseIf dtTrip.Columns(iCount).DataType Is GetType(System.DateTime) Then

    If IsDBNull(dtTrip.Rows(0).Item(iCount)) Then

    sqlTrip = sqlTrip & dParm(SysFnc.DefPastDate.ToString("MM/dd/yyyy HH:mm:ss"))

    Else

    sqlTrip = sqlTrip & dParm(Convert.ToDateTime(dtTrip.Rows(0).Item(iCount)).ToString("MM/dd/yyyy HH:mm:ss"))

    End If

    Else

    If IsDBNull(dtTrip.Rows(0).Item(iCount)) Then

    sqlTrip = sqlTrip & "0"

    Else

    sqlTrip = sqlTrip & Convert.ToString(dtTrip.Rows(0).Item(iCount))

    End If

    End If

    End If

    Next iCount

    sqlTrip = sqlTrip & ") SELECT @@IDENTITY" 'We need to have the trp_rec_id returned back to us. This line does that.

    ''--------------------------------------------------------------------------------------------------------------------------

    ''03/23/2012 - We tried wrapping this SQL statement in a BEGIN/COMMIT block, but it did not seem to help so we reversed it.

    ''--------------------------------------------------------------------------------------------------------------------------

    'sqlTrip += vbCrLf & "COMMIT TRANSACTION " & strTranName

    OpenDataSet(dsResults, sqlTrip) 'Execute our SQL INSERT statement.

    dsRow = GetFirstRow(dsResults) 'Find the new trp_rec_id.

    If IsNothing(dsRow) = False Then 'Find the new trp_rec_id.

    Identity = Convert.ToInt32(dsRow(0)) 'Find the new trp_rec_id.

    End If

    rcReturn = ResultCodes.rcOperationSuccessful 'We did it. Return SUCCESS.

    End If

    End If

    Return rcReturn 'Return the results of this function.

    Catch ex As Exception

    ExceptionHandler(ex, "Common::TDS_XDispTripsWrite")

    Return rcReturn

    End Try

    End Function

    Public Function OpenDataSet(ByRef RefDataSet As DataSet, ByVal CommandString As String) As Integer

    Dim iResult As Integer

    Dim dsTempSet As DataSet

    iResult = 0

    dsTempSet = Nothing

    Try

    gwsDbCommon.OpenSqlDataset(dsTempSet, CommandString)

    If dsTempSet.Tables.Count > 0 Then

    RefDataSet = dsTempSet

    iResult = ResultCodes.rcOperationSuccessful

    Exit Try

    End If

    iResult = ResultCodes.rcErrorsOccured

    Catch e As Exception

    iResult = ResultCodes.rcErrorsOccured

    ErrorHandler(Err, "modCO_Wrapper.OpenSQLDataSet", , True)

    Finally

    dsTempSet.Dispose()

    End Try

    Return iResult

    End Function

    <WebMethod(TransactionOption:=TransactionOption.RequiresNew)> _

    Public Function OpenSqlDataset(ByRef dsReturn As System.Data.DataSet, ByVal SqlText As String) As Integer

    Dim iResult As Integer

    Dim coConn As SqlConnection

    Try

    coConn = New SqlConnection(ApplicationConfiguration.ConnectionString)

    Dim sdaOpenRecordset As SqlDataAdapter

    dsReturn = New System.Data.DataSet

    sdaOpenRecordset = New SqlDataAdapter(SqlText, coConn)

    sdaOpenRecordset.Fill(dsReturn)

    coConn.Close()

    iResult = ResultCodes.rcOperationSuccessful

    Catch ex As System.Exception

    ErrorHandler(ex, "DbCommon::OpenSqlDataset", SqlText, False)

    iResult = ResultCodes.rcErrorsOccured

    Throw ex

    Finally

    Try

    coConn.Close()

    Catch

    End Try

    End Try

    Return iResult

    End Function

  • So, your code dynamically builds the insert statements? Not being a VB programmer, are the insert statments the same everytime they are built?

  • That is correct, the functions I sent you are executed about 12,000-18,000 times per day and work fine 99.9999% of the time. As I described before, the above code runs, then we do a sleep for 750ms and do a dirty (NOLOCK) read to see if the identity we got back is a record in the table. If it is not, we tell the user to save the reservation again (we have a few of these). Then we have another app that looks for these trips some several seconds later and if it finds a missing trip it goes to our logs and tries to recover them from log data (we have a few of these). The interesting thing is that the second case either the dirty read returned us a record that was rolled back right after the dirty read or there is some weird SQL Server stuff going on.

  • Well... My best educated guess is that an invalid insert statement is being built and passed in the ones that are failing. I would write something into the VB.NET code (since it is what generates the insert statement) that logs each statement it creates. Then search for the statements with the IDs that are missing in the log table. That will find you your situation(s) that create an invalid insert.

    Jared
    CE - Microsoft

Viewing 15 posts - 16 through 30 (of 67 total)

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