Trace Messages Part I: Managing Application Sessions

Welcome to the third issue of the new Coding-in-SQL Newsletter.  In case you missed the news, this newsletter has evolved from the T-SQL UDF of the Week.  The name change reflects a shift in focus from strictly UDFs to all types of coding for SQL Server.   All issues of the first two volumes are on line and you can find the Table of Contents at: http://www.novicksoftware.com/udfofweek/udfofweekarchive.htm

The newsletter is now available in HTML.  The HTML layout is much easier to read and the SQL is colorized almost the way that Query Analyzer does it.  To change your subscription to HTML, follow the link at the bottom of the newsletter that says "change profile".  Select the HTML radio button and press the "Update Subscription" button.  I think you'll be happy with what you see.  You can change the subscription back if you don't like the HTML.

Before getting into the theme, I'd like to alert you to an article about a SQL Server 2005 (Yukon) topic that I published earlier this week.  The article, Creating a User Defined Aggregate with SQL Server 2005, shows how to create a user defined aggregate (udagg) that takes the Product of a column.

The current theme is logging messages so that they get to the proper human.  The first two issues for the theme discussed the SQL Server ErrorLog and the SQL Server Agent ErrorLog.  Both facilities are places where SQL Server places messages for DBAs and developers.  Both can be very useful.   But what about messages from user code such as a stored procedure?  Where should it go?

Once place for user code messages is the SQL Server ErrorLog.  The RAISERROR statement has an option, WITH LOG, that directs SQL Server to place the message in its ErrorLog.  That also places it in the Windows Application Event log.  Here’s a sample batch to illustrate:

DECLARE @Msg varchar(255)
SET @Msg = 'Invalid Widget Code' 
RAISERROR (@MSG, 16, 1) WITH LOG

There are disadvantages to using the SQL Server ErrorLog:

·         It isn’t easy to search.  See Vol 3 Issue 1 for a sproc that searches the SQL Server ErrorLog.

·         It disappears after six new error logs are created.

·         Access is limited to those with the sysadmin role. In a shared web hosting environment I usually don’t have the sysadmin role. Thus I can’t read the ErrorLog

Given the disadvantages of the ErrorLog, an alternative is welcome.

I’ve been writing a lot of .Net code in the last couple of years and have become enamored of the TRACE capability that’s built in to .Net.  The TRACE object lets the programmer add lines of trace output to any program through the TRACE.WRITELINE method.  Trace messages are sent to one or more trace listeners which present them to the user in different ways. I generally write about 10 percent of my .Net code as TRACE.WRITELINE statements.

Before working with traces and showing you the code that writes trace output we have to discuss another concept that I use for organizing trace output.  The concept is the session.  For me, sessions represent a single use of a program.  The program might be a Windows Forms program, SQL Agent Job, a stored procedure, or an ASP.Net on-line user session.

I store information about sessions in the AppSession table, which contains the overall session information.  Here’s the CREATE TABLE script for AppSession.  The use for each column is described in Table 1 that follows: 

CREATE TABLE [AppSession] (
       [SessionID] [int] IDENTITY (1, 1) NOT NULL ,
       [ApplicationID] [int] NOT NULL
              CONSTRAINT [DF_AppSession_ApplicationID] DEFAULT (1),
       [SessionGUID] [uniqueidentifier] NOT NULL
              CONSTRAINT [DF_AppSession_SessionGUID] DEFAULT (newid()),
       [ProgramTypeCD] [varchar] (16)  NOT NULL ,
       [ClientProcessID] [int] NOT NULL ,
       [ClientComputerName] [varchar] (32)  NULL ,
       [ClientProgramName] [varchar] (128)  NOT NULL ,
       [ClientUSERID] [varchar] (20)  NOT NULL ,
       [ExecutedOnComputerName] [varchar] (32)  NOT NULL ,
       [USERID] [varchar] (20)  NULL ,
       [CreatedDT] [datetime] NOT NULL
             CONSTRAINT [DF_AppSession_CreatedDT] DEFAULT (getdate()),
       [LastUpDT] [datetime] NOT NULL ,
       [DurationDUR] [datetime] NULL ,
       CONSTRAINT [PK_AppSession] PRIMARY KEY  CLUSTERED
       (
              [SessionID]
       ) WITH  FILLFACTOR = 90  ON [PRIMARY]
) ON [PRIMARY]
GO

Valid program type codes for the ProgramTypeCD column are stored in the AppSessionProgramTypeCD table.  Here’s a script to create it and populate it with the values that I normally use: 

CREATE TABLE AppSessionProgramTypeCD (
       ProgramTypeCD varchar (32) NOT NULL ,
       [Description] nvarchar (255) NOT NULL ,
       CONSTRAINT PK_AppSessionProgramTypeCD
              PRIMARY KEY  CLUSTERED (ProgramTypeCD)
)
GO

SET QUOTED_IDENTIFIER OFF
GO

INSERT INTO AppSessionProgramTypeCD VALUES ("ASP.NET","ASP.Net Session")
INSERT INTO AppSessionProgramTypeCD VALUES ("SQLSP","Stored Procedure")
INSERT INTO AppSessionProgramTypeCD VALUES ("SQLJOB"
                        ,"Job run by SQL Server Agent")
INSERT INTO AppSessionProgramTypeCD VALUES ("WINFORMS"
                        ,"Windows 32 bit .Net CLR Application")
GO

To make my coding life easier, I have a couple of stored procedures that kick off a session by creating the AppSession row. The first of these is usp_AppSession_Create, which handles the general case where I want to supply tall the parameters.  Here’s the CREATE PROC script:

CREATE PROCEDURE dbo.usp_AppSession_Create

    @ApplicationID int = 1 -- Application ID
  , @ProgramTypeCD varchar(32) = 'SQLSP' -- Type of program
  , @ClientProcessID int = 0 -- Process Identifier
  , @ClientComputerName varchar(32) -- Which Computer started this session.
  , @ClientProgramName varchar(128) -- Program starting session
  , @ClientUserID varchar(20) -- User ID of end user.
  , @ExecutedOnComputer varchar(32) -- Computer name where run
  , @UserID varchar(20) -- User id on this system.
  , @SessID int OUTPUT -- New Session ID

/* Creates an AppSession row based on the input parameters.
*
* Example:
DECLARE @RC int -- Return code
      , @ProgName varchar(128)
      , @Session_User varchar(20)
      , @Host_Name varchar(32)
      , @User_Name varchar(128)
      , @SessID int

SELECT @ProgName = APP_NAME()
     , @SEssion_User = SESSION_USER
     , @Host_Name = host_name()
     , @User_Name = User_Name()

EXEC @RC =  usp_AppSession_Create 0
                            , 'Test Batch 1'
                            , @@SPID -- ClientProcessID
                            , @HOST_NAME -- ClientComputerName
                            , @ProgName
                            , @USER_NAME
                            , @@SERVERNAME
                            , @SESSION_USER
                                             , @SessID OUTPUT
PRINT 'RC = ' + CONVERT(varchar, @RC)
PRINT 'New Session ID=' + CONVERT(varchar,@SessID)
*     
* © Copyright 2005 Andrew Novick http://www.NovickSoftware.com
* You may use this code in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as Coding in SQL Newsletter Vol 3 #3 1/19/05   
http://www.NovickSoftware.com/coding-in-sql.htm
****************************************************************/
AS

DECLARE @myError int, @myRowCount int

SET NOCOUNT ON

-- Create the AppSession row
INSERT INTO AppSession
                (ApplicationID, SessionGUID, ProgramTypeCD, ClientProcessID
                        , ClientComputerName, ClientProgramName
                        , ClientUSERID, ExecutedOnComputerName
                        , USERID, CreatedDT, LastUpDT)
         VALUES (@ApplicationID, newid(), @ProgramTypeCD, @ClientProcessID
                        , @ClientComputerName, @ClientProgramName
                        , @ClientUserID, @ExecutedOnComputer
                        , @UserID, GetDate(), GetDate())

SELECT @myError = @@Error
     , @SessID = SCOPE_IDENTITY()

RETURN @myError
GO

One has to supply quite a few parameters to usp_AppSession_Create and I found myself copying the script in the comment header several times.  So for T-SQL stored procedures, I have another proc that fills in some of the parameters. usp_AppSession_SQLJOB takes only four parameters:

CREATE PROCEDURE dbo.usp_AppSession_SQLJOB

    @ApplicationID int = 1 -- Application ID
  , @ClientProgramName varchar(128) = NULL -- Program
                          -- that is starting the session     
  , @ClientUserID varchar(20) -- User ID of end user.
  , @SessID int OUTPUT -- New Session ID

/* Create an AppSession for a SQLJOB. 
* Used by stored procedures that are run directly by SQL Server
* Agent.
*
* Test:
DECLARE @RC int, @SessID int
exec @RC = usp_AppSession_SQLJOB 1
                      , 'TEST_SCRIPT_Usp_AppSession_SQLJOB'
                      , 'TESTUSER', @SessID OUTPUT
PRINT 'RC = ' + coalesce(convert(varchar(12), @rc), '<null')
  + ' Sess=' + coalesce(convert(varchar(12), @sessID), '<null>')
select * from AppSession WHERE SessionID = @SessID
*     
* © Copyright 2005 Andrew Novick http://www.NovickSoftware.com
* You may use this code in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as Coding in SQL Newsletter Vol 3 #3 1/19/05   
http://www.NovickSoftware.com/coding-in-sql.htm
****************************************************************/
AS

SET NOCOUNT ON

DECLARE @RC int -- Return code
      , @ProgName varchar(128)
      , @Session_User varchar(20)
      , @Host_Name varchar(32)
      , @User_Name varchar(128)

SELECT @ProgName = COALESCE(@ClientProgramName, APP_NAME())
     , @SEssion_User = SESSION_USER
     , @Host_Name = host_name()
     , @User_Name = User_Name()

EXEC @RC =  usp_AppSession_Create @ApplicationID
                                , 'SQLJOB'
                                , @@SPID -- ClientProcessID
                                , @HOST_NAME -- ClientComputerName
                                , @ProgName
                                , @USER_NAME
                                , @@SERVERNAME
                                , @SESSION_USER
                                , @SessID OUTPUT

RETURN @RC

GO

The session is used to organize trace messages and to differentiate messages that come from one instance of a program from those that come from other instances of the same program. 

When I started writing this issue I thought that I’d put everything about sessions and traces and cleaning up after ones self in one issue.  That’s not going to work so I’ve split the information into three issues:

• Part I: Application Session Management

• Part II: Writing Trace Information to a Table

• Part III: Cleaning up the Session and Trace information

I’ll defer an example of using usp_AppSession_SQLJOB until next issue.

As always, if you have comments about the newsletter and in particular, the new direction that I’m planning on taking, please let me know at coding-in-sql@novicksoftware.com.

Read the Previous Issue #2

Transact-SQL User-Defined Functions has been published!  Take a look at it now!

Do you have an interesting stored procedure, user-defined-function, or other sample code?  If you'd like to share them please send them in an email to: coding-in-sql@NovickSoftware.com  and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andy
Andrew Novick