How to log in T-SQL

Logging from inside a SQL sproc would be better done to the database itself. T-SQL can write to files but it's not really designed for it.

I think writing to a log table would be my preference.

Alternatively, as you are using 2005, you could write a simple SQLCLR procedure to wrap around the EventLog.

Or you could use xp_logevent if you wanted to write to SQL log

You can either log to a table, by simply inserting a new row, or you can implement a CLR stored procedure to write to a file.

Be careful with writing to a table, because if the action happens in a transaction and the transaction gets rolled back, your log entry will disappear.

I solved this by writing a SQLCLR-procedure as Eric Z Beard suggested. The assembly must be signed with a strong name key file.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static int Debug(string s)
    {
        System.Diagnostics.Debug.WriteLine(s);
            return 0;
        }
    }
}

Created a key and a login:

USE [master]
CREATE ASYMMETRIC KEY DebugProcKey FROM EXECUTABLE FILE =
'C:\..\SqlServerProject1\bin\Debug\SqlServerProject1.dll'

CREATE LOGIN DebugProcLogin FROM ASYMMETRIC KEY DebugProcKey 

GRANT UNSAFE ASSEMBLY TO DebugProcLogin  

Imported it into SQL Server:

USE [mydb]
CREATE ASSEMBLY SqlServerProject1 FROM
'C:\..\SqlServerProject1\bin\Debug\SqlServerProject1.dll' 
WITH PERMISSION_SET = unsafe

CREATE FUNCTION dbo.Debug( @message as nvarchar(200) )
RETURNS int
AS EXTERNAL NAME SqlServerProject1.[StoredProcedures].Debug

Then I was able to log in T-SQL procedures using

exec Debug @message = 'Hello World'