如何登录 T-SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/58709/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to log in T-SQL
提问by Jonas Engman
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?
我正在使用 ADO.NET 访问 SQL Server 2005，并希望能够从我正在调用的 T-SQL 存储过程内部进行登录。这有可能吗？
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.
使用 ADO.NET 时，我无法看到“打印”语句的输出，并且因为我只想将日志记录用于调试，理想的解决方案是从 SysInternals 向 DebugView 发出消息。
采纳答案by Jonas Engman
I solved this by writing a SQLCLR-procedure as Eric Z Beard suggested. The assembly must be signed with a strong name key file.
我按照 Eric Z Beard 的建议通过编写 SQLCLR 过程解决了这个问题。程序集必须使用强名称密钥文件进行签名。
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:
将其导入 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
然后我就可以使用登录 T-SQL 程序了
exec Debug @message = 'Hello World'
回答by Galwegian
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.
或者，当您使用 2005 时，您可以编写一个简单的 SQLCLR 过程来环绕 EventLog。
Or you could use xp_logeventif you wanted to write to SQL log
或者，如果您想写入 SQL 日志，可以使用xp_logevent
回答by Eric Z Beard
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.
您可以通过简单地插入新行来记录到表，也可以实现 CLR 存储过程以写入文件。
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.
写入表时要小心，因为如果操作发生在事务中并且事务被回滚，您的日志条目将消失。
回答by Unsliced
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.
从 SQL sproc 内部记录日志会更好地记录到数据库本身。T-SQL 可以写入文件，但它并不是真正为它设计的。
回答by Eugene Yokota
回答by Steve D
For what it's worth, I've found that when I don't assign an InfoMessage handler to my SqlConnection:
对于它的价值，我发现当我没有为我的 SqlConnection 分配一个 InfoMessage 处理程序时：
sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(MySqlConnectionInfoMessageHandler);
where the signature of the InfoMessageHandler looks like this:
InfoMessageHandler 的签名如下所示：
MySqlConnectionInfoMessageHandler(object sender, SqlInfoMessageEventArgs e)
then my PRINT statements in my Stored Procs do not appear in DbgView.
那么我存储过程中的 PRINT 语句不会出现在 DbgView 中。
回答by Pittsburgh DBA
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.
您可以从存储过程中将行写入日志表。正如其他人所指出的那样，您可以不遗余力地使用 CLR 或 xp_logevent 写入一些文本文件或其他日志，但似乎您需要比实际使用更多的容量。
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.
当事务失败时，就会出现棘手的情况（而正是这些情况才是您真正需要日志的）。由于在这些事务期间发生的任何日志记录都将与它们所属的事务一起回滚，因此最好有一个日志记录 API，您的客户端可以使用它来记录错误。这可以是一个简单的 DAL，它可以记录到同一数据库或共享的数据库。
回答by Daniel Pavic
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.
您可能需要检查Log4TSQL。它为 SQL Server 2005 - 2008 中的存储过程和触发器提供数据库日志记录。您可以在每个过程/触发器的基础上设置单独的、独立的日志级别。
回答by la_1989
Use cmd commands with cmdshell
在 cmdshell 中使用 cmd 命令
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
我在寻找这个问题的答案时发现了这个。 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
回答by hollystyles
You could use output variables for passing back messages, but that relies on the proc executing without errors.
您可以使用输出变量来传回消息，但这依赖于 proc 无错误执行。
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:
然后在你的 ADO 代码中：
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 使用您需要的信息创建您自己的自定义错误，这些信息将通过 ADO 代码中通常的 SqlException Errors 集合提供给您：
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.
嗯，但是是的，不能不感觉只是为了调试，在你的情况下，只需像其他人建议的那样将 varchar 消息插入到错误表中，并在调试时从中选择 *。
