如何登录 T-SQL

2022-01-23 00:00:00 logging sql tsql sql-server ado.net

我正在使用 ADO.NET 访问 SQL Server 2005，并且希望能够从我正在调用的 T-SQL 存储过程内部进行日志记录.这有可能吗?

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 时，我无法看到打印"语句的输出，因为我只想将日志记录用于调试，理想的解决方案是从 SysInternals 向 DebugView 发出消息.

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.

推荐答案

我按照 Eric Z Beard 的建议编写了一个 SQLCLR 过程来解决这个问题.程序集必须使用强名称密钥文件进行签名.

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:..SqlServerProject1inDebugSqlServerProject1.dll'

CREATE LOGIN DebugProcLogin FROM ASYMMETRIC KEY DebugProcKey 

GRANT UNSAFE ASSEMBLY TO DebugProcLogin

将其导入 SQL Server:

Imported it into SQL Server:

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

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

然后我能够使用

exec Debug @message = 'Hello World'

相关文章