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!

Answer 1

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.

Answer 2

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.

Answer 3

Serguei,

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