Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sengoku
Starting Member
29 Posts |
 Posted - 2003-04-29 : 05:33:20
|
| hey there everyone!i'm hoping someone can help with this, because i don't really know what else to try...we've got a big database application/game and it all seems to run fine, except for recently i've noticed very strange behaviour.someone will try and create a new record, and the procedure looks something like this :insert into table (blah blah) values (blah blah)select @@identity as newid from tableand all this is encapsulated in a transaction.now, the problem is this. the @@identity returns the value correctly, as in the next id number of that table... but the data just isn't inserted!!! there's no error at all, although that table is under fairly heavy use, and occasionally deadlocks on select queries... i dunno if this could have anything to do with it?anyway, originally this code was on an ASP page on a seperate computer to the database server, and i thought it must be something to do with that, so i've re-coded it as a stored procedure, which i'm calling with an ADODB.Command object, it all seems to work fine (and quite a bit faster to boot :) ) but... still, the same problem occurs sometimes...there may be something really easy that i'm missing, and i hope this is the case because otherwise i'm stumped :(any ideas from you bright people? or is there any other info i can give that'll help? |
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2003-04-29 : 06:23:38
|
| have you tried monitoring the table with profilier or anything ?Sounds like the transaction may be getting rolled back which is perhaps why it gives you the @@identity but doesn't insert the record.Try monitoring the table.====Paul |
 |
|
|
sengoku
Starting Member
29 Posts |
Posted - 2003-04-29 : 06:45:59
|
| hi there! and thanks for the quick reply! :)i'm getting profiler on the case today, which traces do you think would help me?and, i guess that transaction being rolled back could cause it, but the weird thing is it doesn't error at all (either when it was in ASP or in a stored proc)... is there an @@insertstatus returned as well that i could test?oh, and if it's in a stored proc with the insert statement JUST before the select @@identity one, do i need to transact the whole lot? is there any chance of 2 concurrent stored procs running at once, or does sql automatically queue them up? |
|
|
|
|
|
|
|
|
sql inserting problem, i'm stumped!