October 13, 2002 at 11:17 pm
Hi!
Currently I am using SQL Server 7 & facing a piculiar problem while returning the identity sheed value inside a stored procedure.
The problem is as follows :
I have a table having an Identity Column. Through stored procedure I am
inserting the data into it & while the record is getting created I want to
use the Identity seed generated. The procedure was working fine for some
time. But after some days I found that even though it creates the record
with the identity sheet, it is not returning the lat identity value i.e.
through function @@identity.
Is have checked the database, tables etc through DBCC & also done Update
statistics. But the same problem persists.
When I am using the same procedure on the same table of a different database
it is working fine.
Can you please help me out in solving this problem.
Regards.
Tapks
TapKs
TapKs
October 14, 2002 at 1:18 am
It sounds like there may be a trigger on the table that does an insert in another table, thereby upsetting the identity value. Checked that?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
October 14, 2002 at 1:27 am
Thanks Chris!
Yes there is a trigger which inserts the records to anothr table.
But this procedure is creating the record in the table as well as the recoeds in the other table with the identity sheed generated. But why it is not returning the identity sheed generated ?
Can u please help me out.
TapKs
TapKs
October 14, 2002 at 2:05 am
@@identity returns the latest created identity value in any table, for the current user session. This means that if your procedure inserts a row into table A, and there is a trigger on table A that does an insert in table B when a row is inserted to table A, then the latest INSERT will be the one on table B, and @@identity will return the identity value for that INSERT, or null if there is no identity column on table B.
If you use SCOPE_IDENTITY() instead of @@identity you should be alright, it is limited to the latest created identity value for the current user session, but only in this scope.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
October 14, 2002 at 2:21 am
Thanks Chris for giving me the explanation. But how to use SCOPE_IDENTITY() instead of @@identity? Can u give me some examples.
Regds.
TapKs
TapKs
October 14, 2002 at 2:58 am
Just noticed that you are using SQL7, and SCOPE_IDENTITY is new for SQL2K. As far as I know, in SQL7 there is no similar function, so you have to rethink your situation. Is the trigger necessary? Can your stored procedure do the insert that the trigger does, thereby giving you the possibility to check @@identity before the second insert occurs?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
October 14, 2002 at 3:03 am
Dear Chris!
Thank you very much the valuable info. I will do it in SP itself & will check in SQL2K.
Thank you once again for ur valuable suggestion.
TapKs
TapKs
TapKs
October 14, 2002 at 1:02 pm
You need to wrap the following code around your trigger code
Put this at the start
declare @idValue char(10)
select @idvalue = cast(@@identity as char)
And this at the end
if @idvalue is not null execute ('select identity(int, ' + @idvalue + ', 1) as ro into #IdentityTemp')
This will make sure that the value of @@identity is the same at the end of the trigger as it is at the end.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply