title: How to expose more information about the failure of a stored proc in SQL agent
body: <p>I have a SQL agent job setup and in that job there is a step to execute a stored proc. If that stored proc fails then the SQL agent job will display an error message but there is no other information. Something like a stacktrace or at least the stored proc that was running and the line number would be highly useful.</p>
  
  <p>e.g.
  If the following stored proc is executed then an error message like "Executed as user: NT AUTHORITY\NETWORK SERVICE. Start [SQLSTATE 01000] (Message 0)  Invalid object name 'NonExistentTable'. [SQLSTATE 42S02] (Error 208).  The step failed." with no indication where exactly the failure occured.</p>
  
  <pre><code>CREATE PROCEDURE TestSpLogging AS 
  BEGIN
  PRINT 'Start'
  SELECT * FROM NonExistentTable
  PRINT 'End'
  END
  </code></pre>
  
  <p>What's the best way to expose this information?</p>