April 20, 2009 at 5:11 am
i've got a stored procedure that sits on an sql2008 server.
amongst other things, this stored procedure runs an insert statement to a table on an sql2000 server. this is done via a linked server.
my problem is that on using the @@IDENTITY it comes back as NULL. it also seems to come back as NULL when i try SCOPE_IDENTITY and IDENT_CURRENT.
SET @DocID = @@IDENTITY
set @DocID2 = scope_identity()
set @DocID3 = ident_current(server.database.owner.table)
from other articles i've read it says that @@IDENTITY doesn't work across servers which is fair enough but i'm wondering if i'm using the other functions wrong or if they too dont work across servers.
thanks
David
April 20, 2009 at 11:27 am
I don't think any of the Identity Functions work across linked servers. If I were doing this and had access to the 2000 server, I'd create a stored procedure (if possible) that uses and output parameter to return the identity value.
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
April 21, 2009 at 4:22 am
thanks for the reply. i'm now half way through the process of testing what i've done.
i've created a stored procedure on the 2000 server to insert to the table that resides on that server.
it has a return value of the @@IDENTITY
on the 2008 server i've written the code that calls it and fill the local variable with the return value.
all looks good so far.
David
April 21, 2009 at 5:56 am
Glad you could do that. I'd recommend using SCOPE_IDENTITY() instead of @@Identity though. @@Identity returns the last Identity value created in the session so if there are any triggers, say for logging, that insert into a table with an Identity column you will get that Identity value. SCOPE_IDENTITY() will return the Identity value for the table you are inserting into within the stored procedure.
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
April 21, 2009 at 6:51 am
just read up on the scope_identiy on msdn. i suppose because i dont have any triggers in my stored procedure i'll be OK?
i dont want to do a blanked changing of all @@IDentity for the scope_identity either because it may be the case that for what i'm doing @@identity will be fine?
so is it just 'horses for courses'? or should i really be using scope_identity as much as possible?
thanks
April 21, 2009 at 6:57 am
On 2000 I recommend using SCOPE_IDENTITY pretty much exclusively. Could someone else create a trigger in the DB without your knowledge? What happens if you leave and someone creates a trigger?
In 2005+ I'd recommend using the Output Clause because of a bug found in SCOPE_IDENTITY().
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
April 21, 2009 at 7:55 am
to be honest my manager isn't a big fan of triggers so we dont tend to use them as a matter of course.
for example, if we want a row to be entered into an audit table with every insert run then we would run the audit SP within the insert stored procedure, rather than using a trigger.
i suppose its just a way of making sure things aren't hidden away in triggers.
but its certainly something to think about if that ever changes.
April 21, 2009 at 1:28 pm
Also - you really should not be using the return value from a stored procedure for anything other than the status. I would recommend using an output parameter for the stored procedure and returning the value that way.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2009 at 2:15 am
OK. I think i've unecessarily doubled up in my SP. i've got an output parameter and i'm also using a return statement :hehe:
create procedure dbo.sp1 (param1 int,param2 int, paramOutput = 0 output)
insert into ...
values....
set paramOutput = @@IDENTITY
Return paramOutput
but i'm only using the output parameter. thanks for your help 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply