How to log in T-SQL

Question

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.

Just debugging information.like "X is Foo" – Jonas Engman Sep 12 '08 at 11:55 — Jonas Engman, Sep 12 '08 at 11:55

Jonas Engman · Accepted Answer · 2009-02-13T10:24:07.083

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'

score 9 · Answer 2 · answered Sep 12 '08 at 11:23

9

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

answered Sep 12 '08 at 11:23

Galwegian

41,475
16
112
158

score 7 · Answer 3 · answered Sep 12 '08 at 11:20

7

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.

answered Sep 12 '08 at 11:20

Eric Z Beard

37,669
27
100
145

3

What 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. – HLGEM Nov 19 '09 at 18:07

score 5 · Answer 4 · answered Sep 12 '08 at 11:22

5

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.

answered Sep 12 '08 at 11:22

Unsliced

10,404
8
51
81

score 3 · Answer 5 · answered Sep 12 '08 at 11:24

3

There's the PRINT command, but I prefer logging into a table so you can query it.

answered Sep 12 '08 at 11:24

Eugene Yokota

94,654
45
215
319

score 1 · Answer 6 · answered Oct 05 '08 at 23:06

You can write rows to a log table from within a stored procedure. As others have indicated, you could go out of your way to write to some text file or other log with CLR or xp_logevent, but it seems like you need more volume than would be practical for such uses.

The tough cases occur (and it's these that you really need your log for) when transactions fail. Since any logging that occurs during these transactions will be rolled back along with the transaction that they are part of, it is best to have a logging API that your clients can use to log errors. This can be a simple DAL that either logs to the same database, or to a shared one.

score 1 · Answer 7 · edited Feb 24 '12 at 19:22

1

For what it's worth, I've found that when I don't assign an InfoMessage handler to my SqlConnection:

sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(MySqlConnectionInfoMessageHandler);

where the signature of the InfoMessageHandler looks like this:

MySqlConnectionInfoMessageHandler(object sender, SqlInfoMessageEventArgs e)

then my PRINT statements in my Stored Procs do not appear in DbgView.

edited Feb 24 '12 at 19:22

Taryn

242,637
56
362
405

answered Nov 19 '09 at 18:04

Steve D

23
3

This is easily the best answer of the lot. – Contango Feb 05 '17 at 22:43

score 0 · Answer 8 · edited Jan 04 '13 at 05:19

0

You may want to check Log4TSQL. It provides Database-Logging for Stored Procedures and Triggers in SQL Server 2005 - 2008. You have the possibility to set separate, independent log-levels on a per Procedure/Trigger basis.

edited Jan 04 '13 at 05:19

Rikesh

26,156
14
79
87

answered Dec 15 '09 at 08:18

Daniel Pavic

1
1

What is an architecture that used behind? How LOG4SQL saves log data during rollbacks? – Roman Pokrovskij Jan 09 '14 at 12:40

hollystyles · Answer 9 · 2008-09-12T13:53:48.663

You could use output variables for passing back messages, but that relies on the proc executing without errors.

create procedure usp_LoggableProc 

@log varchar(max) OUTPUT 

as

-- T-SQL statement here ...

select @log = @log + 'X is foo'

And then in your ADO code somehwere:

string log = (string)SqlCommand.Parameters["@log"].Value;

You could use raiserror to create your own custom errors with the information that you require and that will be available to you through the usual SqlException Errors collection in your ADO code:

RAISERROR('X is Foo', 10, 1)

Hmmm but yeah, can't help feeling just for debugging and in your situation, just insert varchar messages to an error table like the others have suggested and select * from it when you're debugging.

score 0 · Answer 10 · answered Mar 10 '20 at 07:16

Use cmd commands with cmdshell

I found this while searching for an answer to this question. https://www.databasejournal.com/features/mssql/article.php/1467601/A-general-logging-t-sql-process-to-write-to-txt-files.htm

select @cmdtxt = "echo " + @logEntry + " >> drive:\path\filename.txt"
exec master..xp_cmdshell @cmdtxt

score 0 · Answer 11 · answered Sep 16 '21 at 04:57

I've been searching for a way to do this, as I am trying to debug some complicated, chained, stored procedures, all that are called by an external API, and which operate in the context of a transaction.

I'd been writing diagnostic messages into a logging file, but if the transaction rolls back, the new log entries disappear with the rollback. I found a way! And it works pretty well. And it has already saved me many, many hours of debugging time.

Create a linked server to the same SQL instance, using the login's security context. In my case, the simplest method was to use the localhost loop address, 127.0.0.1
Set the linked server to enable RPC, and to NOT "Enable Promotion of Distributed Transactions". This means that calls through that server will take place outside of your transaction context.
In your logging procedure, (I have an example excerpted below) write to the log table using the procedure through loopback linked server if you are in a transaction. You can write to it the usual way if your are not. Writing though the linked server is considerably slower than direct DML.

Voila! My in-process logging survives the rollback, and I can find out what's happening internally when things are going south.

I can't claim credit for thinking of this--I found the approach after some time with Google, but I'm so pleased with the result I felt like I had to share it.

USE TX
GO

CREATE PROCEDURE dbo.LogError(@errorSource Varchar(32), @msg Varchar(400))
AS BEGIN
    SET NOCOUNT ON
       IF @@TRANCOUNT > 0 
            EXEC [127.0.0.1].TX.dbo.LogError @errorSource, @msg 
       ELSE
           INSERT INTO TX.dbo.ErrorLog(source_module, message)
               SELECT @errorSource, @msg
 END
 GO

How to log in T-SQL

11 Answers11

Linked