sqlcmd error handling

Question

I have a basic select query which forces an error saved into a .sql file called 2 test fail.sql:

SELECT 1/0;
GO

I'm calling this via sqlcmd with the following script:

BEGIN TRY
    EXEC [master].[sys].[xp_cmdshell] 'sqlcmd -S . -i "G:\Release test\2 test fail.sql "'
END TRY
BEGIN CATCH
 SELECT '1'
END CATCH

I want it to fall into the CATCH but it is just selecting an output to the results. I've looked around and -b is recommended but this doesnt seem to work for me, any help would be appreciated

Answer 1

I am not sure that it is even possible. TRY/CATCH catches exceptions, however xp_cmdshell does not throw exception, it just shows you error in output. You can do some workarounds, for example:

BEGIN TRY
        DECLARE @outputs AS TABLE (outp VARCHAR(MAX));
        DECLARE @result INT;
        INSERT INTO @outputs
        EXEC @result = [master].[sys].[xp_cmdshell] 'sqlcmd -b -S . -i "d:\1.txt"'
        IF  (   @result > 0)
        RAISERROR ('error',16,1)
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;


        select * from @outputs
    END CATCH