Extended Event collect sql text executed

Question

Post reply

Extended Event collect sql text executed

Lou

SSCrazy

Points: 2929
More actions
September 7, 2020 at 9:26 pm
Go to Answer
#3786827

Dear Coallegues,
I am trying to configure an extended event session in order to capture parameters executed with a specific stored procedures in a specific database, without results:
It is not possible use Profiler
Please help, thanks for any idea
CREATE EVENT SESSION [9999] ON SERVER
ADD EVENT sqlserver.rpc_completed
(
ACTION
(
sqlserver.database_id,
sqlserver.session_id,
sqlserver.username,
sqlserver.client_hostname,
sqlserver.sql_text,
sqlserver.tsql_stack
)
WHERE (sqlserver.database_id = 24) )
ADD TARGET package0.asynchronous_file_target
(
SET FILENAME = N'd:\test\299650v5.xel',
METADATAFILE = N'd:\test\299650v5.xem'
)
WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF
);
Erland Sommarskog

SSC-Insane

Points: 24038
More actions
September 9, 2020 at 4:09 pm
Answer
#3787362

Yeah, all I get for tsql_text is "Unable to retrieve SQL text".
Extended Events in SQL 2008 was a little half-baked, and things like capturing what is executed definitely works better with Trace in that version. (Well, I tend to think that it works better in any version given how much easier Trace is to use, but that is another story.)
So these appear to be the choices: Trace ...or upgrade, which is overdue anyway.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

Profiler is of course a really bad idea, but why would a server side Trace be impossible?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Try adding the module_end event to your event session. I believe you need to include that to get the parameters.

Sue

Lou SSCrazy Points: 2929 More actions · Answer 3

Thanks for your responses,

but it appears that in SQL Server 2008 (r2), is not possible to capture store procedures parameters, I have tryed with the events commented above and it brings the name but not the parameters.

Again, thanks for your ideas and effort.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Erland Sommarskog wrote:

(Well, I tend to think that it works better in any version given how much easier Trace is to use, but that is another story.)

It's too bad that we can only give one "LIKE" to a post or I'd give you a million on that statement. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)