scope_identity

• keradinides

  Mr or Mrs. 500

  Points: 557

  More actions

  May 7, 2004 at 12:05 am

  #66006

  Hi

  I have an app in Gupta (Centura) working throu ODBC to SQL2000.

  Currently works with the ident_current ('xxx') function to retrieve the last ID inserted to table 'xxx'.

  I want to change it to scope_identity(), but it returns null.

  The @@identity also works fine.

  Any idea why this happening?

  Thanks a lot

• Guy Haskell

  Ten Centuries

  Points: 1212

  More actions

  May 11, 2004 at 4:58 pm

  #506058

  I've never experienced this problem, although I connect through OLEDB rather than ODBC.

  The approach I tend to take is to either put the insert into a stored procedure eg:

  create procedure spInsertData @data nvarchar(20) as

  declare @return int

  insert into mytable(data) values (@Data)

  select @return=scope_identity()

  return @return

  Or alternatively (but technically slower) just execute the insert and select scope_identity() as a single batch

• keradinides

  Mr or Mrs. 500

  Points: 557

  More actions

  May 12, 2004 at 12:24 am

  #506074

  I think that throu OLEDB there would be no problem to retrieve scope_identity().

  The store procedure would be a solution but it will be my last choice, because it has to be changed in about 10 places in my code.

  Thanks for your ideas.