Running Profiler via a SQL script.
in [Using MS SQL Server]
|
Prev: a brand new query tool is out on beta, we need feedback - so you get a free copy
Next: How to determine free time
From: Jimbo on 6 Nov 2009 10:42 Hi, SQL 2005 Enterprise Edition (SP2) I'm having trouble running a SQL script generated via the SQL 2005 Profiler. I've set up a very simple proifle template to look for 'Crystal Report' SQL that runs for over 2 seconds in the mill - the only two filters I have on the template are 'Application name' and 'Duration'. When I run it in the Profile GUI, all is fine, and I receive the expect output. When I generate a SQL script of the profile template, to run in Query Analyser or as a scheduled job, none of the filtering seems to work, and I receive data for all SQL running through the mill. What's wrong? Does anyone have any suggestions? Thanks very much, James.
From: Erland Sommarskog on 6 Nov 2009 18:38 Jimbo (james.goodwill(a)googlemail.com) writes: > SQL 2005 Enterprise Edition (SP2) > > I'm having trouble running a SQL script generated via the SQL 2005 > Profiler. > > I've set up a very simple proifle template to look for 'Crystal > Report' SQL that runs for over 2 seconds in the mill - the only two > filters I have on the template are 'Application name' and 'Duration'. > When I run it in the Profile GUI, all is fine, and I receive the > expect output. > > When I generate a SQL script of the profile template, to run in Query > Analyser or as a scheduled job, none of the filtering seems to work, > and I receive data for all SQL running through the mill. > > What's wrong? Does anyone have any suggestions? There was a bug with creating the script in Profiler, so that traces were not exported correctly. I don't remember when it was fixed, but take a look at the calls to sp_trace_setfilter at the end of the script, and verify the calls against the documentation in Books Online. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Jimbo on 8 Nov 2009 13:49
On Nov 6, 11:38 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > Jimbo (james.goodw...(a)googlemail.com) writes: > > SQL 2005 Enterprise Edition (SP2) > > > I'm having trouble running a SQL script generated via the SQL 2005 > > Profiler. > > > I've set up a very simple proifle template to look for 'Crystal > > Report' SQL that runs for over 2 seconds in the mill - the only two > > filters I have on the template are 'Application name' and 'Duration'. > > When I run it in the Profile GUI, all is fine, and I receive the > > expect output. > > > When I generate a SQL script of the profile template, to run in Query > > Analyser or as a scheduled job, none of the filtering seems to work, > > and I receive data for all SQL running through the mill. > > > What's wrong? Does anyone have any suggestions? > > There was a bug with creating the script in Profiler, so that traces were > not exported correctly. I don't remember when it was fixed, but take a > look at the calls to sp_trace_setfilter at the end of the script, and > verify the calls against the documentation in Books Online. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland, Thank you. I have created scripted profiler SQL myself without using the Profiler to create it for me, so I will have a look at the SQL created. Thanks once again. James. |