I am trying to implement an excel based solution whereby stock prices in a
spreadsheet are updated (via RTD) every time a trigger in a database places
messages in a queue. Not that it's relevant to my question, but the client
part of the solution is implemented using the C# sqlnotificationrequest
My question relates to what exactly the "WAITFOR (RECEIVE..." syntax does. I
have done an awful lot of reading on this, which says that when this SQL
command is executed the message is taken off the queue, but using
"RETENTION=ON" when setting up the queue means that messages are left on it
once they have been read.
I want to have multiple excel clients updated, so they will all be executing
"WAITFOR (RECEIVE..." in the sqlcommand bound to the sqlnotificationrequest.
Can I do this, or does this mean that the first one will read the message,
will remove it from the queue and the others won't get it?
I ask this question because I have had some problems with some messages not
being received by the clients. I tried to work out what the problem was by
implementing a "WAITFOR (RECEIVE..." in a loop in a stored proc in the db
itself to read off the queue. When I did this, no messages were received by
my excel clients.
I guess what I am really asking, is can I use a sql server service broker
queue to broadcast messages to multiple clients?