BTEQ throws error when session parameter maxerror is set to 1 and SQL script is executed with .run file,
it doesn't throw error when SQL script is executed with .compile file
Is there any hack for that?
Thanks in advance :-)
.compile file option doesn't even raise errorlevel, even if there are errors in procedure definition,
I can't find any way to catch it's errors
.if errorlevel > 0 doesn't work either
Sooooo, there is solution, but a "bit" desperate.
We can save BTEQ output to file and search it for SPL errors right after running BTEQ with .compile file option.
We must deal with syncing saving of log file and searching for errors.
I was trying to deal with this same issue (how to trap errors in stored proc compilations) and I think I found a cleaner way of doing it.
Quoting the manual:
BTEQ Query Reference -> Handling Errors -> Stored Procedure Compilation
All syntax and semantic errors in the stored procedure source text are reported with a nonzero
warning code. The activity count is set to the total number of compilation errors and
So, I came up with this
-- Compile a stored proc which contains an error .compile file=Stored_proc_with_compilation_error.sql .IF activitycount > 0 then SELECT STORED_PROCEDURE_COMPILATION_ERROR_OCCURRED;
So, if any complilation errors occur, the SELECT statement is executed, which itself causes an error that can be trapped (since there is no column called "STORED_PROCEDURE_COMPILATION_ERROR_OCCURRED")
In the logs, you see this:
-- Compile a stored proc which contains an error
.compile file=Stored Stored_proc_with_compilation_error.sql
*** Procedure has been replaced. One Error/Warning.
*** Warning: 5527 Stored Procedure Created with Warnings.
*** Total elapsed time was 5 seconds.
Warnings reported during compilation
SPL5000:W(L786), E(5495):Stored Procedure 'D1P_XX.sp_procedure_which_is_missing' does not exist.
.IF activitycount > 0 then SELECT STORED_PROCEDURE_COMPILATION_ERROR_OCCURRED;
*** Failure 3822 Cannot resolve column 'STORED_PROCEDURE_COMPILATION_ERROR_OCCURRED'. Specify table or view.
Statement# 1, Info =0
*** Total elapsed time was 1 second.
*** You are now logged off from the DBC.
*** Exiting BTEQ...
*** RC (return code) = 8
Hope this helps someone!
Multi-condition IF commands and additional status value keywords were added to BTEQ for TTU 16.00 and rolled back to all supported releases starting with 14.00.00.21, 14.10.00.15, 15.00.00.06 and 15.10.01.02. As part of that, the WARNINGCODE status value keyword was added. Only the more recent BTEQ Reference Manuals document this and have examples. Basically, the database returns a non-0 warning code value whenever a compile results in warnings or errors. For an SQL-stored Procedure (SSP), a 5526 warning code can be explicitly tested for to see if the compiler gave an error. A 5527 would indicate it only gave warnings. For other objects such as XSPs and UDFs the codes are 5603 and 5607. So a multi-condition IF command such as the following can be used to branch accordingly in a BTEQ script when a compile has been unsuccessful either because the compiler itself is not configured correctly or because it worked but returned errors or warnings about what it attempted to compile.
.IF ( NOT ((ERRORCODE=0) AND (WARNINGCODE=0)) ) THEN .GOTO errorpath
In case it helps, starting with BTEQ 16.20.00.04 you can also opt to use an IF/ELSE/ENDIF construct rather than a GOTO. The following example shows how one might get more specific branching accomplished...
.BRANCHMSG TERSE .IF ERRORCODE=0 .REMARK 'Compile successful.' .IF WARNINGCODE=0 .REMARK 'No compiler errors or warnings.' .ELSEIF WARNINGCODE=5527 .REMARK 'Compiler returned only warnings.' .ELSEIF WARNINGCODE=5526 .REMARK 'Compiler returned errors.' .ELSE .REMARK 'Other non-0 warning code returned.' .ENDIF .ELSE .REMARK 'Compile failed.' .ENDIF
The BRANCHMSG command was added at the same time as IF/ELSE constructs. This control enables users to specify whether the generation of messages that explain input instruction branching for GOTO skipping and conditional block bypassing is to be verbose or terse. An ERRORONDBSWARNING command was also added at the same time that the WARNINGCODE keyword was added. This control enables users to specify that database warnings are to be treated as though they are errors. This allows the ERRORLEVEL status value to also be assigned to the severity level associated with each encountered warning.