June 29, 2006 at 5:58 am
I have a stored procedure that has several update statements in it and when I run it against a table with 27,000 rows it stops with an error. The procedure basically looks for a series of chars in a string and copies them to another column. The problem is I can't tell which row is causing the error. The table has a PK and I was wondering if there is a way to capture this when the error occurs. Maybe I could capture it to a variable and then I would be able to see it using the debug sp command.
June 29, 2006 at 6:16 am
You'd be better off posting your procedure so someone can take a look at it...
Here're several suggestions to debug:
1) You can use t-sql debugger - if you've never used one, use BOL to guide you.
2) When running the procedure in QA, you'll get a line# as reference for the error.
3) Use that line# to run only the specific portion of the code and use a "select..." with your filters to see what rows you get back so you can narrow down your tracking.
4) You can use "PRINT" within your sproc to aid you.
**ASCII stupid question, get a stupid ANSI !!!**
June 29, 2006 at 6:16 am
Could you please post the code and some example data? I cannot think of a way to determine which row is causing the problem apart from recoding as a procedural cursor, seeing exactly which pkey causes the error by printing out each pkey as it is processed and then fixing the bug. You can then put your fixed code back into your set-based code.
June 29, 2006 at 6:24 am
Thanx for the answers. I know what is causing the error. I just need to know what row is causing it so I can adjust the proc to look for this condition and react to it. Another thing that has perplexed me since I started programming T-SQL is the line numbers. I see them in errors, but they sure don't seem to make since. Do they include blank lines, returns etc.? Is there a way to show line numbers?
June 29, 2006 at 6:25 am
You can't do what describe since a SQL statement is a transactuion which will succeed or fail as a whole.
Changing the UPDATE to a SELECT would tell you whether the issue is in the read or write part of the operation. You could find the first row that causes the error by using TOP <N> in your statement. You could start with <N> = 13500 (halfway through the records), then iteratively apply the following algorithm: if the error still occurs, divide N by 2. If not, multiply N by 1.5. If you repeat this process you will find the point at which subtracting 1 from N makes the difference between error and no error. The first erroring row is then row N. effectively you narrow it down by a systematic process of trial and error.
But you can probably avoid this kind of procedure by checking the content of the error message. SQL error messages are generally pretty good. If you post the SQL and the error message you get, it will probably be possible to use a more direct way of finding the source of error. E.g. if the error is an out-of-range input to CAST function, you may be able to serach directly for records with out-of-range values.
Line numbers in errors refer to the batch or procedure in which the error occurs. If you are using SPs, functions, and especially dynamic SQL etc this may not be the same as the line number in the batch you are currenty running. However, if you are just running plain SQL, the line number should work. In QA, you can click on the error to jump to the appropriate line.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply