Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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!

like image 332
Serguei Avatar asked Jan 29 '09 19:01

Serguei


People also ask

How do I print a message in SQL?

Declare @SumVal int; Select @SumVal=Sum(Amount) From Expense; Print @SumVal; You can, of course, print any number of fields from the table in this way. Of course, if you want to print all of the results from a query that returns multiple rows, you'd just direct your output appropriately (e.g. to Text).

What is a CLR function in SQL?

CLR functions can be used to access external resources such as files, network resources, Web Services, other databases (including remote instances of SQL Server). This can be achieved by using various classes in the . NET Framework, such as System.IO , System. WebServices , System. Sql , and so on.

How do you implement CLR table valued function?

Implementing Table-Valued Functions. Implement table-valued functions as methods on a class in a Microsoft . NET Framework assembly. Your table-valued function code must implement the IEnumerable interface.


2 Answers

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..)
like image 139
Nestor Avatar answered Sep 30 '22 20:09

Nestor


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.

like image 23
Sean Bright Avatar answered Sep 30 '22 20:09

Sean Bright