Stored procedure error handling.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored procedure error handling.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-17 : 14:59:53

Aaron writes "The environment: Win2K server, running IIS 5 and SQL Server 2000 everything with the latest service packs. A web application written in Asp classic using ADO to call stored procedures. We ran into some problems with our error handling. Sometimes checking the Asp err.number object was good, sometimes it had a zero when it should have had an error. After a bit of testing I was able to create two procs that demonstrate the problem.

Why does this throw an error that propigates into the Asp err object:

CREATE PROC testProc1
AS
SELECT 1/0 --that's one divided by zero
SELECT 1
--end proc

and this does not throw an error:

CREATE PROC testProc2
AS
SELECT 1
SELECT 1/0
--end proc

TestProc1 immediately terminates and loads the Asp err object with a divide by zero error. It can be trapped by using the:
if err.number <> 0 then syntax. However, testProc2 has an err.number of zero, indicating success. It seems that once any single statement in a proc has executed successfully then things like divide by zero errors are buried. If the divide by zero is first then the proc responds as expected. Any ideas or comments would by nice."

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-17 : 15:46:42

Both of these throw the divide by zero error on my machine. Are you running both of these on the same database?

HTH

=================================================================

Happy Holidays!

Kristen
Test

22859 Posts

Posted - 2004-12-18 : 02:46:31

The second one will show the error in the SECOND resultset - so you have to do MyRS.NextRecordset in ADO before you will see the error, and even then you may have to do a

for each objError in MyDataConn.errors

to see the errors.

This is particularly troublesome on, say, and UPDATE where there is normally NO resultset, as you should still iterate MyDataConn.errors to check for errors.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources