parameter for stored proc.

[s...@nospamxyz.com]

ALTER QUEUE <object> WITH
[ STATUS = { ON | OFF } [ , ] ]
[ RETENTION = { ON | OFF } [ , ] ]
[ ACTIVATION (
{ [ STATUS = { ON | OFF } [ , ] ]
[ PROCEDURE_NAME = <procedure> [ , ] ]
[ MAX_QUEUE_READERS = max_readers [ , ] ]
[ EXECUTE AS { SELF | 'user_name' | OWNER } ]
| DROP }
) ]
[ ; ]

Gurus - Is it possible to specify parameter for the stored proc?

Please suggest any alternatives.

[Bob Beauchemin]

No, procedures with parameters are not allowed, an error message is
returned.

What would such a parameter be used for? You do not call this procedure
yourself, ever, its called by the broker infrastructure only. If you'd
planned to pass in something to distinguish between multiple
messages/services/contracts bound for the same queue, an alternative in
general is that queues are cheap, better to have multiple queues/services
than multiple usages for a single queue and something to distinguish between
them. Another choice is to make it part of the message body.

Other than that, without knowing what you're trying to accomplish, there's
no way to suggest concrete alternatives.

Cheers,
Bob Beauchemin
SQLskills

"s...@nospambbb.com" <s...@nospamxyz.com> wrote in message
news:uMIRh0T...@TK2MSFTNGP02.phx.gbl...

[s...@nospamxyz.com]

Thx Bob!

I have table with 20 SystemSettings. On each setting i have to call the an
stored proc for processing the request with the parameters from the
SystemSettings table. Only one instance of the stored proc can be called
for each setting at a given time Other calls to the same settings must be
queued.

I don't want to create 20 queues and 20 different stored procs for handling
the que. and dynamic sql as more settings are added.

I am looking for a better solution. since it will be the same code with
different queue and service

Somewhere i read may be conversation group can be helpful in this kind of
situation. I am also wondering if
MAX_QUEUE_READERS = max_readers in such case is or more 1.

I hope i am clear. Please let me know if you need more info.

Any ideas?

Thx!

"Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message
news:O%23GiWFYh...@TK2MSFTNGP03.phx.gbl...

[Bob Beauchemin]

If you need to do subtly different things with different queued messages,
you'd indicate that with different message types. You can find out the
message type in the activation proc, and use this as a qualifier in your
(mostly the same) code. Or make the distinguishing bit a part of the
message. You don't need a parameter in a activation proc for this.

If you want other writer/processor to wait for the others to finish, that's
what SQL Server's exclusive lock does. If it's an exclusive lock around a
database row, that's built-in. If it's an exclusive lock around a block of
(stored procedure) code that you want, you can accomplish that with a SQL
Server application lock. Putting application locks in activation procs could
slow down your activation proc queue processing in general, however.

In Service Broker, reading from a queue locks at the conversation group
level; reading the first message in a conversation group or asking for an
explicit conversation group lock means that no one else can read messages
from the same group while you are processing, although other readers can
process other conversations/in other conversation groups in the same queue.
Conversation groups are associated with conversations however, so you
couldn't read additional messages in the same *conversation* from another
activation proc instance while the conversation group is locked. Because
conversations are tied to conversation group. You can specify conversation
group in the begin dialog statement, so having an explicit set of
conversation groups (you keep track of this), might work for your purposes.

Specifying max_queue_readers = 1 means only one instance of the activation
proc will be running at a time. This would effectively serialize activation
processing for that service (but inserts into the queue would process, the
messages would just queue up), which you likely wouldn't want if had wanted
20 different use-cases being serviced by the same service.

Hope this helps,
Bob Beauchemin
SQLskills

"s...@nospambbb.com" <s...@nospamxyz.com> wrote in message

news:O9Synxdh...@TK2MSFTNGP02.phx.gbl...

[s...@nospamxyz.com]

Thx a lot Bob for insight and alternatives.

Conversation Group was the answer i was looking for. Just curious if the
order(sequence) of the messages in the conversation group
is retained when messages are processed at the target queue?

Thx!

"Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message

news:e1AR$lihJH...@TK2MSFTNGP05.phx.gbl...

[Bob Beauchemin]

No, the ordering of messages is guarenteed at a conversation level, but not
at a conversation group level.

Cheers,
Bob

"s...@nospambbb.com" <s...@nospamxyz.com> wrote in message

news:ej7TTSsh...@TK2MSFTNGP02.phx.gbl...

[s...@nospamxyz.com]

Thx a lot again.

It should not be a problem in our case as the process to put in the queue
is user triggered.

"Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message

news:OYnVr9th...@TK2MSFTNGP03.phx.gbl...