Search code examples
sql-serverdebuggingtracesqlclr

How to PRINT a message from SQL CLR function?

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

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