Browse DevX
Sign up for e-mail newsletters from DevX


A Trace Tool, Part 1 : Page 3

A trace tool for tracing the execution path is a very powerful tool for debugging applications. When it comes to n-tier applications that use ASP, configured COM+ components and stored procedures in SQL Server it's a "must-have" tool. In this first part of a three-part article, Jimmy Nilsson starts by discussing some tracing aspects for SQL Server.




Building the Right Environment to Support AI, Machine Learning and Deep Learning


Normally I use a standardized code structure for my stored procedures too, but that’s another (long) story and therefore I just show a trace call here, without a complete stored procedure.

EXEC jnskTraceBegin @strSource

In the sample above you can see that I use OBJECT_NAME() instead of hard coding the name of the stored procedure. Then I send a trace call of the "Begin" type. It’s as simple as that. And of course, when you call jnskTraceBegin, for example, it’s impossible to tell (and you shouldn’t care) what mechanism is really used for sending the trace call. Have I mentioned earlier that I like encapsulation<g>?


I use a registry setting for checking if trace calls should be sent or not from my COM+ components. That registry setting is read at certain time intervals so it does not become such a bottleneck as it would if I had read it before each trace call was done. At first I thought I should read from the registry in my stored procedures too. I could have used xp_regread for reading from the registry so it wouldn’t have been hard, but my guess was that it would slow down the tracing. I couldn’t come up with a really good cache solution for how to store the registry value for my stored procedures for a couple of minutes or so. Instead I decided to let the developer go in directly and comment/uncomment code in the stored procedures such as jnskTraceBegin, jnskTraceEnd and so on, or only in jnskTraceWrite if all calls are made through that one.

I keep an instance of all the stored procedures mentioned above in every database. That way, I can start tracing in just one specific database and not have it started for all the databases of that particular SQL Server instance.


If you let jnskTraceBegin and so on do the trace calls directly, then you have a simple way of filtering, such as only allowing trace calls of a certain type to be done. It will also give you a small performance gain since you skip calling the jnskTraceWrite procedure.

On the other hand, I find it easier to always use jnskTraceWrite so I use this most often. This means I only have one place to add all my pre-filtering, such as only being interested in the trace calls from three specific stored procedures or only being concerned about "Msg" trace calls with a certain variable and so on.

And when you don’t want to have tracing activated, as I said before, you just comment out the code in jnskTraceWrite and optionally in jnskTraceBegin and so on.


There are several other options for tracing stored procedures and I will briefly discuss some of these below.

  • PRINT statements

    I quite often use PRINT statements in jnskTraceWrite or in jnskTraceBegin and so on to get the output directly in SQL Server Query Analyzer instead of in another tool. This is handy when you focus on just the stored procedures. You could use the following in jnskTraceBegin:

    PRINT CAST('Begin' AS CHAR(8)) +
    CAST(@vstrSource AS CHAR(50))
  • To do the same thing in jnskTraceWrite, you should use a CASE clause to decide which type of trace call was used. For example @vintTraceType = 2 should be converted to 'Begin'.

  • Another positive effect of using PRINT like this is that you will find the information in ADO’s Errors collection so you can inspect it from there too. (Observe that if you don’t use SET NOCOUNT ON, you will only find the first PRINT statement in the Errors collection if you use SQLOLEDB instead of MSDASQL as the OLE DB driver.)
  • SQL Server Profiler

    This is a really powerful tool and you might think that you don’t need a custom tracing solution when you have this. In my opinion I see situations when both are needed. Especially when it comes to projects where stored procedure isn’t the only thing that has been used, but COM+ components too. (Quite common, right?)

    On the other hand, it should be possible to use calls to sp_trace_generateevent with user-configured events from jnskTraceWrite to use SQL Server Profiler as the client tool for the tracing. SQL Server Profiler is of course a much better and more capable client tool than my quick hack jnskTraceView. To take this to the extreme, jnskTraceWrite could be called from ASP and the COM+ components too, to collect all trace information in SQL Server Profiler. My guess is that this isn’t a good solution since it probably adds a lot of overhead to the system, but I haven’t tried it.
  • Save trace calls to a table in SQL Server

    The drawback is probably the same as with calling sp_trace_generateevent from everywhere (I mean from ASP and COM+ components too). There will be too much effect on the system and it’s important to have as little as possible of that.
  • T-SQL Debugger

    In SQL Server 2000, a T-SQL Debugger is integrated with SQL Server Query Analyzer. This works like a charm. But once again, you need different types of tools for different problems and in different phases of the project.

    BOL tells us that we should take care not to use the T-SQL debugger on a production server. The debugger can lock certain resources.
  • Write to a file

    I will discuss this further in the following parts of this article.
  • Writing to Visual Studio Analyzer (VSA)

    Finally, it’s possible to write messages to VSA, but I decided a year ago to skip that at that time and I haven’t changed my mind yet. If you ask around you will probably find that very few people are using VSA as of today. Perhaps it will change with the next version.


