Showing posts with label handling. Show all posts
Showing posts with label handling. Show all posts

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