SQL Server 2005 introduces a run-time Profiler library
The SQL Profiler tool has evolved in every major SQL Server release. Earliest versions of SQL Server came with no trace capability, then someone produced a filter utility (SQLEye) which was later the basis for MS's 6.5 SQL Trace filter utility. SQL 7.0 introduced a server event driven model but it's api was way too complex for day to day scripting use. SQL 2000 separated the server / GUI components cleanly, simplified access to the tsql system proc based api and introduced a function that allowed us to load .trc log files at run time.
Each of these steps improved our accessability to trace server activity, adding value for developers and admins alike. However we've never been able to get at this information directly at run-time, without having to open up a GUI, click on icons etc.
Until now.
SQL 2005 now includes a trace library which provides you with access to run-time trace information, from within your program. It does have one big caveat though - you need to create a Profiler trace definition file manually first and pass a file system location to the .tdf when using the new trace libary. This is a limitation if you want to DEFINE your traces at run time, but aside from this limitation, it's still a big step forward for admins to get at run time trace info. Another bit of good news is that the new trace library is backward compatible with SQL 2000
Here's a very brief code demo in VB.Net - you'll need VS2005 to run this. Note that this code is vastly simplified for simple demonstration.
Imports Microsoft.SqlServer.Management.Trace
.
.
.
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose