如何从SQL CLR函数中打印消息?

Is there an equivalent of

有一个等价物吗

PRINT 'hello world'

which can be called from CLR (C#) code?

可以从CLR (c#)代码调用哪个?

I'm trying to output some debug information in my function. I can't run the VS debugger because this is a remote server.

我尝试在函数中输出一些调试信息。我无法运行VS调试器，因为这是一个远程服务器。

Thanks!

谢谢!

5 个解决方案

#1

The answer is that you cannot do the equivalent of

答案是你不能做同样的事

PRINT 'Hello World'

from inside a [SqlFunction()]. You can do it however from a [SqlProcedure()] using

从一个[SqlFunction()]。您可以通过[SqlProcedure()]使用它

SqlContext.Pipe.Send("hello world")

This is consistent with T-SQL, where you would get the error "Invalid use of a side-effecting operator 'PRINT' within a function" if you stick a PRINT inside a function. But not if you do it from a stored procedure.

这与T-SQL是一致的，如果在函数中粘贴一个PRINT，就会出现“在函数中使用副作用操作符'PRINT'无效”的错误。但如果是从存储过程中执行，则不会。

For workarounds i suggest:

解决方法建议:

Use Debug.Print from your code, and attach a debugger to the SQL Server (I know this doesnt work for you as you explained).
使用调试。从代码中打印，并将调试器附加到SQL服务器(我知道这对您不适用，正如您所解释的)。
Save the messages in a global variable, for instance List<string> messages, and write another table-valued function that returns the contents of messages. Of course, the access to messages needs to be synchronized because several threads might try to access it at the same time.
将消息保存在全局变量中，例如List 消息，并编写另一个表值函数，该函数返回消息的内容。当然，对消息的访问需要同步，因为多个线程可能同时尝试访问它。
Move your code to a [SqlProcedure()]
将代码移动到[SqlProcedure()]
Add a parameter 'debug' that when =1 the function will return the messages as part of the returned table (assuming there is a column with text..)
添加一个“debug”参数，当=1时，函数将返回消息作为返回表的一部分(假设有一个带文本的列)。

#2

You should just be able to do:

你应该能够做到:

SqlContext.Pipe.Send("hello world");

If you are running this within a CLR UDF, SqlContext.Pipe will always be null as you discovered. Without a valid SqlPipe I don't believe you can do what you want.

如果您正在一个CLR UDF、SqlContext中运行它。正如您所发现的，管道将始终为空。没有有效的SqlPipe，我不相信您能做您想做的事情。

If this is purely for debugging purposes, you could always open a file within the managed code and write your output there. This requires that your assembly has EXTERNAL_ACCESS permission, however, and this in turn requires the database be marked as trustworthy. Not necessarily something that I would do or recommend.

如果这纯粹是为了调试目的，那么您总是可以在托管代码中打开一个文件，并在那里写入您的输出。但是，这要求程序集具有EXTERNAL_ACCESS权限，这反过来要求将数据库标记为可信的。不一定是我想做或推荐的。

#3

Ahh I see... Jsut to clarify: if you have a SqlFunction then SqlContext.Pipe is not available, however in an SqlProcedure it is and you can use Send() to write messages.

啊我明白了…要澄清的是:如果您有SqlFunction，那么就是SqlContext。管道不可用，但是在SqlProcedure中，您可以使用Send()来编写消息。

I still haven't found a way to output information from a SqlFunction aside from an exception message.

除了异常消息之外，我还没有找到从SqlFunction输出信息的方法。

#4

You can try to put those information through "xp_logevent" stored procedure. You can set your debug information as "information", "warning" or "error" at the different level. I also tried to put those debug/error information into event log, but that require a little bit configuration at security, which I doubt I can not use that at production.

您可以尝试通过“xp_logevent”存储过程放置这些信息。您可以将调试信息设置为不同级别的“信息”、“警告”或“错误”。我还试图将这些调试/错误信息放入事件日志中，但这需要在安全性上进行一点配置，我怀疑我不能在生产中使用它。

#5

SQLCLR Functions -- Scalar User-Defined Functions (UDFs), Table-Valued Functions (TVFs), User-Defined Aggregates (UDAs), and methods within User-Defined Types (UDTs) -- when using the Context Connection (i.e. ConnectionString = "Context Connection = true;"), are bound by most of the same restrictions that T-SQL functions are bound by, including not being able to PRINT or RAISERROR('message', 10, 1). However, you do have a few options.

SQLCLR函数标量用户定义函数(udf),表值函数(tvf),用户定义的聚合(uda)和方法在用户定义类型(udt)——当使用上下文连接(即ConnectionString =“上下文连接= true;”),受大多数相同的t - sql功能受限制,包括无法打印或RAISERROR(“消息”,10日1)。然而,你有几个选择。

Before we get to those options, it should be stated that:

在我们讨论这些选择之前，应该指出:

you don't need to switch to using a Stored Procedure. If want a function then stick with a function.

您不需要切换到使用存储过程。如果想要一个函数，就坚持一个函数。
adding a "debug" parameter and changing the output for this seems a bit extreme since UDFs (T-SQL and SQLCLR) functions do not allow for overloading. Hence the debug parameter will always be in the signature. If you want to trigger debugging, just create a temp table called #debug (or something like that) and test for via SELECT OBJECT_ID(N'tempdb..#debug'); using "Context Connection = true;" for the ConnectionString (which is fast and can be done in SAFE mode and is part of the same session so it can see the temp table). Get the result of that from if (SqlCommand.ExecuteScalar() == DBNull.Value).

添加一个“调试”参数并更改输出看起来有点极端，因为udf (T-SQL和SQLCLR)函数不允许重载。因此，调试参数将始终在签名中。如果您希望触发调试，只需创建一个名为#debug(或类似的东西)的临时表，并通过SELECT OBJECT_ID(N'tempdb. #debug')进行测试;使用“Context Connection = true”来表示ConnectionString(它速度很快，可以在安全模式下执行，并且是相同会话的一部分，因此可以看到temp表)。从if (SqlCommand.ExecuteScalar() == DBNull.Value)获取该结果。
please do not use a global (i.e. static) variable. that is far more complicated than necessary, and requires (typically) that the Assembly be set to UNSAFE, which should be avoided if at all possible.

请不要使用全局变量(即静态变量)。这远比必要的复杂得多，并且要求(通常)将程序集设置为不安全，如果可能的话，应该避免这种情况。

So, if you can at least set the assembly to EXTERNAL_ACCESS, then you have a few options. And doing this does not require setting the database to TRUSTWORTHY ON. That is a very common (and unfortunate) misunderstanding. You just need to sign the assembly (which is a good practice anyway), then create an Asymmetric Key (in [master]) from the DLL, then create a Login based on that Asymmetric Key, and finally grant the Login EXTERNAL ACCESS ASSEMBLY. After doing that (one time), you can do any of the following:

因此，如果您至少可以将程序集设置为EXTERNAL_ACCESS，那么您就有一些选择。这样做并不需要将数据库设置为可信的。这是一个非常常见的(也是不幸的)误解。您只需签署程序集(这是一个很好的实践)，然后从DLL中创建一个非对称密钥(在[master]中)，然后基于该非对称密钥创建一个登录，最后授予登录外部访问程序集。在这样做(一次)之后，你可以做以下任何一种:

write the messages to a file using File.AppendAllText (String path, String contents). Of course, if you don't have access to the file system then this isn't as helpful. If there is a shared drive on the network that can be accessed, then as long as the service account for the SQL Server service has permission to create and write files on that share, then this will work. If there is a share that the service account doesn't have permission to but your Domain / Active Directory account does, then you can wrap that File.AppendAllText call in:

使用文件将消息写入文件。AppendAllText(字符串路径，字符串内容)。当然，如果您没有访问文件系统的权限，那么这就没有那么有用了。如果网络上有一个可以访问的共享驱动器，那么只要SQL Server服务的服务帐户具有在该共享上创建和编写文件的权限，那么这个操作就可以工作。如果有一个共享服务帐户没有权限，但是您的域/活动目录帐户有权限，那么您可以打包该文件。AppendAllText调用:
```
using (WindowsImpersonationContext _Impersonate = 
                      SqlContext.WindowsIdentity.Impersonate())
{
   File.AppendAllText("path.txt", _DebugMessage);
    _Impersonate.Undo();
}
```
connect to SQL Server and write the messages to a table. It can be the current / local SQL Server or any other SQL Server. You can create a table in [tempdb] so that it is automatically cleaned up the next time SQL Server is restarted, but otherwise lasts until that time, or until you drop it. Making a regular / external connection allows you to do DML statements. Then you can select from the table as you are running the function.

连接到SQL Server并将消息写到表中。它可以是当前/本地SQL服务器或任何其他SQL服务器。您可以在[tempdb]中创建一个表，以便在下一次重新启动SQL Server时自动清理它，但其他情况将持续到该时间，或直到您删除它为止。做一个常规/外部连接可以让你做DML语句。然后可以在运行函数时从表中进行选择。
write the messages to an environment variable. Environment variables aren't exactly limited in size since Vista / Server 2008, though they don't really handle newlines. But any variable set from within .NET code will also survive until the SQL Server service is restarted. And you can append message by reading the current value and concatenating the new message to the end. Something like:

将消息写入环境变量。自Vista / Server 2008以来，环境变量的大小并没有受到严格限制，尽管它们并不真正处理换行。但是，从. net代码中设置的任何变量都将继续存在，直到SQL Server服务重新启动为止。您可以通过读取当前值并将新消息连接到末尾来附加消息。喜欢的东西:
```
{
  string _Current = System.Environment.GetEnvironmentVariable(_VariableName,
                                  EnvironmentVariableTarget.Process);

  System.Environment.SetEnvironmentVariable(
      _VariableName,
      _Current + _DebugMessage,
      EnvironmentVariableTarget.Process);
}
```

It should be noted that in each of these 3 cases, it is assumed that the testing is being done in a single-threaded manner. If the function will be running from multiple sessions at the same time, then you need a way to separate the messages. In that case, you can get the current "transaction_id" (all queries, even without a BEGIN TRAN are a transaction!) which should be consistent for any particular execution (across multiple uses in the same function as well as if the function is called per each row across multiple rows). You can use this value as a prefix for the messages if using the file or environment variable methods, or as a separate field if storing into a table. You can get the transaction by doing the following:

应该注意的是，在这三种情况中，假设测试是用单线程方式进行的。如果函数将同时从多个会话运行，那么您需要一种方法来分隔消息。在这种情况下，您可以获得当前的“transaction_id”(所有查询，即使没有BEGIN TRAN也是事务!如果使用文件或环境变量方法，可以将此值用作消息的前缀，如果存储到表中，则可以作为单独的字段。您可以通过以下步骤获得交易:

int _TransactionID;

using (SqlConnection _Connection = new SqlConnection("Context Connection = true;"))
{
    using (SqlCommand _Command = _Connection.CreateCommand())
    {
        _Command.CommandText = @"
SELECT transaction_id
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
";

        _Connection.Open();
        _TransactionID = (int)_Command.ExecuteScalar();
    }
}

Additional info on T-SQL and SQLCLR functions

关于T-SQL和SQLCLR函数的其他信息。

The following list was initially taken from the MSDN page for Create User-defined Functions (Database Engine) and then edited by me, as noted, to reflect the differences between T-SQL functions and SQLCLR functions:

以下列表最初取自MSDN页面，用于创建用户定义函数(数据库引擎)，然后由我编辑，如前所述，以反映T-SQL函数和SQLCLR函数之间的差异:

User-defined functions cannot be used to perform actions that modify the database state.

用户定义的函数不能用于执行修改数据库状态的操作。

User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.

用户定义函数不能包含以表为目标的输出到子句中。

User-defined functions can not return multiple result sets. Use a stored procedure if you need to return multiple result sets.

用户定义的函数不能返回多个结果集。如果需要返回多个结果集，请使用存储过程。

Error handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @@ERROR, or RAISERROR. [ Note: This is in terms of T-SQL, either native or submitted from a SQLCLR function. You can use try / catch / finally / throw in .NET code. ]

错误处理在用户定义的函数中受到限制。UDF不支持TRY…CATCH、@ error或RAISERROR。[注意:这是针对T-SQL的，可以是本机的，也可以是从SQLCLR函数中提交的。您可以使用try / catch / finally / throw in . net代码。]

SET statements are not allowed in a user-defined function.

在用户定义的函数中不允许设置语句。

The FOR XML clause is not allowed

不允许使用FOR XML子句

User-defined functions can be nested; ... The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. User-defined functions can be nested up to 32 levels.

用户定义的函数可以嵌套;…当被调用的函数开始执行时，嵌套级别会增加，当被调用的函数完成执行时，嵌套级别会减少。用户定义的函数可以嵌套到32层。

The following Service Broker statements cannot be included in the definition of a Transact-SQL user-defined function:

BEGIN DIALOG CONVERSATION

开始对话交谈

END CONVERSATION

结束谈话

GET CONVERSATION GROUP

得到交流小组

MOVE CONVERSATION

移动的谈话

RECEIVE

收到

SEND

发送

下面的服务代理语句不能包含在Transact-SQL用户定义函数的定义中:开始对话对话结束对话GET CONVERSATION GROUP MOVE CONVERSATION RECEIVE SEND。

The following pertains to both T-SQL functions and SQLCLR functions:

下面是T-SQL函数和SQLCLR函数:

Cannot use PRINT

不能使用打印

Cannot call NEWID() [ Well, unless you SELECT NEWID() from within a View. But within .NET code, you can use Guid.NewGuid(). ]

不能调用NEWID()[嗯，除非您从视图中选择NEWID()。但是在. net代码中，可以使用guide . newguid()。]

The following pertains only to T-SQL functions:

以下仅适用于T-SQL函数:

User-defined functions cannot call a stored procedure, but can call an extended stored procedure.

用户定义的函数不能调用存储过程，但可以调用扩展存储过程。

User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

用户定义函数不能使用动态SQL或临时表。表变量是允许的。

In contrast, SQLCLR functions can:

而SQLCLR函数可以:

Execute Stored Procedures, as long as they are read-only.

执行存储过程，只要它们是只读的。

Make use of Dynamic SQL (all SQL submitted from SQLCLR is ad hoc / dynamic by its very nature).

使用动态SQL(从SQLCLR提交的所有SQL本质上都是特定的/动态的)。

SELECT from temporary tables.

选择从临时表。

#1