How to PRINT a message from SQL CLR function?

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

Leave a Comment