How to display the error message when using ":on error exit" in SQLCMD mode?

Question

I have a query with T-SQL statements in SQL Server 2012 with SQLCMD Mode turned On running interactively in SSMS. There's a :on error exit statement. When I run the query, the query window shows "Query completed with errors" but no error messages are displayed.

How do I get the error message to display?

Answer 1

Refer MSDN(RaiseError) to achieve what you are looking for:

Severity levels 11-19 will cause execution to jump to the CATCH block.

BEGIN TRY

    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

Note: Using severity levels from 20 through 25 are considered fatal, will cause abrupt termination of client connection.

Answer 2

It was a syntax error and not a run time error. I am not sure if SQLCMD mode doesn't show any errors in SSMS like vanilla sQL.