Can anyone please tell me why my error handling does not work. I am using the following code in a script file which I pass as an input file to oSql:
IF (@.@.ERROR <> 0)
EXIT(1)
ELSE
EXIT(0)
I have also tried labels as written below to no avail:
IF (@.@.ERROR <> 0) GOTO error_control
success_control:
EXIT(0)
error_control:
EXIT(1)
If anyone knows the solution could they please let me know.I've just written a little script as well and it seems that 'EXIT' must be the last statement within the script.
Example:
SET NOCOUNT ON
DECLARE @.rc int
SET @.rc = 0
IF ((SELECT DB_NAME()) = 'pubs') BEGIN
SET @.rc = 99
GOTO COMMON_EXIT
END
SELECT DB_NAME()
COMMON_EXIT:
EXIT(SELECT @.rc)
GO
The EXIT that will set the MS-DOS ERRORLEVEL variable.
Example (1)
C:\Work>osql -E -Styche -isctest.sql -n -h-1
master
0
C:\Work>echo %errorlevel%
0
C:\Work>
Example (2) against Pubs
C:\Work>osql -E -Styche -isctest.sql -n -dpubs -h-1
99
C:\Work>echo %errorlevel%
99
C:\Work>
Also one thing about @.@.ERROR, it is affect by all T-SQL statements.
Example
UPDATE blah blah blah
IF (@.@.ROWCOUNT = 0) BEGIN
IF (@.@.ERROR = 0)
SET @.RC = 0
ELSE
SET @.RC = 1
END
@.@.ERROR will always be 0, since the IF statement is successful it will override the @.@.ERROR value set by the UPDATE statement.
UPDATE blah blah blah
SELECT @.Rows = @.@.ROWCOUNT,
@.Err = @.@.ERROR
IF (@.Rows = 0) BEGIN
IF (@.Err = 0)
SET @.RC = 0
ELSE
SET @.RC = 1
END
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment