January 10, 2014 at 9:56 am
I have a Stored Proc that has been working good and never really had Identity issues.
The logic is used was I set @trans_id = SCOPE_IDENTITY(). I use that ID to tie everything back to the parent table. I put this logic after the INSERT into the parent table to pull that IDENTITY. Then it eventually error out. It is just very strange for this to happen because if i do the process outside the stored proc, it works like I want it too. I do it through the stored proc, it didn't like it.
I did some test on IDENTITY.
select SCOPE_IDENTITY() = 141
select @@IDENTITY = 141
select IDENT_CURRENT ('my table') = 1
I changed my logic to use the IDENT_CURRENT because i had to get this working asap but as I research this, it says I should be using SCOPE_IDENTITY() to set ID's which is what i was doing until it wouldn't work.
I even reset the identity (reseed,0) and SCOPE_IDENTITY, and @@IDENTITY wouldn't reset, they pulled the values above.
Anybody seen something like this before?
January 10, 2014 at 10:02 am
cdl_9009 (1/10/2014)
I have a Stored Proc that has been working good and never really had Identity issues.The logic is used was I set @trans_id = SCOPE_IDENTITY(). I use that ID to tie everything back to the parent table. I put this logic after the INSERT into the parent table to pull that IDENTITY. Then it eventually error out. It is just very strange for this to happen because if i do the process outside the stored proc, it works like I want it too. I do it through the stored proc, it didn't like it.
I did some test on IDENTITY.
select SCOPE_IDENTITY() = 141
select @@IDENTITY = 141
select IDENT_CURRENT ('my table') = 1
I changed my logic to use the IDENT_CURRENT because i had to get this working asap but as I research this, it says I should be using SCOPE_IDENTITY() to set ID's which is what i was doing until it wouldn't work.
I even reset the identity (reseed,0) and SCOPE_IDENTITY, and @@IDENTITY wouldn't reset, they pulled the values above.
Anybody seen something like this before?
Reseeding the identity will not change the results of SCOPE_IDENTITY or @@IDENTITY. You might want to read up about what those do.
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/[/url]
Given the nature of what you are describing you might want to take a look at the OUTPUT clause. It would very possibly be easier to use in your situation, although without more details it is hard to know for sure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply