Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


A Trace Tool, Part 1 : Page 2

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.



You will also need a tool for capturing all the trace calls. Below you can see a screen shot of my own such tool after it has captured a call from an ASP page to an ASP include, through three COM+ layers and then to a stored procedure. Look at the type column and you can see that first all the different layers are entered and then they are left. In the stored procedure a "Msg" trace call was also sent and the value of the parameter @vintId was inspected.

Fig 1 Screen shot of jnskTraceView

The tool is very simple, but there is still some stuff I’d like to describe further in the third part of this article. Before I write that part of the article, I will upgrade this tool. I wrote the current version early this year (2000), and it works, but I have several small things I’d like to (and must) fix real soon... Anyway, you will find this tool together with the rest of the tools discussed in this part in the download, see SUPPLEMENTAL FILES below.


I decided to use the Win32 API procedure OutputDebugString (ODS) as the mechanism for sending my trace calls. Those calls can then be collected by, for example, the Visual Studio Debugger but also by my client tool.

If you like to use the procedure in VB, you declare it this way:

Private Declare Sub OutputDebugString _
Lib "kernel32" Alias "OutputDebugStringA" _
(ByVal lpOutputString As String)

And then you can use it like this:

OutputDebugString "Hello World!"

In my tracing solution I will send a string with all the needed information separated by tabs. Since every piece of data will be in a specific "column" of the row, it’s easy to parse the row and do whatever I like to do with it.

A drawback of using ODS is that there are several other programs (such as SQL Server and IIS) that are calling the same procedure too. Therefore one of the filter options in the client tool is to only show the calls that were created by my routines.


Let’s leave the general stuff about the complete article and dig further into the details of how to add tracing to your stored procedures in SQL Server.

I have a collection of stored procedures named like this:

  • jnskTraceAssert
  • jnskTraceBegin
  • jnskTraceEnd
  • jnskTraceError
  • jnskTraceMsg
  • jnskTraceWrite

The signatures of the "Begin" procedure look like this:

(@vstrSource VARCHAR(255))

The only information that needs to be sent is the name of the stored procedure that was entered.

The signature of the "Msg" procedure is only slightly larger:

(@vstrSource VARCHAR(255),
@vstrMsg VARCHAR(255))

Here a parameter is added with the variable name and its value.

The last stored procedure, jnskTraceWrite, could be used by all the other trace procedures for centralizing the final call to OutputDebugString. Yes, I know that I can’t call the Win32 API directly from stored procedures, but there are other alternatives.


If I want to call OutputDebugString from my stored procedures there are several possibilities and I will discuss two of those a little further.

It’s quite simple to wrap the call in a COM component and then use COM Automation (or OLE Automation as SQL Server still calls it) from the stored procedures to instantiate the component and call a method. I have created a COM component called jnskTraceSrv and its only method has the following signature:

Public Sub jnskTrace _
(ByRef renuType As TraceType, _
ByRef rstrSource As String, _
ByRef rstrMsg As String, _
ByRef rstrUser As String, _
ByRef rstrEXEname As String, _
ByRef rstrIsInTransaction As String, _
ByRef rstrIsSecurityEnabled As String)

(The parameters are ByRef simply for performance reasons.)

Assume that all trace calls are channeled through the stored procedure jnskTraceWrite, then its implementation could look like this when COM Automation is used. (Observe that @@TRANCOUNT is used instead of "yes" or "no" as the value for the "In Tx"-column of the client tool.)

(@vintTraceType INT,
@vstrSource VARCHAR(255),
@vstrMsg VARCHAR(255)) AS
  @intObj INT,
  @intHr INT,
  @strTran VARCHAR(3),
  @strUser VARCHAR(30)

  --Build the info that is to be sent...
  SET @strTran =
  SET @strUser = SYSTEM_USER

  EXEC @intHr = sp_OACreate
  'jnskTraceSrv.Trace', @intObj OUTPUT
  IF @intHr <> 0 BEGIN
    PRINT 'Problem at creation...'
    RETURN 1

  EXEC @intHr = sp_OAMethod @intObj,
  'jnskTrace', NULL, @vintTraceType,
  @vstrSource, @vstrMsg,
  @strUser, 'MS SQL', @strTran, ''
  IF @intHr <> 0 BEGIN
    PRINT 'Problem at execution...'
    RETURN 2

  EXEC @intHr = sp_OADestroy @intObj
  IF @intHr <> 0 BEGIN
    PRINT 'Problem at release...'
    RETURN 3

As you see, it’s quite easy to use COM Automation in SQL Server. Even so, you should be careful because you will of course take away resources that SQL Server could have used instead. (In my case I wrote the COM component in VB so there will be a couple of MBs occupied just for this small tool, if it is the only VB thing running on the SQL Server.)

You should also make sure you know that the source code is safe for the COM component. It will run in SQL Server’s address space so it can bring down SQL Server and it can also be used for a hacker attack. Having said that, no one will try out the COM component that I supplied with this part of the article... ;-)

In my opinion, COM Automation in SQL Server is much more suitable when you have large granular calls that will take some time to run and which won’t be called frequently because there is quite a lot of overhead associated with the instantiation/destroy mechanism. Sending trace calls doesn’t fit that description at all. A better solution I think is to write an extended stored procedure instead.


SQL Server has support for another thing called ODS, but this time it means Open Data Services and not OutputDebugString. It’s an API that you can use for writing your own external SQL Server resources in, for example, C. There are a lot of extensions included with SQL Server that Microsoft has built as extended stored procedures.

In Visual C++ there is a wizard for creating an extended stored procedure. I’m mostly a VB and SQL guy so I asked my friend Dan Byström if he had an hour or so to help me with writing a couple of extended stored procedures, and xp_jnsktracewrite was one of them.

When I tested another extended stored procedure that we wrote, I found that even if I sent longer strings than 255 characters, the strings were truncated. It took me quite some time to figure out that the wizard for Visual C++ used outdated functionality for receiving parameters. It was also the case that I needed to change the header and lib files for the ODS support that was delivered with Visual C++ 6. It didn’t help to use the files from the latest Platform SDK, but using the samples from SQL Servers own installation CD and the header and lib files from the same place finally made it work. One more thing, I had to export a function that SQL Server 7 and 2000 call to see what version of the header files the extended stored procedure is using. (Search for "Creating Extended Stored Procedures" in Books OnLine (BOL) and read the document with the same title, to get more information about the last comment.)

Anyway, when those problems were solved and the extended stored procedure is installed in the master database, it’s very simple to use the xp from your stored procedures.

(@vintTraceType INT,
@vstrSource VARCHAR(255),
@vstrMsg VARCHAR(255)) AS
  @strUser VARCHAR(30)

  SET @strUser = SYSTEM_USER

  EXEC master..xp_jnsktracewrite
  @vintTraceType, @vstrSource,
  @vstrMsg, @strUser,

Several of the drawbacks of COM Automation apply to extended stored procedures too. I also think it’s harder to build extended stored procedures, but that is highly dependent on your background of course.

On the other hand, extended stored procedures have several advantages, and the most important is that they are faster than COM Automation. Especially when several small calls are needed. I measured a simple stored procedure that only did a single update and had "Begin" and "End" trace calls. When the trace calls were done with my extended stored procedure, I could execute the stored procedure almost twice as many times in a certain time frame as when COM Automation was used instead.

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