look'n for ideas on how to randomly pick message from ssb queue

Question

i have a queue that holds job requests...i read from it just fine right now...all job requests get posted to queue via a sql agent job.

however, i would like to investigate ways to randomly pluck from the queue...

does anyone have some ideas on this topic???

ideally, imho...the receive sql cmd might want to support a random read operation/capability/switch...

thanks in advance.

-mt

---

mike t.

Answer 1

to randomly pluck from the queue...

Hello Mike,

A queue implements the FIFO principle: first in, first out. There is no way to pick out randomly messages and at all, it wouldn't make sense for a queueing system.

If you use SQL Server 2008R2 then you could use the priority properties for the messages and the sender could assign random priorities, then you would have a kind of randomly.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

well i was think'n of reading all the messages off the queue and writting them to a table w/a identity column...then...using some mystical

random function read them back off (randomly via the identity column value) and put them back into the queue for the 'real' consumer to begin processing them...but this just sounded like

too much work...

of coarse this could be applied to the job that is originally populating the queue...it could write all the requests to a table first, then using some mystical random function, enqueue them for consumption...

which then gets me to wondering how i could implement this 'mystical' random function in t-sql???

---

mike t.

Answer 3

Hello Mike,

I don't know what kind of "mystic" you like to get?

You could give every message a random value e.g. a uniqueidentifier with NewId() and then sort them.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

yes...it looks as if writing to a temp table (before/after) then reading back out with a order by clause of: 'order by newid()' will randomly return a result set...which i could then re-post back to the queue...

this looks hopeful...will try it out.

thanks for the tip.

---

mike t.