Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    9

    Unanswered: Rollback Identity problem

    Hi,

    I am having a problem with rollbacks. Each time my transaction fails, the identity number increases even though I have rolled it back.

    I was considering calling DBCC CheckIdent() after it rolls back but that can only be used by the administrator...

    Anyone have any ideas?

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36
    Use scope_identity() and while inserting into this table, set identity insert on at the beginning and off at the end.

  3. #3
    Join Date
    Dec 2003
    Posts
    9
    Originally posted by suresh_m_kumar
    Use scope_identity() and while inserting into this table, set identity insert on at the beginning and off at the end.
    Thankyou for your reply. That's a good idea, but I think you may have meant IDENT_CURRENT for inserting the value?

    Does the SET IDENTITY_INSERT ON require adminstrator privelages?

  4. #4
    Join Date
    Dec 2003
    Posts
    9
    I tried

    begin transaction

    set insert_identity('table1') on

    insert table1 (identity_column....)

    values (ident_current('table1'),......)

    set insert_identity('table1') off

    rollback transaction

    But this will not work as you are required to be the Administrator of this table to use the 'SET' command (I am accessing via an ASP page, without admin privelages).

    Does anyone have any other ideas?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The problems with IDENTITY...

    Are you trying to rely on it to be a "sequence" number?

    In 1 window

    Code:
    DECLARE @x int, @y int
    SELECT @x = 0, @y = 0
    WHILE @x < 10
    	BEGIN
    		WHILE @y < 100000
    			BEGIN
    				SELECT @y = @y + 1
    			END
    	SELECT @y = 0
    	SELECT GetDate() AS SysTime, 'Preparing for Row INSERT'
    	BEGIN TRAN
    	INSERT INTO myTable99 (Col2) SELECT 'x'
    	COMMIT TRAN
    	SELECT @x = @x + 1
    	END
    
    SELECT * FROM myTable99
    In another window

    Code:
    USE Northwind
    GO
    
    --CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1))
    --GO
    
    DECLARE @x int, @y int
    SELECT @x = 0, @y = 0
    WHILE @x < 10
    	BEGIN
    		WHILE @y < 100000
    			BEGIN
    				SELECT @y = @y + 1
    			END
    	SELECT @y = 0
    	SELECT GetDate() AS SysTime, 'Preparing for Row INSERT'
    	BEGIN TRAN
    	INSERT INTO myTable99 (Col2) SELECT 'x'
    	ROLLBACK TRAN
    	SELECT @x = @x + 1
    	END
    
    --DROP TABLE myTable99
    --GO
    But then you already know about this problem...why is it a problem for you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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