insert row in a table inside another database and get identity back

Question

insert row in a table inside another database and get identity back

Andrei Dragotoniu

Grasshopper

Points: 12
More actions
November 13, 2008 at 10:00 am

#201341

Hi guys,
here's a question that needs an answer.
I have 2 sql 2005 databases. Inside the first one I have an sp which inserts a row into a table inside the second.
I want to get the identity field for that but it always comes back as -1.
I am using this code to get the identity back :
DECLARE @InsertedId int
INSERT INTO [db2].dbo.WP ( test1, test2 )
values ( @test1, @test2)
SET @InsertedId = @@IDENTITY
RETURN @InsertedId
The code works, the record is added with no problem, but as i said, it returns -1.
I know it will work if i move the sp inside the second db, but that's one thing i don't want to do.
Is there any way i can get this identity back ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I'd look at using the OUTPUT clause with the insert. You may be able to get the identity value assigned from the INSERTED values.

Haven't tried it, but it is worth a shot. Look it up in BOL.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

OUTPUT works wonderfully for that purpose, although an example in books online used to suggest the opposite. It's very easy to implement. Check out this example:

---------------------------------------------------------------------------

declare @sample TABLE

( [ID] [uniqueidentifier] NOT NULL primary key DEFAULT (newid())

,[Filler] [varchar](100) NULL

)

declare @output Table (outID [uniqueidentifier])

insert into @sample (Filler)

OUTPUT inserted.ID into @output

select 'apples' union all

select 'bananas' union all

select 'cumquats'

select * from @sample

select * from @output

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Looks like this may be the way to go. I amy have to look at the output clause in a little more depth for other uses in my environment as well.

Andrei Dragotoniu Grasshopper Points: 12 More actions · Answer 4

ok, problem solved

I changed the code , instead of doing a return on the identity I am now doing a select on the identity variable.

Therefore the result is now a dataset with the right value in it.

I only needed to update my DAL so that it reads the value as a string and then cast it to an integer and finally return that, which means my BAL and the rest of the code can stay the same.

I still don't understand why return did not work though.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

It works fine on my 2005 development box. What does your exec statement look like in the other db you run this proc from?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills