Post reply

@@IDENTITYin very busy site - if many insert in same time What happend?

  • August 7, 2008 at 12:05 pm

    #854673

    There could also be something really silly going on in the logic. Something like:

    CREATE TABLE #Temp

    ( RecID INT IDENTITY(1, 1)

    , SomeText VARCHAR(50)

    )

    INSERT INTO #Temp

    ( SomeText )

    SELECT 'Some Text'

    WHERE 1 = 2

    Nothing is inserted - no Scope Identity no Rows Affected.

    Todd Fifield

  • August 7, 2008 at 1:32 pm

    #854729

    Thank you very much for all responses

    Yes

    There is one column INDENTITY all the rest columns accept NULLS no duplicate check nothing

    ONLY SIMPLE INSERT

    There NO DB Error log when call the SQL statement, But fail to insert (because there is no GAPS into indentity column)

    There is trace GUID from the client they came to our web site with this guid we store it in DB in this table and we redirect to thier page with the GUID and they have confirmation that this person visit our web site and COMPLETE his purpose.

    BUT OUR problem is there is NO RESPONSE FROM THIS PERSON

    BECAUSE IN THIS TABLE where we store this guid, cometime .... + @@INDENTITY return NULL

    WE USE THIS INDENTITY ID and store his response in other table

    The margin error is fine 6000 clients / 40-50 lose / 1-3 clients per second

    I just want to know MORE about this...

    it is SIMPLE INSERT INTO then SELECT @INDENTITY as ID ....but NULL :w00t:

    Thank you very much again for responses

    valentin

  • August 7, 2008 at 1:45 pm

    #854738

    As I said in my last post. Run a Trace against the server to make sure the application is actually doing the insert. If the insert was failing you WOULD have a gap in the Identity values because, even when you do not explicitly say BEGIN TRANSACTION, there is a transaction by default and the identity value is incremented.

    You NEED the trace to verify what you think is happening IS really happening. I assume the web application code is running the INSERT code either directly or through a stored procedure and, based on the information you have provided and the accumulated SQL Server knowledge of all those who have commented on this thread, I would check the Web Code for a bug.

    A TRACE WILL help you find out if there is a call to the INSERT when you expect it to happen.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • August 7, 2008 at 2:24 pm

    #854757

    Valentin Petkov (8/7/2008)

    ...it is SIMPLE INSERT INTO then SELECT @INDENTITY as ID ....but NULL

    You have got to get off the track you are on or it will be very difficult to find the answer. If you execute an Insert statement, and a row is inserted into the table, @@Identity WILL return the correct value.

    You are more willing to think that there is something wrong with @@Identity than to think there may be something wrong with your code.

    Get this straight right now: the problem is with your code. It is not doing (or not always doing) what you think it should be doing. Once you accept that fact, you will already be much closer to finding the problem.

    I have asked a simple question: is there an Instead of trigger on the table? Another comes to mind: Is the "Insert/Select @@Identity" sequence being executed in the database (a stored procedure perhaps) or in the application? If possible, show us this code.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • August 8, 2008 at 7:18 am

    #855135

    Like a number of previous posters said, @@IDENTITY is not a good thing to use...

    I normally use ident_current. Here's an example...

    CREATE TABLE Temp1 (ID int IDENTITY(1,1) NOT NULL, someChar Varchar(4) not null)

    insert into temp1 (someChar) values ('junk')

    select ident_current ('temp1')

  • August 9, 2008 at 3:41 am

    #855651

    Have you tried using SCOPE_IDENTITY() function yet?

    If you have many users running inserts against same table, @@IDENTITY can returns any users last ID (regardless of scope).

    N 56°04'39.16"
    E 12°55'05.25"

  • August 9, 2008 at 3:46 am

    #855653

    http://msdn.microsoft.com/en-us/library/ms187342.aspx

    Do you have some transactions around the insert statement?

    N 56°04'39.16"
    E 12°55'05.25"

  • August 9, 2008 at 3:54 am

    #855656

    Here is another reason causing @@IDENTITY returning "wrong" value

    declare @abc table (i int identity(100,1), j int)

    insert @abc values (1)

    SELECT @@IDENTITY AS [WhatID?]

    declare @mytab table (id int identity(1, 1), myfield tinyint)

    INSERT INTO @myTab (myField) VALUES(123456)

    SELECT @@IDENTITY AS [WhatID?]

    N 56°04'39.16"
    E 12°55'05.25"

  • August 9, 2008 at 10:48 am

    #855695

    Heh... even SCOPE_IDENTITY() doesn't fix that problem. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 9, 2008 at 11:33 am

    #855701

    Here's an actual example of why using @@IDENTITY is risky:

    CREATE TABLE Test

    ( TestID INT IDENTITY(1, 1)

    , SomeText VARCHAR(50)

    )

    GO

    CREATE TABLE TestLog

    ( LogID INT IDENTITY(100, 1)

    , TestID INT

    , LogText VARCHAR(50)

    )

    GO

    CREATE TRIGGER Test_ITrig

    ON dbo.Test

    AFTER INSERT

    AS

    BEGIN

    SET NOCOUNT ON;

    INSERT INTO TestLog

    ( TestID, LogText )

    SELECT

    TestID, SomeText

    FROM inserted

    END

    GO

    -- An insert

    INSERT INTO Test

    ( SomeText )

    SELECT

    'Some Text'

    SELECT

    @@IDENTITY AS AtATIdentity

    , SCOPE_IDENTITY() AS ScopeIdentity

    -- AtAtIdentity = 100 - The identity from TestLog in the Trigger

    -- ScopeIdentity = 1 - The identity from the Insert statement

    Todd Fifield

  • August 9, 2008 at 12:59 pm

    #855709

    Jeff Moden (8/9/2008)

    Heh... even SCOPE_IDENTITY() doesn't fix that problem. 🙂

    Well put.

    I just demonstrated that we know to little about OP environment and code to make helpful suggestions.

    N 56°04'39.16"
    E 12°55'05.25"

  • August 9, 2008 at 5:19 pm

    #855715

    Peter, are you, by any chance, going to PASS this year? Sure would like to me you in person, someday.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 10, 2008 at 2:11 am

    #855738

    I thought PASS in Europe happened in April?

    http://www.european-pass-conference.com/[/url]

    Are there more PASS conferences?

    Oh, I see now that you are one of those "yankees" 😀

    November 18-21, 2008

    Washington State Convention & Trade Center, Seattle WA.

    Well, not very likely to happen this year (unless I get sponsored 😛 ).

    But... Me and my girlfriend have plans to hike (buy a car and drive anti-clockwise) around the states not so far in the future.

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 13 posts - 16 through 27 (of 27 total)

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