Wednesday, March 7, 2012

oSql Error Handling

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

No comments:

Post a Comment