August 11, 2009 at 3:44 am
Hi
I am trying to capture the Stored Procedure call executed by a report.
When executing my TestProc (below) with exec TestProc 1,1 within management studio it correctly returns the sp name and the parameters ie ‘TestProc 1,1’.
However when I call the proc from reporting services it only returns the proc name and not the parameter values.
This difference appears to be related to Inputbuffer seeing the reporting services call as RPC eventtype rather than a language event.
create proc TestProc
@param1 int,
@param2 int
as
declare @cmd varchar(250)
set @cmd ='dbcc inputbuffer (' + cast(@@spid as varchar(13))+') WITH NO_INFOMSGS '
create table #tmp (eventtype varchar(100), parameters int, eventinfo varchar(250))
insert #tmp
exec(@cmd)
select eventinfo as col1
from #tmp
go
I have tried a variety of other methods such as
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = @@spid
SELECT * FROM ::fn_get_sql(@Handle)
And
SELECT r.session_id, r.status, r.start_time, r.command, s.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.session_id = @@spid
And
Using the Adam Bean views dbo.who_vw which is described http://www.sqlservercentral.com/Forums/Topic464009-146-1.aspx here. When called from reporting services the view has the same problem as DBCC inputbuffer and only displays the proc name and not the params in the query column.
It does show in the Program Name column, however, that the calls from reporting services are using .Net SqlClient Data Provider. This might be a clue.
All methods leave the parameter values off the end when the containing proc is called from reporting services.
Profiler displays the proc call with the parameters fine so SQL Server must be ‘know’ what they are.
Any advice greatly appreciated.
Cheers,
Jules
August 11, 2009 at 6:49 am
August 12, 2009 at 2:02 am
August 13, 2009 at 9:06 am
Ok, I tested it both in SSIS and SSRS. SSIS works fine, it returns the result you expect. And as you say, SSRS doesn't work... Have you tried creating a function instead that returns the data?
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
December 3, 2009 at 1:56 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply