SQLCMD + SQLDIAG
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...