Obtaining SSMS errors when a SSRS report returns an error, without logging on to SSMS each time.

• High Plains Grifter

  Ten Centuries

  Points: 1191

  More actions

  July 16, 2013 at 6:33 am

  #275997

  Hello people,

  I have a problem in that if some SSRS report runs a procedure which returns an error, the message displayed at the SSRS end is very inadequate. For various reasons, we have been advised not to turn on remote errors to display the SSMS error message which we need to see in order to diagnose the problem.

  Therefore, I wonder whether there is any way to make a query that will return the procedure error message returned, given some information about the user / procedure / report or whatever (we normally get told about errors pretty soon after they happen, so we may be able to just order the error messages by time, if only we can find the error messages).

  We cannot simply log into our live environment for every error to look at the error in SSMS. Unfortunately there are a lot of procedures already existing and we would rather avoid using CATCH commands to capture errors on every single one, which would be a large amount of work.

  I have had a bit of a fumble around the trace logs and things, but have not found the error messages that will be helpful... any ideas - are the error messages stored somewhere?

  Example:

  I make a report based on this procedure:

  ALTER PROCEDURE [dbo].[RETURN_AN_ERROR]

  AS

  CREATE TABLE #TestMeUp

  ( Id INT

  ,Value VARCHAR(10))

  INSERT INTO #TestMeUp

  SELECT 1,'this'

  UNION

  SELECT 2,'that'

  UNION

  SELECT 3,'the'

  UNION

  SELECT 4,'other'

  UNION

  SELECT 5,'and'

  UNION

  SELECT 6,'look'

  UNION

  SELECT 7,'another'

  UNION

  SELECT 1,'one'

  SELECT Id

  ,(SELECT B.Value FROM #TestMeUp AS B WHERE B.Id = A.Id) AS Value

  FROM #TestMeUp AS A

  DROP TABLE #TestMeUp

  the report gives this unhelpful error:

  An error has occurred during report processing. (rsProcessingAborted)

  Query execution failed for dataset 'Error'. (rsErrorExecutingCommand)

  For more information about this error navigate to the report server on the local server machine, or enable remote errors

  whereas SSMS gives this much more useful error message (which we would like to see from outside of SSMS):

  8 row(s) affected)

  Msg 512, Level 16, State 1, Procedure RETURN_AN_ERROR, Line 26

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

  Thanks for looking!

  Mark

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  July 16, 2013 at 7:06 am

  #1632851

  Depending on the drive you have chosen to install your Reporting Services instance, the physical path to the Log Files where you can search for errors is the following:

  C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles

  

  The SQL Guy @ blogspot[/url]

  @SeanPearceSQL

  About Me[/url]

• High Plains Grifter

  Ten Centuries

  Points: 1191

  More actions

  July 16, 2013 at 7:42 am

  #1632873

  Hi Sean,

  ours is on the server, so I'll need to get someone else to have a look at that, but if that holds the full error messages, then that's what I want - thank you very much!

  Mark