November 11, 2004 at 2:27 pm
SQL Server 7.0 SP3
I am having a problem with a stored procedure and I am pulling my hair out trying to figure out whats wrong.
The stored procedure contains a section of code consisting of 3 simple update statements like those shown below. These are the last statements in the stored procedure. The select @error & insert into debugtable statements were put in to determine that at least the sections of code were at least passed thru if the WHERE clause failed.
The stored procedure is called hundreds of times a day. A couple of times a week, the following happens.
Table one is updated but Table 2 & Table 3 is not. The debugtable shows an entry for Table1 Update with a error code of 0 and a row count of 1. There are no entries for Table2 or Table3. Its like the stored procedure exited after inserting the Table1 update info into the debugtable.
There are no entries in the server event logs or the sql server logs at the time of the problem.
If anyone has any ideas or how to further troubleshoot the problem, that would be great.
IF @Name != ' '
BEGIN
update Table1
set Col2 = 'A',
Col3 = 'B'
where Col1 = @Name
select @Error = @@ERROR, @RowCount = @@ROWCOUNT
insert into DebugTable values (getdate(),'Table1 Update',STR(@Error),STR(@RowCount))
update Table2
set Col2 = 'A',
Col3 = 'B'
where Col1 = @Name
select @Error = @@ERROR, @RowCount = @@ROWCOUNT
insert into DebugTable values (getdate(),'Table2 Update',STR(@Error),STR(@RowCount))
update Table3
set Col2 = 'A',
Col3 = 'B'
where Col1 = @Name
select @Error = @@ERROR, @RowCount = @@ROWCOUNT
insert into DebugTable values (getdate(),'Table3 Update',STR(@Error),STR(@RowCount))
END
November 11, 2004 at 4:27 pm
So can you reproduce the error - I'm guessing not? But if you can, try stepping through the stored proc in debug mode and see whether that gets you anywhere.
Does the problem always happen at the same time of day? Perhaps when some other process is running?
You could try running Profiler - set up a trace that filters out as much 'noise' as possible and focuses purely on the execution of the stored proc and leave it running - may provide some clues.
Good luck
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 12, 2004 at 3:08 am
Hi
Sorry if this is a bit over-obvious but it looks to me like at the time it "fails" that tables 2 and 3 don't have a Col1 value that matches @Name. As there are no errors or exceptions then it looks to me like it's doing exactly what it's supposed to.
Avoid the temptaption to say "...but I know there are relevant rows in the tables". My guess is that SQL-Server thinks not (maybe some other process locking the tables or something!)
My next step would be, before each of the update steps do a count of the table where Col1 = @Name and save that in a working table somewhere along with some sort of date / time stamp (so you can tie the values with the "failures"). It's also possible that the first update causes the value of @Name to be Nulled, so it would be worth holding the value of @Name as well, before each update
Then when it goes wrong you can see if you actually have any data that needs changing...
Hope that helps
Sam
November 12, 2004 at 6:27 am
Thanks for the suggestions. It is not reproducable & appears to be random.
It is a good suggestion to add the @Name value to my debug table before the updates but even if there were no rows in table 2 or table 3 that matched the WHERE clause the debug insert statement should have inserted the Table2 Insert & @@Error & @@ROWCOUNT into the debug table. Its almost as if the stored procedure just exited after the Table1 Inserted debug statement.
I will try profiler but since it only happens 1 or 2 times a week, it will be a little tough to keep logs from being huge.
November 17, 2004 at 2:47 am
Just thought following might help in resolving your problem
When a statement with in sp executes in sql server, and there is a error, whether the next statement will execute or not depends on the severity of the error.
It might be happening in your case that the severity of the error in executing second update is so high that the sql server is aborting before even coming to error handling code. I have observed this behavior many times and I have started using the following stardard for coding stored procedures, which seem to work very well.
1. Do not write error handling code inside stored procedures (since it is not certain that it will execute)
2. Exit the sp as soon as error occurs with a return error code
3. handle the error at the calling routine (VB-->ADO for example)
Example
Create Procedure TestSP
...
BEGIN
UPDATE TABLE1
SET ...
WHERE ... ---Scenario 1 Severe error results in sp abort here
SELECT @M_ERR = @@ERROR,...
IF @M_ERR <> 0
RETURN @M_ERR ---Scenario 2 less Severe error results in controlled exit
UPDATE TABLE2
SET ...
WHERE ... ---Scenario 1 Severe error results in sp abort here
SELECT @M_ERR = @@ERROR,...
IF @M_ERR <> 0
RETURN @M_ERR ---Scenario 2 less Severe error results in controlled exit
...
RETURN 0
END
-- VB --> ADO--------------------------------------
Function blnExecTestSp() as boolean
On Error Goto blnExecTestSpError
blnExecTestSp = False
'Create connection (usrCn) and command (usrCmd)
'Set parameters for usrCmd
usrCmd.Execute
If usrCmd.Parameters(0) <> 0 --- Scenario 3 Handle less severe errors
'Error information available in usrCmd.Errors
'Handle the error, write to log,...
Exit function
End if
blnExecTestSp = True
Exit function
blnExecTestSpError: --- Scenario 4 Handle severe errors here
'Error information available in usrCmd.Errors
'Handle the error, write to log,...
End Function
As you can see here severe errors are handled using Scenario 1 and 4
and less severe errors are handled using Scenario 2 and 3
November 18, 2004 at 3:52 am
Fair point but this wouldn't work if your SP was kicked of by another server process (say, a batch job).
I do generally dissagree though... you should always have error handling in your procedures.
It's true that serious errors just kill the procedure before your error handler kicks in and you need to cater for that, but if you properly construct your error handler it can return some valuable info on the cause of what went wrong as you can include data into your error string. You can of course get round that by using output parameters for returning error data but you start getting complexity and defeat the object of not having error handling...
I think you really need a good combination of both techniques.
Sam
November 18, 2004 at 6:02 am
Thanks for the suggestions. Unfortunately, the SP is called by a 3rd party application that I have no control over. I do not know what sort of error handling it has. I am only given a required SP name, input & output parameters.
If a serious error occurs that simply stops execution of the SP, shouldn't there be some sort of error info in the SQL log?
November 18, 2004 at 6:48 am
Try the following example. It is similar to the one in the original problem. I am trying to write to a log table for each insert. You can run it in query analyzer to see the results.
Note that only the first insert succeeds. Second insert has a error trying to insert string into integer column. Log table will not have entry for second insert.
This is one of the case where the execution stops where error occurs (second insert). There may be other cases...
When I said severe error, I didn't meen fatal errors. So you will not see these errors in Sql*server log or event log. These errors have the different severity level.
Drop Table dbo.RpTest123
Go
Create Table dbo.RpTest123
(code int,
name varchar(12)
)
go
Drop table dbo.RpTestLog123
go
Create Table dbo.RpTestLog123
(
error varchar(12),
descr varchar(255)
)
go
drop procedure dbo.RpTest123Sp
go
Create procedure dbo.RpTest123Sp
AS
Begin
declare @m_err int
insert into dbo.RpTest123 values (1,'Rajesh1')
select @m_err = @@error
insert into dbo.RpTestLog123 values(STR(@m_err),'first insert')
insert into dbo.RpTest123 values ('sdfd','Rajesh2')
select @m_err = @@error
insert into dbo.RpTestLog123 values(STR(@m_err),'second insert')
end
go
---------------------------------
Now run the sp once
exec dbo.RpTest123Sp
check the log table
select * from dbo.RpTestLog123
------------------
0 first insert
November 18, 2004 at 7:17 am
Rajesh, thanks for the sample code. It was enlightnening. SQL Server should not just exit the procedure because of the 'string in an integer col' error. The statement should fail & the error code should available in the @@error variable. That is a serious deficiency.
I did not know of this behavior and it gives me more things to look at.
Again, thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply