SQL Server write to EventLog

Getting debug information from a complex stored procedure can sometimes be a challenge in sql server. Do you write to a log table or return the information as part of the query response? Why not use the event log?

Turns out writing to the event log from within a stored procedure is really easy:

DECLARE @@MESSAGE varchar(255)
set @@MESSAGE = 'executing sub query XXX'
EXEC xp_logevent 60000, @@MESSAGE, informational

AppInfoHandler: ASP.NET application and environment diagnostic tool

This is a cool little tool that I wrote a few years ago. At the time I was working for a government department and our web applications where deployed to a multi-tier web farm with ISA acting as a load balancer. Direct traffic to an individual box was not allowed so it was quite difficult to determine which server you were hitting, Making it next to impossible to diagnose intermittent deployment problems.

Enter the AppInfoHandler. AppInfoHandler is a diagnostic tool that when hit spews out a tonne of information about the application such as.

General
    Machine Name
    Server IP
    IIS Version 
    IIS Priority
    IIS Up Time
    .Net Version
    OS Version
    Service Identity
    Trust Level
    Server Name
    App Domain Id
    Physical Application Path
    Virtual Application Path
    Application Temp Path
    Temp Path
    Authenticated
    Secure Connection (https)
    User Identity Name
    User Host Address
    Impersonation Level
    Server Time
    Server Culture
    Server UI Culture
    Server Cores
    Server Memory
Loaded Assemblies
Server Variables (these are dynamically loaded from the request)
    ALL_HTTP
    ALL_RAW
    APPL_MD_PATH 
    APPL_PHYSICAL_PATH
    AUTH_TYPE
    AUTH_USER
    AUTH_PASSWORD 
    LOGON_USER
    REMOTE_USER
    CERT_COOKIE 
    CERT_FLAGS 
    CERT_ISSUER 
    CERT_KEYSIZE 
    CERT_SECRETKEYSIZE 
    CERT_SERIALNUMBER 
    CERT_SERVER_ISSUER 
    CERT_SERVER_SUBJECT 
    CERT_SUBJECT 
    CONTENT_LENGTH
    CONTENT_TYPE 
    GATEWAY_INTERFACE 
    HTTPS 
    HTTPS_KEYSIZE 
    HTTPS_SECRETKEYSIZE 
    HTTPS_SERVER_ISSUER 
    HTTPS_SERVER_SUBJECT 
    INSTANCE_ID 
    INSTANCE_META_PATH 
    LOCAL_ADDR
    PATH_INFO
    PATH_TRANSLATED
    QUERY_STRING 
    REMOTE_ADDR
    REMOTE_HOST
    REMOTE_PORT 
    REQUEST_METHOD
    SCRIPT_NAME
    SERVER_NAME
    SERVER_PORT
    SERVER_PORT_SECURE
    SERVER_PROTOCOL
    SERVER_SOFTWARE 
    URL
    HTTP_CONNECTION
    HTTP_ACCEPT
    HTTP_ACCEPT_ENCODING
    HTTP_ACCEPT_LANGUAGE
    HTTP_COOKIE
    HTTP_HOST
    HTTP_REFERER
    HTTP_USER_AGENTenter PC 6.0)
Session Variables (these are dynamically loaded from the session)
Cache Variables (these are dynamically loaded from the cache)
Application Variables (these are dynamically loaded from the application cache)

This data could easily be extended to include web.config values or the status of windows services etc. One of the great things about this tool is that its a single self contained dll so it can deployed once into the GAC/master web.config once and will work on any application on the server.

The original idea for this was based on cache manager written by Steven Smith.

If anyone has any feedback or features they'd like to see added please let me know.

Download the binary here or the source code here.

How to make AppInfoHandler work in your application:

1. Add a reference to the sfc.AppInfoHandler.dll

2. In your application web.config add this to you system.web config section
    <httphandlers>
        <add path="AppInfo.axd" type="sfc.AppInfoHandler.AppInfo,sfc.AppInfoHandler" verb="*"/>
    </httphandlers>

3. Run your application and navigate to "http://[your application]/AppInfo.axd - Its that easy

How to redirect Console and Debug output to your UI

How many times have you been in your test environment trying to understand whats happening with no debugger to call on.

I find this especially frustrating in WPF where binding errors fail silently. Log files are a drag - wouldn't it be cool if you could pipe the console/debug output to somewhere in your UI?

It turns out this is pretty easy. Console output can be redirected to a stream

You can easily subclass TextWriter to pipe the stream output to a textbox for example

            // Instantiate the writer 

            TextWriter _writer = new TextBoxStreamWriter(txtMessage); 

            // Redirect the out Console stream 

            Console.SetOut(_writer); 

            Console.WriteLine("Now redirecting console output to the text box");

    public class TextBoxStreamWriter : TextWriter

    {

        TextBox _output = null;

        public TextBoxStreamWriter(TextBox output)

        {

            _output = output;

        }

        public override void Write(char value)

        {

            base.Write(value);

            _output.Dispatcher.BeginInvoke(new Action(() =>

                    {

                        _output.AppendText(value.ToString());

                    })

            ); // When character data is written, append it to the text box. 

        }

        public override Encoding Encoding

        {

            get { return System.Text.Encoding.UTF8; }

        }

    }

Undoing what you've done.

            StreamWriter standardOutput = new StreamWriter(Console.OpenStandardOutput());

            standardOutput.AutoFlush = true;

            Console.SetOut(standardOutput);

Redirecting debug output is even easier as it offers a lot of flexibility via tracelisteners out of the box

In this example I’m leverging the code from above to send debug output to the console and hence to my textbox

            Debug.Listeners.Add(new TextWriterTraceListener(Console.Out));

            Debug.WriteLine("Now redirecting debug output to the text box");