Insert failing silently, sometimes 2

Do you have any triggers on the table? Is it possible that the trigger(s) could be causing your insert to fail?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

No, there are no triggers in the database.

I also ran a profiler while the users were using the application, and saw the calls being made. The data being sent to the proc seemed perfectly normal and well formatted.

Thanks,
Sheila

If there is another transaction opened and that transaction is rollbacked, then the whole transaction is rallbacked.

Also, if this is just a single insert, why do you need to wrap it in transaction?

PluralSight Learning Library

Hi Markos,

>>If there is another transaction opened and that transaction is rollbacked...

I didn't understand your comment. Do you mean some other stored proc is affecting this proc?

>>Also,...why do you need to wrap it in transaction?
I don't think I need to. This is old code, with this strange problem, given to me to resolve the problem. I have removed those explicit transactions. The Autotansaction is on, and should work.

What I am wondering is: Could the explicit transaction have been the cause of the problem? I don't want to wait for a month to find that the problem still exists.

Thanks,
Sheila

I meant, if this SP is called from another SP, for example, and that SP has its own transaction opened, then if that outer SP does a rollback, the record will not be inserted.

PluralSight Learning Library

No, there is no nested transaction involved in this case.

The C# code calling this proc uses this call:
command.ExecuteNonQuery();

Thanks again,
Sheila

I also ran a profiler while the users were using the application, and saw the calls being made.

Click to expand...

Did you catch a call that failed?

SimonSellick,

No Unfortunately the failure occurs silently, and seldom.

I made sure that the parameters being passed are formatted properly so the DB won't reject them. e.g. the float and date params are in format that the DB expects.

Thanks,
Sheila

How sure are you that the call is really happening? Silent failure doesn't sound like Microsoft, unless you have specifically told the db to ignore particular types of error.

Does your call depend on any implicit data type conversions? They sometimes have unexpected results. Are null parameters correctly catered for?

Can you add some code around the insert to check that it is actually being called when it fails? Ideally, a log of the call parameters, written by the C# caller; and a corresponding one written by SQL Server before and after the insert, so that you could wait for a failure then see the actual call made in the C# log and what happened to it in the SQL Server log. There would obviously be a performance hit.

Simon

Just came across this article

that may shed some light on this problem (I only glanced through, but the top portion seems to address this problem).

PluralSight Learning Library

Thanks, markros. This article is really good at making me think.

Thanks, Simon. I have now added C# code to log the state, and am waiting for the culprit.

I'll update this thread when I settle this issue.

Thanks again,
Sheila

What is the status of this problem?

BTW, I'm re-reading this article now and I see that Alex talks about silent lost updates under high concurrency here

(close to the end of the article):

Listing 23: A loop that invokes UpdateWebPageStats to increment NumVisits for one and the same row 10,000 times in a loop

PluralSight Learning Library