Results 1 to 8 of 8

Thread: Getting recenlty inserted value from a table

  1. #1
    Join Date
    Oct 2007
    Posts
    8

    Getting recenlty inserted value from a table

    Hi all,

    I am inserting one row from sp which is at one database into table on other database.I want to retrieve one columns value which will be recently inserted .
    I was trying it using output parameter and @@Identity in sp.
    But hardly any success.I guess we can not get @@Identity values from other server.

    Can anybody suggest better way to do it

    Thanks

    Kedar

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Not cross server. If the table has identity column, can you check max value of the column?

  3. #3
    Join Date
    Oct 2007
    Posts
    8
    Thank you rmiao for your time.
    I cant go for max as my table is going to get updated from many session at a time.Even i tried using max but sometimes it is giving me wrong values.

    But i got the answer about how can we get identity values across database.

    We can use IDENT_CURRENT('databasename.ownername.tablename').

    This is giving recently inserted identity value in a table across the database as well.

    Thank you .

    Kedar
    Last edited by kedarwaghmode; 11-22-2007 at 03:41 AM.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Tried SCOPE_IDENTITY?

  5. #5
    Join Date
    Oct 2007
    Posts
    8
    yes i did .......But it is also working for cross server.......

    Do you think Openquery or Openwrowset will be of any use.....If yes can you give sample query.


    Kedar

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    How did you get those cross server?

  7. #7
    Join Date
    Oct 2007
    Posts
    8
    Ohhh i m sorry ,
    I forgot to add 'not' before that also ,I am sorry

    I wanted say SCOPE_IDENTITY is NOT working for cross server

    Thanks!!

    Kedar

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    You need run 'select SCOPE_IDENTITY' in same session with insertion.

Posting Permissions

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