April 4, 2008 at 7:09 am
Return null value from scope_identiy from differnt box
declare @sequenceId int
insert into box1.db.dbo.proj(a) value (1)
select @sequenceId = scope_identity()
--table proj has identity column
when i run the query from box2
it will return null value
why it's not return actual valy?
April 4, 2008 at 8:25 am
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
(
)
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply