December 30, 2008 at 1:03 pm
To begin, the word Asset used below refers to a data entity spread across several tables. The details of said asset aren't important.
I have a stored procedure called SetAsset. It takes some input that it uses to update an asset. Optionally it can be used to update other assets that have matching properties (provided by some criteria). To do this I loop through the matching assets in a cursor calling SetAsset for each one. Due to the fact SetAsset is calling itself it is kind of recursive, but it will only ever go 1 call deep into the recursion, so its not really recursive and its not meant to be recursive. And I can guarantee it will only ever go 1 call deep.
But wait there's more! You're thinking, "Cursors and recursion... How could it get any worse?" ...
The primary table being updated by SetAsset has a trigger for INSERT, UPDATE, and DELETE operations so that gets fired a whole whack load of times.
Now, with all this said, the procedure works great (on a small number of assets). But once the number of assets grows beyond 150, I get the following exceptions:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Then the client disconnects and when I try to run another query I get this:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
The error isn't deterministic either, meaning it doesn't always occur at the same point during execution.
This has got me thoroughly stumped so any suggestions are most welcome.
Thanks!
December 30, 2008 at 1:07 pm
We will need to see the Sproc's code, the table definitions and the trigger code.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 1:34 pm
Check the SQL error log. Normally that message means that you've hit an error with a severity of 20 or higher (very, very severe) and the connection has been closed by SQL because of that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply