Is it possible to send a message from inside a function?

Question

I was trying to make a function to log some operations in my database...
The log was going to work by sending message trough the service broker and storing them in a queue.
The problem is that when I tried to make this function I got this error:

"Invalid use of side-effecting or time-dependent operator in 'BEGIN DIALOG' within a function."

So... is there a way to send a message via service broker using functions?

Thanks for your help,
Renato Fontes

Answer 1

You are not supposed to be able to do this, because the data engine does not want the state of the database changing from within a function (i.e., no side-effects).  This is so that the optimizer will not have to take into account what order functions are called in nor how many times they are called (which can vary greatly with different query plans).  In fact it can eliminate the call entirely if it determines that you are not using the function's results.

---

-- RBarryYoung, [phone] blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Please! Remember to Vote all helpful replies as Helpful

Answer 2

That said, there are a couple of ways around this rule, if you are truly desperate.  The first is to use a SQLCLR function, and make your changes through a separate connection (because the context connection will not allow you to do this from a function) to the server.  The second is to use OPENQUERY, again, to make a separate connection into the server.

Although both of these can work, I do strongly recommend against using them.  First, because of the reasons I listed in my first reply, you do not really have control over how often (or in what order) your function will be called.  But even worse, you can easily deadlock yourself with these techniques (something that is normally impossible) because both of these tricks involve making a second connection to the server in order to fool it.

---

-- RBarryYoung, [phone] blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Please! Remember to Vote all helpful replies as Helpful