Yes, I know that I’m definitely not inventing the concept of tracing. It’s discussed in several books and you will find solutions for it in several third-party products (as well as in the .NET Framework, as I mentioned earlier) and so on. Once again, I’d just like to stress the need for it, not my particular solution. (As you can see I’m doing my best to be a really nagging parent to my three-year-old son. Some of that is spilling over to my articles...)


It’s time for a couple of confessions. (I will add some more in the following articles.)

  • Problem with collecting output from several machines

    This is possible since the client tool can save the collected information to a file. Several of those files can then be opened from one instance of the client tool and the files will be merged. The problem with this is the synchronization of the clocks of all the machines. I will discuss this further and present a solution in the third part of the article.

    Another easy solution would be if I (or someone else) added an implementation variant so that I can request the output to be sent to a certain file on a shared drive instead of as calls to OutputDebugString. There are several things that should be done, but we must make money too, right?

  • Not possible to view trace output remotely

    See the bullet above.

    You should also take a look at DebugView from Sysinternals, see http://www.sysinternals.com/dbgview.htm. This tool allows you to collect ODS calls from a remote machine.

  • Overhead is definitely not zero

    Especially not when trace calls are activated and you are in listen mode. When you have deactivated trace calls, the overhead is quite low. I will present some diagrams for that in a later part of the article.

    The important thing to remember is to use trace calls in a smart manner. Don’t put them in a tight loop for example, if it’s not really important.

  • Not really usable from User-Defined Functions (UDF)

    In the current implementation of UDFs in SQL Server 2000, you can’t call stored procedures. You can call extended stored procedures so it’s possible to use my trace solution, but you get some trouble with the configurational aspects. I guess I could create two versions of the xp_jnsktracewrite, one empty and one that does the trace call. Then it’s possible to register the correct one depending on whether tracing should be activated or not, but now it starts to get messy. There are just too many places to change to start tracing and since the extended stored procedure is for the whole system, it won’t be possible to decide to use tracing for some databases and not to use it for others.

    UDFs are really cool, but they also have some rough edges in this first version that I hope will get fixed later.
  • Especially for SQL Server and not Oracle, DB2 and so on

    As I understand it, both extended stored procedures and COM Automation don’t work with Oracle, DB2 and so on, so the discussed mechanisms here are proprietary. On the other hand, I guess it should be easy to write a similar tool to use from, for example, Oracle that can call OutputDebugString.


There won’t be any source code today for the client tool and the COM component, but the reason for that is that I will upgrade both those for the second and third part of the article. You will find the tools mentioned in this part of the article so you can try them, and you will find the source code for the extended stored procedure.

Please observe that all use of the supplied tools is at your own risk. They were written very quickly and are given away as tools for inspirational purposes only. There will probably be upgraded versions of all the tools supplied with parts two and three of the article.

The tools have been tested with:

  • SQL Server 7 / Windows NT 4
  • SQL Server 2000 / Windows 2000

Client tool:

  • jnskTraceView.exe
    Requires that you have the VB6 environment or a VB6 created app installed on the system before. Uses Microsoft Common Dialog Control 6.0 and Microsoft Windows Common Controls 6.0.
  • jnskTrVw.ocx
    Register with regsvr32.

Stored procedure support:

  • jnskTrace.sql
    Execute this script in SQL Query Analyzer to get the stored procedures added to the current database.
  • xp_jnsktracewrite.dll
    Register in the master database with:
    '{your path}\xp_jnsktracewrite.dll'
  • xp_jnsktracewrite.zip
    The code for the xp.

COM component:

  • jnskTraceSrv.dll
    Register with regsvr32 (requires that you have the VB6 environment or a VB6 created app installed on the system before).


Hopefully I have inspired you to start using tracing in your applications. And not only in your ASP code and COM+ components, but also in your stored procedures. I’ve been working with stored procedures in SQL Server since 1995 and since then I have grown more and more eager to use them! With this comes a greater need for the same debuggability support I want to have in my other components.

Perhaps some of you think that there was too much talk about SQL Server and stored procedures and too little about COM+ in this part of the article? My guess is that at least 75% of the developers of configured COM+ components in the world are also using SQL Server. And for the other 25% percent, I will talk much more about COM+ in the second part of this article. Expect the second part of the article, but don’t hold your breath. ;-)

Special thanks to Dan Byström at Visual Design!

Jimmy Nilsson is the owner of the Swedish consultant company JNSK AB (www.jnsk.se). He has been working with system development since late 1988 (with VB since version 1.0) and, in recent years, he has specialized in component-based development, mostly in the Microsoft environment. He has also been developing and presenting courses in database design, object-oriented design, etc. at a Swedish University for six years. Jimmy is the author of ".NET Enterprise Design with Visual Basic .NET and SQL Server 2000" and he often speaks at VSLive conferences.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date