Stairway to Server-side Tracing - Step 2: Creating a SQL Trace Using T-SQL (Stairway Series)

Question

Post reply

Stairway to Server-side Tracing - Step 2: Creating a SQL Trace Using T-SQL (Stairway Series)

Viewing 7 posts - 16 through 21 (of 21 total)

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

DBA_Learner SSCarpal Tunnel Points: 4228 More actions · Answer 1

I am following the same way as in script..but receiving the folowing error on Step 2:

Must declare the scalar variable "@TraceID".

Any suggestions..do we need to declare any traceid value

jb 60938 Valued Member Points: 53 More actions · Answer 2

Hey

If you didn't remove the line:

DECLARE @TraceID INT;

You are declaring it, so I would check for misspellings, left out lines, or if you do a GO inbetween

/Jon

DBA_Learner (9/12/2012)
I am following the same way as in script..but receiving the folowing error on Step 2:
Must declare the scalar variable "@TraceID".
Any suggestions..do we need to declare any traceid value

DBA_Learner SSCarpal Tunnel Points: 4228 More actions · Answer 3

Thanks for response. It is now working fine. But, I have a question.

Now in msdn sites for sp_trace_setevent / sp_trace_setfilter / sp_trace_setstatus , I see the following WARNING message.

/*

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead. */

So, can anyone explain me what is this Extended events mean?

Dave Brooking Ten Centuries Points: 1373 More actions · Answer 4

It looks like the Extended events for tracing are a new addition in 2012, does the following link help? http://msdn.microsoft.com/en-us/library/ff878264.aspx

Dave

majinbao Grasshopper Points: 13 More actions · Answer 5

Thank you very much for the post. great articles.

I have one question:

I can use trace to get who, when, from where took the database OFFLINE. but I can not get the whole information of who, when, from where took the database ONLINE.

the only information I can get is object:alert, but it doesn't tell me the TextData is null. I can not tell what happened to this database.

any solution? thanks.

Dan Guzman SSCommitted Points: 1506 More actions · Answer 6

majinbao (11/7/2013)
Thank you very much for the post. great articles.
the only information I can get is object:alert, but it doesn't tell me the TextData is null. I can not tell what happened to this database.
any solution? thanks.

The Object:Altered event does not expose the statement text. If you need the text data too, you can include the SQL:Statement Completed event, filtered accordingly (i.e. LIKE '%ALTER%'. The SQL:Statement Completed event can then be correlated with the Object:Altered based on TransactionID included in both events. Assuming you are leveraging the default trace for the Object:Altered event, a separate trace for the SQL:Statement Completed event will be needed.

harvey007 Valued Member Points: 52 More actions · Answer 7

http://idealprogrammer.com/net-languages/c/idealsqltracer-simple-free-open-source-alternative-sql-profiler/ =>IdealSqlTracer is a nice open source C# project on github that uses sp_trace_create, sp_trace_filter, sp_trace_setstatus sql procs to create custom traces for you.. The advantage of IdealSqlTracer is that it takes all of the sql generated behind the scenes in a desktop application or web page, and it formats it to make it easily readable. The formatted sql is put into notepad so that you can cut-n-paste the sql and run it directly in Sql Server Management Studio (SSMS). The advantage of doing this is that it makes it possible for you to see exactly what is going on in your application or website. I have found this to be a great debugging tool for me as a programmer.

Here is an example screen shot of the formatted sql that is placed into notepad:

Thanks, Wade Harvey