Return null value from scope_identiy from differnt box

• Jack Corbett

  SSC Guru

  Points: 184395

  More actions

  April 4, 2008 at 8:25 am

  #798471

  YOu executing the insert on the remote server not the local server so there is not an identity value being created in the scope of the local server. If you really need this to work you should program the insert as a stored procedure on the remote server with an output parameter that returns the newly created Identity value. Like this:

  -- On Box1 (the remote server in your example

  Create Procedure project_ins

  (

  @a Int,

  @new_project Int = Null Output

  )

  As

  Insert Into proj

  (

  a

  )

  Values

  (

  @a

  )

  Set @new_project = Scope_Identity()

  Return

  Grant Exec On project_ins To linked_server_user

  -- on box2 (the local server)

  Declare @new_project Int

  Exec box1.database.dbo.project_ins @a= 1, @new_project = @new_project Output

  Select @new_project

  This should return the value you want.

  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