<p>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?</p>

<p>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.</p>

[{'answer_id': 58716, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<p>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.</p>\n'}, {'answer_id': 58719, 'author': 'Unsliced', 'author_id': 2902, 'author_profile': 'https://Stackoverflow.com/users/2902', 'pm_score': 3, 'selected': False, 'text': "<p>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. </p>\n"}, {'answer_id': 58722, 'author': 'Galwegian', 'author_id': 3201, 'author_profile': 'https://Stackoverflow.com/users/3201', 'pm_score': 3, 'selected': False, 'text': '<p>I think writing to a log table would be my preference.</p>\n\n<p>Alternatively, as you are using 2005, you could write a simple SQLCLR procedure to wrap around the EventLog.</p>\n\n<p>Or you could use <strong>xp_logevent</strong> if you wanted to write to SQL log</p>\n'}, {'answer_id': 58723, 'author': 'Eugene Yokota', 'author_id': 3827, 'author_profile': 'https://Stackoverflow.com/users/3827', 'pm_score': 2, 'selected': False, 'text': '<p>There\'s the <a href="http://msdn.microsoft.com/en-us/library/ms176047%28SQL.90%29.aspx" rel="nofollow noreferrer">PRINT</a> command, but I prefer logging into a table so you can query it.</p>\n'}, {'answer_id': 59017, 'author': 'hollystyles', 'author_id': 2083160, 'author_profile': 'https://Stackoverflow.com/users/2083160', 'pm_score': 0, 'selected': False, 'text': '<p>You could use output variables for passing back messages, but that relies on the proc executing without errors.</p>\n\n<pre><code>create procedure usp_LoggableProc \n\n@log varchar(max) OUTPUT \n\nas\n\n-- T-SQL statement here ...\n\nselect @log = @log + \'X is foo\'\n</code></pre>\n\n<p>And then in your ADO code somehwere:</p>\n\n<pre><code>string log = (string)SqlCommand.Parameters["@log"].Value;\n</code></pre>\n\n<p>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:</p>\n\n<pre><code>RAISERROR(\'X is Foo\', 10, 1)\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 172808, 'author': 'Pittsburgh DBA', 'author_id': 10224, 'author_profile': 'https://Stackoverflow.com/users/10224', 'pm_score': 1, 'selected': False, 'text': "<p>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.</p>\n\n<p>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.</p>\n"}, {'answer_id': 545415, 'author': 'Jonas Engman', 'author_id': 4164, 'author_profile': 'https://Stackoverflow.com/users/4164', 'pm_score': 4, 'selected': True, 'text': "<p>I solved this by writing a SQLCLR-procedure as Eric Z Beard suggested. The assembly must be signed with a strong name key file.</p>\n\n<pre><code>using System;\nusing System.Data;\nusing System.Data.SqlClient;\nusing System.Data.SqlTypes;\nusing Microsoft.SqlServer.Server;\n\npublic partial class StoredProcedures\n{\n    [Microsoft.SqlServer.Server.SqlProcedure]\n    public static int Debug(string s)\n    {\n        System.Diagnostics.Debug.WriteLine(s);\n            return 0;\n        }\n    }\n}\n</code></pre>\n\n<p>Created a key and a login:</p>\n\n<pre><code>USE [master]\nCREATE ASYMMETRIC KEY DebugProcKey FROM EXECUTABLE FILE =\n'C:\\..\\SqlServerProject1\\bin\\Debug\\SqlServerProject1.dll'\n\nCREATE LOGIN DebugProcLogin FROM ASYMMETRIC KEY DebugProcKey \n\nGRANT UNSAFE ASSEMBLY TO DebugProcLogin  \n</code></pre>\n\n<p>Imported it into SQL Server:</p>\n\n<pre><code>USE [mydb]\nCREATE ASSEMBLY SqlServerProject1 FROM\n'C:\\..\\SqlServerProject1\\bin\\Debug\\SqlServerProject1.dll' \nWITH PERMISSION_SET = unsafe\n\nCREATE FUNCTION dbo.Debug( @message as nvarchar(200) )\nRETURNS int\nAS EXTERNAL NAME SqlServerProject1.[StoredProcedures].Debug\n</code></pre>\n\n<p>Then I was able to log in T-SQL procedures using</p>\n\n<pre><code>exec Debug @message = 'Hello World'\n</code></pre>\n"}, {'answer_id': 1765319, 'author': 'Steve D', 'author_id': 214836, 'author_profile': 'https://Stackoverflow.com/users/214836', 'pm_score': 1, 'selected': False, 'text': "<p><em>For what it's worth, I've found that when I don't assign an InfoMessage handler to my SqlConnection:</em></p>\n\n<pre><code>sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(MySqlConnectionInfoMessageHandler);\n</code></pre>\n\n<p><em>where the signature of the InfoMessageHandler looks like this:</em></p>\n\n<pre><code>MySqlConnectionInfoMessageHandler(object sender, SqlInfoMessageEventArgs e)\n</code></pre>\n\n<p><em>then my PRINT statements in my Stored Procs do not appear in DbgView.</em></p>\n"}, {'answer_id': 1905922, 'author': 'Daniel Pavic', 'author_id': 231908, 'author_profile': 'https://Stackoverflow.com/users/231908', 'pm_score': 0, 'selected': False, 'text': '<p>You may want to check <a href="http://log4tsql.codeplex.com/" rel="nofollow noreferrer" title="Raycoon Log4TSQL">Log4TSQL</a>. 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.</p>\n'}, {'answer_id': 60612988, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>Use cmd commands with cmdshell</p>\n\n<p>I found this while searching for an answer to this question.\n<a href="https://www.databasejournal.com/features/mssql/article.php/1467601/A-general-logging-t-sql-process-to-write-to-txt-files.htm" rel="nofollow noreferrer">https://www.databasejournal.com/features/mssql/article.php/1467601/A-general-logging-t-sql-process-to-write-to-txt-files.htm</a></p>\n\n<pre><code>select @cmdtxt = "echo " + @logEntry + " &gt;&gt; drive:\\path\\filename.txt"\nexec master..xp_cmdshell @cmdtxt\n</code></pre>\n'}, {'answer_id': 69202552, 'author': 'Curt', 'author_id': 1754010, 'author_profile': 'https://Stackoverflow.com/users/1754010', 'pm_score': 0, 'selected': False, 'text': "<p>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.</p>\n<p>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.</p>\n<ol>\n<li><p>Create a linked server to the same SQL instance, using the login's\nsecurity context.   In my case, the simplest method was to use the\nlocalhost loop address, 127.0.0.1</p>\n</li>\n<li><p>Set the linked server to enable RPC, and to NOT &quot;Enable Promotion of\nDistributed Transactions&quot;.   This means that calls through that\nserver will take place outside of your transaction context.</p>\n</li>\n<li><p>In your logging procedure, (I have an example excerpted below) write\nto the log table using the procedure through loopback linked server\nif you are in a     transaction.  You can write to it the usual way\nif your are not.   Writing though the linked server is considerably\nslower than direct DML.</p>\n</li>\n</ol>\n<p>Voila!  My in-process logging survives the rollback, and I can find out what's happening internally when things are going south.</p>\n<p>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.</p>\n<pre><code>USE TX\nGO\n\nCREATE PROCEDURE dbo.LogError(@errorSource Varchar(32), @msg Varchar(400))\nAS BEGIN\n    SET NOCOUNT ON\n       IF @@TRANCOUNT &gt; 0 \n            EXEC [127.0.0.1].TX.dbo.LogError @errorSource, @msg \n       ELSE\n           INSERT INTO TX.dbo.ErrorLog(source_module, message)\n               SELECT @errorSource, @msg\n END\n GO\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/58709', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4164/']