No pipe in functions

Posted by Chris on October 04, 2006

I spent a couple of minutes with a confused look on my face today. I was writing a CLR function and wanted to output some information about the progress. @SqlContext.Pipe.Send(“some message”);@ should work, right? Nope, a NullReferenceException was all I got for my troubles. I figured out that it was the property Pipe that did not return a SqlPipe, instead it gave me null. But I could not understand why there was no pipe.

Although I am sure that most of my readers are smarter than me and would figure out what the problem was in an instant, I thought I’d just make a note of it here anyway. First I thought about the DataAccess and SystemDataAccess parameters of the SqlFunction attribute. It seemed strange that they should be involved, but I tried experimenting with them anyway. No luck. After a while of thinking some more I did what always works, went for a cup of coffee. The answer came to me just about the same time as the cup was brewed. Functions are not allowed to send results to the client, whether implemented in CLR or T-SQL. Using a PRINT statement in a T-SQL UDF results in the following error when trying to create it:

@Msg 443, Level 16, State 14, Procedure test, Line 3@

@Invalid use of side-effecting or time-dependent operator in ‘PRINT’ within a function.@

So, I ended up using the Visual Studio debugger for my purposes, just as I should have from the start of course. Debugging by outputting text strings is not the best way of debugging. I just had one problem with the debugger though. I was using Niels Berglund’s excellent SQLCLR project add-in for Visual Studio for creating, deploying and debugging the code. I am not sure if the Visual Studio CLR project type behaves the same way, or even if it was just me doing something wrong, but the debugging mode were always abruptly ended with a message that the deployment took too long and timed out. Has anyone else had this problem?


Trackbacks are closed.

blog comments powered by Disqus