DBCC INPUTBUFFER;1, Retrieving Parameter info from executed SQL w/ Reporting Services

Question

Hi all,

I've been struggling with the DBCC INPUTBUFFER in SQL 2005/2008 attempting to get some information on each run of my queries. I know there are a number of other methods available that can do similar things to DBCC INPUTBUFFER, including sp_who/sp_who2, fn_get_sql(), dm_exec_sql_text, and so forth, but none of these others return the parameter values of the executed SQL, which is the key piece of information I'm trying to retrieve here.

So, I produced the following query:

ALTER PROCEDURE [dbo].[SampleProc]
 @Param1 NVARCHAR(MAX) = NULL,
 @Param2 NVARCHAR(MAX) = NULL,
 @Param3 NVARCHAR(MAX) = NULL
AS
SET NOCOUNT ON
BEGIN
 DECLARE @Procedure nvarchar(128);
 SET  @Procedure = OBJECT_NAME(@@PROCID);

 --get first parameter name for use in substring.
 DECLARE   @ParamName1 NVARCHAR(128);
 SELECT TOP 1 @ParamName1 = [PARAMETER_NAME]
 FROM INFORMATION_SCHEMA.PARAMETERS params
 INNER JOIN Sys.objects sysObjects ON params.SPECIFIC_Name = sysObjects.Name
 WHERE sysObjects.Name = @Procedure AND sysObjects.Type ='P'

 --get input buffer and substring as needed.
 CREATE TABLE #inputBuffer(EventType VARCHAR(128), Parameters INT, EventInfo NVARCHAR(MAX))
 INSERT   #inputBuffer EXEC('DBCC INPUTBUFFER(' + @@SPID + ')')
 SELECT SUBSTRING(EventInfo, CHARINDEX(@ParamName1, EventInfo, 0), LEN(EventInfo) - CHARINDEX(@ParamName1, EventInfo, 0))
 FROM   #inputBuffer
 DROP TABLE  #inputBuffer
END

and ran it with the following in SQL Management Studio:

EXEC [dbo].[SampleProc]
  @Param1 = '444',
  @Param2 = '555',
  @Param3 = NULL

This works perfectly, and returns something along the lines of

"@Param1 = '444',    @Param2 = '555',    @Param3 = NULL"

as its output. Please note: I've trimmed everything before "@Param1" in my query above with a substring.

I'm trying to include sections of the above SQL in queries run by reporting services, and then save the information returned from DBCC INPUTBUFFER to a log table for later use.

However, when queries like this are run through reporting services, either via the web UI frontend or otherwise, the return string is simply:

"DatabaseName.dbo.SampleProc;1"

and all the useful information about the parameters is lost. I've done some searching online and found a few others asking similar questions (certain uses of DBCC INPUTBUFFER simply returning "command;1"), but no answers. Can anyone explain what's happening here and how to get around it?

Best Regards,

David McKay.

http://www.davemckay.co.uk