How to log in T-SQL
Solution 1
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'
Solution 2
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
Solution 3
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.
Solution 4
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.
Solution 5
There's the PRINT command, but I prefer logging into a table so you can query it.
Jonas Engman
Updated on September 16, 2021Comments
-
Jonas Engman over 2 years
I'm using ADO.NET to access SQL Server 2005 and would like to be able to log from inside the T-SQL stored procedures that I'm calling. Is that somehow possible?
I'm unable to see output from the 'print'-statement when using ADO.NET and since I want to use logging just for debuging the ideal solution would be to emit messages to DebugView from SysInternals.
-
dburges over 14 yearsWhat we did to avoid the problem of the logging disappearing is to write log entries to a table variable then insert the table variable data into the log table after the transaction is committed or rolled back.
-
Roman Pokrovskij over 10 yearsWhat is an architecture that used behind? How LOG4SQL saves log data during rollbacks?
-
Contango over 7 yearsThis is easily the best answer of the lot.