How to PRINT a message from SQL CLR function?

Question

Is there an equivalent of

PRINT 'hello world'

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

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

Thanks!

Solution 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

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.

For workarounds i suggest:

1. Use Debug.Print from your code, and attach a debugger to the SQL Server (I know this doesnt work for you as you explained).
2. 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.
3. Move your code to a [SqlProcedure()]
4. 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..)

Solution 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.

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.

Solution 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.

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

Solution 4

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.

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).

• 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:

• 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:

  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.

• 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:

  {
    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:

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

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:

• 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. ]
• SET statements are not allowed in a user-defined function.
• The FOR XML clause is not allowed
• 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.
• 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

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

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

The following pertains only to T-SQL functions:

• 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.

In contrast, SQLCLR functions can:

• 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).
• SELECT from temporary tables.

Solution 5

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.

View more solutions