August 16, 2006 at 11:51 am
If I have 2 insert statements, one inserting into a Users table of DatabaseA and another Inserting into a Users table of DatabaseB on the same server, how can I tell the Scope_Identity to scope the returned identity based off the second insert, not the first?=
August 16, 2006 at 11:57 am
Did you read Books On Line ?
Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.
If you want SCOPE_IDENTITY() for the 2nd INSERT, then simply call SCOPE_IDENTITY() after the 2nd INSERT.
August 16, 2006 at 11:59 am
another solution would be to use IDENT_CURRENT('table_name')
Chuck
August 16, 2006 at 12:04 pm
Why would you use IDENT_CURRENT() ?
Returns the last identity value generated for a specified table in any session and any scope.
So, another session on your SQL Server could insert a new row a couple of milliseconds after yours, and you'd have the wrong identity value in your batch. Typically you want to grab an identity in order to insert subsequent records with the correct foreign key, so you have potential to royally screw up the integrity of your data using this function.
August 16, 2006 at 12:04 pm
That's exactly what I did, but it's still taking the identity of the first insert!!!
INSERT INTO Users (AspNetUserID, Active, CreateUserID) VALUES (@NewUserId, 1, 0)
IF (NOT EXISTS (SELECT UserID from InstantForum414.dbo.InstantASP_Users
WHERE InstantForum414.dbo.InstantASP_Users.EmailAddress = @Email
OR InstantForum414.dbo.InstantASP_Users.Username = @UserName))
BEGIN
INSERT INTO InstantForum414.dbo.InstantASP_Users
(EmailAddress, Username, Password)
VALUES
(@Email, @UserName, @Password)
SET @NewForumUserID = SCOPE_IDENTITY()
UPDATE Users SET InstantASP_UserID = @NewForumUserID
WHERE Users.UserID = @NewUserID
END
August 16, 2006 at 12:06 pm
>>>IDENT_CURRENT
I know that, I don't want this though, that's too risky. I want only in this scope. There could be another process that performs an insert on that table giving me a sh## value back in my stored proc
August 16, 2006 at 12:06 pm
>>That's exactly what I did, but it's still taking the identity of the first insert!!!
Does the table being inserted to actually have an identity column ? Post the DDL.
August 16, 2006 at 12:07 pm
Refresh, i had posted the wrong code, now look at what I originally had. I tried putting an Identity_scope right after the second insert and it still took the first.
August 16, 2006 at 12:10 pm
Yes, both tables have an identity
Users.UserID - identity column of DB1.Users table
InstantASP_Users.UserID - identity column of DB2.InstantASP_Users table
both databases are on the same server.
August 16, 2006 at 12:15 pm
The 2nd INSERT is happening inside a conditional IF block.
Are you certain the 2nd INSERT is actually inserting ? Have you added PRINT debugging to verify that everything inside the IF is actually being executed ?
August 16, 2006 at 3:40 pm
You prlby need to initialize the @NewForumUserID to NULL before the insert.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
August 16, 2006 at 7:34 pm
I think there is no problem actually after testing more. I think that it is returning the identity of the second insert since the Scope_Identity was placed after the second insert.
I was fooled because I was getting a datatype error saying it couldn't convert the ID it was returning to another datatype in another one of my statements.
But actually it was a problem with another update statement. So anyway, it's fine.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply