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

    Unanswered: bug with ident_current

    Hello,
    I have tried developped a stored proc that is using the ident_current('tablename') function to get the last Identity ID.

    It was working fine. I drop and recreate the table (which reset the ID) and found that the ident_current said that the last Identity Id used was 1. The problem was that the table was empty. I insert a record and try it again. It said again that the last Identity ID was 1. After the insertion if the first record, everything is fine...

    I would like to know if you know a way get 0 when the table is empty and 1 when there is only one identity id that have been used.

    Thank

    Felix Pageau
    fpageau@str.ca

    You can test the functionnality with the following code:

    create table identCurrentBugExeample(IDnumber int IDENTITY(1,1), aChar char(1))
    Declare @count as int
    set @count = (select count(IDnumber) from identCurrentBugExeample)

    --Print the number of record in the table
    print @count

    --Supposed to print 0 because there isn't any identity values that has been used
    print ident_current('identCurrentBugExeample')
    insert identCurrentBugExeample(aChar) values('a')

    --Print 1 because the last identity used is 1
    print ident_current('identCurrentBugExeample')
    insert identCurrentBugExeample(aChar) values('a')

    --Print 2 because the last identity used is 2
    print ident_current('identCurrentBugExeample')
    drop table identCurrentBugExeample

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm sorry...is there a question here?
    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.

  3. #3
    Join Date
    Jan 2004
    Posts
    27
    Yes,

    is there an option I can use with the fonction to know if there has been a record inserted. I mean, I would like to know if I can get "0" instead of 1 when there hasn't been any record inserted.

    It is not because the count of the table is 0 that hasn't been any record inserted and deleted (and the next identity won't be 1...)

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you looking for SCOPE_IDENTITY?
    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.

  5. #5
    Join Date
    Jan 2004
    Posts
    27
    i'm not looking for SCOPE_IDENTITY. I need the to know what was the last value inserted into the IDENTITY column.

    If there wasn't any record ever added, I want something like NULL or -1 to show that the table is still intact.

    When there was at least one record added, I want the value of IDENTITY coloumn of the last record

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ok...I admit it...I have NO idea what you're getting at...

    anything like...

    Code:
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), col2 char(1))
    GO
    
    SELECT MAX(Col1) FROM myTable99
    INSERT INTO myTable99(Col2) SELECT 'A'
    SELECT MAX(Col1) FROM myTable99
    
    DROP TABLE myTable99
    GO
    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.

  7. #7
    Join Date
    Jan 2004
    Posts
    27
    Hello,
    the code you have provided works great if the record hasn't been deleted.

    When you delete a record (or all the records), the next time you insert one, the value of the IDENTITY column will be x+1

    Like:
    ID Name GUID
    1 Felix AAAAAjsdhksdkjk
    2 Bob AAAASDFfsdjkdsj
    ... ...
    7 Alycia ASDADnenefnefn
    8 Roger AAAAAdfshdsfhjsdf

    If I delete the record #8, and I insert a new one it will look like :
    ID Name GUID
    1 Felix AAAAAjsdhksdkjk
    ....
    7 Alycia ASDADnenefnefn
    9 NewName AAAACCCCddddd

    Then I delete all the records
    ID Name GUID


    Now I must know what is the ID to add a new one (I need to know that before adding it because I'm saving some parts of the record on a Exchnage 2000 linked server (where I must put the ID), get the Global Unique ID of the record in Exchange 2k after saving and putting the guID back in the SQL server in the right record.)

    If I do ident_current('tablename') and the value returned is 1. How can I know if the new record I will put in the table will have the ID 1 or 2 if when I do ident_current and there hasn't been anyrecord in the table I got 1 and when there is (or there was) only one record, I also got 1 ???

    Do you have a clue on how to do this ?

  8. #8
    Join Date
    Jan 2004
    Posts
    27

    More details...

    By the way, I can't get the GUID (global unique Identifier) directly from Exchange but only while I'm using the linked server to exchange from SQL Server. But I can get the ID from the exchange server while using the linked server and I can set it directly in Exchange...

    I need a cross reference between the record (object because it is a OO databse) and the record in SQL server.

    That is why I must get the next ID.
    Create a dummy record with no informations
    Send the ID back to exchange
    Save the object in exchange with the ID
    Send the GUID of exchange to SQL
    Put the GUID in the right "dummy" record with the ID
    Get the needed informations from the linked server

    And that should work (that is working when I already have a record in the table in sql prior to adding the dummy record. But when I haven't put any, I got a bad ID for the first record that I had. Every other records added later will work fine).

    It is why I need to know what is the last value of the IDENTITY column and get 0 when there wasn't any ever added to the table. But the function is giving 1 when there wasn't any record added and is also giving 1 when only one record has been added.

    Thank a lot

  9. #9
    Join Date
    Jan 2004
    Posts
    1

    Re: bug with ident_current

    I think you will have to set Identity_SID starts from 0 then only..you will get '0' for Select IDENT_CURRENT('table1') when no reords inserted....

    try playing with Identity_SID ...you will get the correct value...

    see below For SQL help --------
    IDENT_CURRENT
    Returns the last identity value generated for a specified table in any session and any scope.

    Syntax
    IDENT_CURRENT('table_name')

    Arguments
    table_name

    Is the name of the table whose identity value will be returned. table_name is varchar, with no default.

    Return Types
    sql_variant

    Remarks
    IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

    IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.


    @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.


    SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Posting Permissions

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