scope_identity insert problem 2

[slatet]

We're having some difficulty with a scope_identity var that gets returned from an insert. When printed, it is correct, but when we try to use it for another insert, nothing is ever inserted into the table.

Here is the first insert:
Insert into Master_Node (a bunch of cols)
Values (a bunch of vars)
SELECT SCOPE_IDENTITY() AS @pINodeID


print @pINodeID - this prints out the correct values

insert into Links (Link_Name, Parent_INode_ID) Values (@Links, @pINodeID) - when this inserts, the "Parent_INode_ID" value is ,null>

anyone know why? This is our last bug to completing the procedure.

Thanks in advance.

 

[DotNetGnat]

try this:

SELECT ident_current() AS @pINodeID

-DNG

 

[DotNetGnat]

this is the difference...i am still trying to understand myself...

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.


@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.


SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

-DNG

 

[slatet]

I get an error that says, 'The ident_current function requires 1 arguments

 

[DotNetGnat]

SELECT ident_current('Master_Node') AS @pINodeID

-DNG

 

[slatet]

Tried it, same problem, not inserting into the Links table.

 

[DotNetGnat]

did you try...

SELECT @@Identity AS @pINodeID

-DNG

 

[vongrunt]

Any trigger on Links table?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[slatet]

Just tried @@Identity - same problem

There are no triggers in the database at all.

 

[SQLBill]

Do you have a GO in between those statements? Do you ever leave the 'scope' of the first statement or are the two INSERTs done consecutively.

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

Just curious... isn't

SELECT SCOPE_IDENTITY() AS @pINodeID

...syntactically incorrect? Did you get any error?

Normally I would use/expect this:

SET @pINodeID = SCOPE_IDENTITY()

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DotNetGnat]

i tried and this is syntactically correct...

SELECT SCOPE_IDENTITY() AS @pINodeID

-DNG

 

[slatet]

There are no "GO" statements in there anywhere. Do I need them? The three lines that I posted are in exact order as to the way I'm doing them, so I wouldn't think I was leaving the scope.


That line is syntactically wrong. I fixed it so that is wasn't. I will try the line you suggest and see if that makes a difference.

 

[slatet]

Guess what,

SET @pINodeID = SCOPE_IDENTITY()

did it! Thanks for all the help!

 

[SQLSister]

DotNetGnat - you should never ever use @@identity. If someone later puts a trigger on your table that inserts a record into another table with an identity field in it, that is the value it will return. @@identity is a prescription for data integrity problems.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[DotNetGnat]

SQLSister,

thanks for the info. I will avoid using @@Identity.

-DNG

 

[SQLSister]

THe worst part is that often the table with the trigger on it is newer than the other table and thus it returns values you already have, so it links the data to the wrong record in the parent table and no errors occur. You can go a long time before a user complains about how messed up the data is before anyone notices. This type of data integrity problem is nearly impossible to fix.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[DotNetGnat]

and SQLSister...you need to change the tsunami link in your signature to Katrina Relief...

-DNG

 

[SQLSister]

Thank you. I will do that.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[DotNetGnat]

Thank you. Thank you for the info on @@Identity.

-DNG