May 7, 2012 at 8:13 am
OK i'm coming to those more experienced than my self. i had an SP fail and i am wondering if with out having a trace or extended events running if there is any way to find the error generated by the SP. I have looked through the server error logs and nothing was there (Not unexpected but worth a look any way.)
I had the error reported by one of our applications with no sql error information which is not normal. The SP deletes information out of our cue table with code similar to the following:
CREATE PROCEDURE usp_RemoveCue
@FKvar
AS
DELETE b
FROM tableA a
INNER JOIN tableB b
ON a.id = b.id
WHERE a.FK_id = @FKvar
DELETE tableA
WHERE a.FK_id = @FKvar
I am thinking the join failed and if i'm hosed, i'm hosed and will have to bring it up to the higher ups to turn on a way to log the errors.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 7, 2012 at 8:40 am
i guess it depends on what you mean by failed. do you mean:
1. zero rows got deleted, but you expected it to delete something,
2. or do you mean it actually got an error (probably due to a constraint violation?) when the rows weere being deleted, but some child rows with a FK caused an error and rollback?
the join condition is no big deal, unless you misspelled a column name, that would not cause an error; the join might always cause zero rows to be deleted.
is there an error at all? are there any foreign keys on table B that coudl cause a rollback? is there more tables with FK's to Table A than just Table B?
Lowell
May 7, 2012 at 9:33 am
Lowell (5/7/2012)
i guess it depends on what you mean by failed. do you mean:1. zero rows got deleted, but you expected it to delete something,
2. or do you mean it actually got an error (probably due to a constraint violation?) when the rows weere being deleted, but some child rows with a FK caused an error and rollback?
the join condition is no big deal, unless you misspelled a column name, that would not cause an error; the join might always cause zero rows to be deleted.
is there an error at all? are there any foreign keys on table B that coudl cause a rollback? is there more tables with FK's to Table A than just Table B?
nothing got deleted. there are actually no constraints on the table since things move so quickly through the automated cue. TableA is jobID, machineID, Denormalized columns the machines need to do the task. TableB is jobID, machineID. on the next run it ran successfully but the jobs got repeated (since they were never deleted from the cue on the first run).
was hoping there was some spot it may have gotten logged. i have been looking at the data involved and see no abnormalities.
looks like ill have to bring up logging or error handeling to the boss
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 7, 2012 at 11:01 am
Unless we're talking about some type of actual error, no, there's nothing to see for a query that behaved, but didn't do what you wanted.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2012 at 1:05 pm
Grant Fritchey (5/7/2012)
Unless we're talking about some type of actual error, no, there's nothing to see for a query that behaved, but didn't do what you wanted.
what i expected to hear. what im figuring is one delete occurred the other got rolled back which caused the issue. thanks for the help.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 8, 2012 at 1:56 am
capn.hector (5/7/2012)what i expected to hear. what im figuring is one delete occurred the other got rolled back which caused the issue. thanks for the help.
It means your SP requires proper error handling 😛
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 8, 2012 at 2:42 am
capn.hector (5/7/2012)
Grant Fritchey (5/7/2012)
Unless we're talking about some type of actual error, no, there's nothing to see for a query that behaved, but didn't do what you wanted.what i expected to hear. what im figuring is one delete occurred the other got rolled back which caused the issue. thanks for the help.
Put the two DELETE statements inside a TRANSACTION.
Also check the transaction isolation level.
Did any deadlock happen?
May 8, 2012 at 4:24 am
If you can modify the proc code, you could add something that raises an error yourself;
For example if not exists(SELECT therRecordstoDelete) or testing if @@rowcount =0
And log info to helpxdiagnose the issue
Lowell
May 8, 2012 at 6:08 am
thanks to the suggestion of Grant it got me thinking about the where clause. i was helping out the operator about an hour after the "Error" occured since it was unusally high job volume (had a large batch come through the pipe). at the time of the "Error" he was moving things around the cue table for manual load balancing updating the machineID in the cue table. (while our system is good a giant batch can slow down every thing else) so what im fairly certain happened is durring the time the application was reading info out of the denormalized table the jobs in the cue table were moved causing the where to fail since the jobs were no longer assigned to the machine. Not an "Error" in sql but an error in what normally happens when the batches are at normal levels.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 8, 2012 at 6:11 am
capn.hector (5/8/2012)
thanks to the suggestion of Grant it got me thinking about the where clause. i was helping out the operator about an hour after the "Error" occured since it was unusally high job volume (had a large batch come through the pipe). at the time of the "Error" he was moving things around the cue table for manual load balancing updating the machineID in the cue table. (while our system is good a giant batch can slow down every thing else) so what im fairly certain happened is durring the time the application was reading info out of the denormalized table the jobs in the cue table were moved causing the where to fail since the jobs were no longer assigned to the machine. Not an "Error" in sql but an error in what normally happens when the batches are at normal levels.
Interesting, but you did the move before the normal load occurred which is why you just had a "failed" WHERE clause instead of an actual blocking situation. Makes sense.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2012 at 6:21 am
Grant Fritchey (5/8/2012)
capn.hector (5/8/2012)
thanks to the suggestion of Grant it got me thinking about the where clause. i was helping out the operator about an hour after the "Error" occured since it was unusally high job volume (had a large batch come through the pipe). at the time of the "Error" he was moving things around the cue table for manual load balancing updating the machineID in the cue table. (while our system is good a giant batch can slow down every thing else) so what im fairly certain happened is durring the time the application was reading info out of the denormalized table the jobs in the cue table were moved causing the where to fail since the jobs were no longer assigned to the machine. Not an "Error" in sql but an error in what normally happens when the batches are at normal levels.Interesting, but you did the move before the normal load occurred which is why you just had a "failed" WHERE clause instead of an actual blocking situation. Makes sense.
yep our app runs an SP which inserts the jobs from our cue table into or denormalized cue, reads out of our denormalized cue then runs a second SP to delete from both tables. thanks to your "It worked just not like you wanted it to" it pointed me in the direction i needed to look.
Thanks for the help
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 8, 2012 at 7:11 am
Queue, not cue, surely?
(Sorry :-D)
May 11, 2012 at 3:44 pm
Gazareth (5/8/2012)
Queue, not cue, surely?(Sorry :-D)
lol had pool cue on my brain when i was posting that.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply