SQLCMD + SQLDIAG

Steve — Mon, 10 Mar 2008 19:23:00 GMT

I stepped in some cat food on my way to my computer. It was duck I believe. Darn #@! cat...

But nevertheless, today I played around with SQLCMD mode in Management Studio.
When running the following script, the SQLDIAG STOP_ABORT immediately gets executed before the stored procedure has even started.

:!! SQLDIAG /R /Q /O"C:\SQLDIAGResults"
:!! SQLDIAG START

EXEC my_proc
GO

:!! SQLDIAG STOP_ABORT

A small workaround I found as I thought it might help if a wrap the execution of the proc: put the exec proc statement in a try and cath block

:!! SQLDIAG /R /Q /O"C:\SQLDIAGResults"
:!! SQLDIAG START

BEGIN TRY
    EXEC my_proc
END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

:!! SQLDIAG STOP_ABORT

A try and catch statement never hurts does it...

Steve's SQL Server Blog : SQLDIAG

SQLCMD + SQLDIAG