[ACCEPTED]-How to PRINT a message from SQL CLR function?-sqlclr

Accepted answer

Score: 36

The answer is that you cannot do the equivalent 7 of

PRINT 'Hello World'

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

SqlContext.Pipe.Send("hello world")

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

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).
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.
Move your code to a [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..)

Score: 10

You should just be able to do:

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

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

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

Score: 2

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

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

Score: 2

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

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

you don't 81 need to switch to using a Stored Procedure. If 80 want a function then stick with a function.
adding 79 a "debug" parameter and changing 78 the output for this seems a bit extreme 77 since UDFs (T-SQL and SQLCLR) functions 76 do not allow for overloading. Hence the 75 debug parameter will always be in the signature. If 74 you want to trigger debugging, just create 73 a temp table called #debug (or something like 72 that) and test for via SELECT OBJECT_ID(N'tempdb..#debug'); using "Context Connection = true;" for the ConnectionString 71 (which is fast and can be done in SAFE mode 70 and is part of the same session so it can see 69 the temp table). Get the result of that 68 from if (SqlCommand.ExecuteScalar() == DBNull.Value).
please do not use a global (i.e. static) variable. that 67 is far more complicated than necessary, and 66 requires (typically) that the Assembly be 65 set to UNSAFE, which should be avoided if at all 64 possible.

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

write the messages to a file 53 using File.AppendAllText (String path, String contents). Of course, if you don't have access 52 to the file system then this isn't as helpful. If 51 there is a shared drive on the network that 50 can be accessed, then as long as the service 49 account for the SQL Server service has permission 48 to create and write files on that share, then 47 this will work. If there is a share that 46 the service account doesn't have permission 45 to but your Domain / Active Directory account 44 does, then you can wrap that File.AppendAllText call in:
```
using (WindowsImpersonationContext _Impersonate = 
                      SqlContext.WindowsIdentity.Impersonate())
{
   File.AppendAllText("path.txt", _DebugMessage);
    _Impersonate.Undo();
}
```
connect 43 to SQL Server and write the messages to 42 a table. It can be the current / local SQL 41 Server or any other SQL Server. You can 40 create a table in [tempdb] so that it is automatically 39 cleaned up the next time SQL Server is restarted, but 38 otherwise lasts until that time, or until 37 you drop it. Making a regular / external 36 connection allows you to do DML statements. Then 35 you can select from the table as you are 34 running the function.
write the messages 33 to an environment variable. Environment 32 variables aren't exactly limited in size 31 since Vista / Server 2008, though they don't 30 really handle newlines. But any variable 29 set from within .NET code will also survive 28 until the SQL Server service is restarted. And 27 you can append message by reading the current 26 value and concatenating the new message 25 to the end. Something like:
```
{
  string _Current = System.Environment.GetEnvironmentVariable(_VariableName,
                                  EnvironmentVariableTarget.Process);

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

It should be 24 noted that in each of these 3 cases, it 23 is assumed that the testing is being done 22 in a single-threaded manner. If the function 21 will be running from multiple sessions at 20 the same time, then you need a way to separate 19 the messages. In that case, you can get 18 the current "transaction_id" (all 17 queries, even without a BEGIN TRAN are a transaction!) which 16 should be consistent for any particular 15 execution (across multiple uses in the same 14 function as well as if the function is called 13 per each row across multiple rows). You 12 can use this value as a prefix for the messages 11 if using the file or environment variable 10 methods, or as a separate field if storing 9 into a table. You can get the transaction 8 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 7 was initially taken from the MSDN page for 6 Create User-defined Functions (Database Engine) and then edited by me, as noted, to reflect 5 the differences between T-SQL functions 4 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 3 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 2 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 1 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.

Score: 1

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