Insert failed with no error - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert failed with no error

Author

Topic

level88
Starting Member

5 Posts

Posted - 2009-03-06 : 20:10:53

Hi, I have encountered a weird error. It seems that an insert statement didn't execute and the process didn't error out and continued on. Here's the logic:

step 1: (insert into table, values xxx)
step 2: increment counter

The problem is that the procedure ran 5 times and at step 2, the counter was incremented every time but the insert didn't happen at step 1. It seems to happen about once in every 5000 executions of the procedure. Is there some logical explanation for this? If the insert fails at step 1, shouldn't it error out and end right there?

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-06 : 21:19:52

Does the table have any kind of triggers? That could be rolling back the data?

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-06 : 21:48:35

You'll need to post the code in order for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

level88
Starting Member

5 Posts

Posted - 2009-03-06 : 21:49:31

quote:
Originally posted by guptam

Does the table have any kind of triggers? That could be rolling back the data?

Hi, thanks for the reply. I didn't set up any triggers on the table. The problem just doesn't make sense.

level88
Starting Member

5 Posts

Posted - 2009-03-06 : 21:53:26

quote:
Originally posted by tkizer

You'll need to post the code in order for us to help.

Ok, here it is:

BEGIN TRAN

INSERT INTO Feedback
(fdbOAid,fdbText,fdbRating,fdbRaterID,fdbUserID,fdbDate,fdbBS,fdbDateDiff)
VALUES
(@tmpOrderID,@tmpText,@tmpRating,@tmpUserID,@tmpCommentedOnID,@tmpDate,@tmpBS,@tmpDateDiff)

IF @tmpBS = 'B'
UPDATE Orders SET OrderSellerFdb = @tmpRating + 1 WHERE OrderID = @tmpOrderID
ELSE
UPDATE Orders SET OrderBuyerFdb = @tmpRating + 1 WHERE OrderID = @tmpOrderID

IF @tmpRating = 0
UPDATE Users SET User_Feedback = User_Feedback + 1 WHERE UserID = @tmpCommentedOnID
IF @tmpRating = 2
UPDATE Users SET User_Feedback = User_Feedback - 1 WHERE UserID = @tmpCommentedOnID

IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN

The insert didn't happen but the UPDATE Users SET User_Feedback = User_Feedback + 1 did. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-06 : 22:08:27

Your transaction isn't properly coded. You should be checking @@ERROR after every DML statement (INSERT/UPDATE/DELETE).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources