Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Autonumber skipping values

    Our website is in ASP classic backed with sql server 2000. We have a custom error page that logs all errors in to a table. Looking at the table I see the autonumber/id field is not in a continuous sequence. A co-worker found this:

    SQL Server makes no attempt to guarantee sequential gap-free values in identity columns. If records are deleted SQL Server won't go back and populate using those values. It's also possible for an insert to fail and "use up" an identity value. Books Online has additional detailed information about identity columns.

    We don't delete records from this table so am curious if inserts are failing. I noticed we weren't escaping the querystring before putting it into the db. Before I fixed this I put some sql in the querystring and I received an error but looking in the table there was not an entry. My thought was there might be a gap in sequence in the table now. After escaping the querystring I reproduced the errors and they were logged into the table, but there was no gap in sequence from when the same errors weren't logged.

    My question is if anyone else has any comments or experience with the identity column skipping values. Also if the sql syntax is good what else would cause an insert to fail? Timeouts? Constraints?

    Thanks,
    GG
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i have a comment about gaps in identity values --

    if the gaps matter, your design is flawed

    just ignore the gaps

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    145
    nothing relies on the id column of this table so I guess in that sense it doesn't "matter", but it seems something isn't right. I've never ran into this before.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He is just worried that the gaps may indicate a hidden problem.

    Post an example of the code you use to insert records into the table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I should have such worries.

    Some of you actually get to work on projects where people are not jumping up and down with their hair on fire waiting on stuff?

    how do you get motivated everyday?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jan 2004
    Posts
    145
    Here is the current code. I didn't create it originally. We should make it into a stored proc and maybe use a command object, anyway...

    Code:
    <%
     On Error Resume Next 
       set objError = Server.getLastError()
       strNumber = objError.AspCode
          If strNumber = "" then strNumber = "No code number available"
       strSource = replace(objError.Category, "'", "''")
       strPage = objError.File
       strDesc = replace(objError.Description, "'", "''")
       strCode = Server.HTMLEncode(objError.Source)
       If strCode = "" then strCode = "No code available"
       strLine = ObjError.Line
       strASPDesc = replace(ObjError.ASPDescription, "'", "''")
       strErrorDesc = strDesc + "." + strASPDesc
       strRemoteAddr = Request.ServerVariables("REMOTE_ADDR")
       strRemoteHost = Request.ServerVariables("REMOTE_HOST")
       strLocalAddr = Request.ServerVariables("LOCAL_ADDR")
       strQuery_String = replace(Request.ServerVariables("QUERY_STRING"), "'", "''")
    
       set Conn = Server.CreateObject("ADODB.Connection")
       conn.ConnectionString = strCon
       conn.open
       conn.execute("Insert into ERROR_TABLE" _
       					& "number," _
    					& "source," _
    					& "page," _
    					& "desc," _
    					& "code," _
    					& "line," _
    					& "remote_addr," _
    					& "remote_host," _
    					& "local_addr," _
    					& "QS) " _
    				& "values(" _
    					& "'" & strNumber & "'," _
    					& "'" & strSource & "'," _
    					& "'" & strPage & "'," _
    					& "'" & strErrorDesc & "'," _
    					& "'" & strCode & "'," _
    					& "'" & strLine & "'," _
    					& "'" & strRemoteAddr & "'," _
    					& "'" & strRemotehost & "'," _
    					& "'" & strLocalAddr & "'," _
    					& "'" & strQuery_String & "')")
       %>
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suspect that you identified and fixed the error when you escaped the query string.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2007
    Posts
    183
    Missing "(" after ERROR_TABLE.

  9. #9
    Join Date
    Jan 2004
    Posts
    145
    Peso, that was a typo when posting. I made some changes. But I was able to figure things out. What was happening was the querystring was too long for the field in the db. I just truncated the querystring before inserting and all records are being inserted successfully.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

Posting Permissions

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