CODEWITHSUNDEEP
sql-servertemp-tablesservice-broker
Vincent Chalmel
Vincent Chalmel

Reputation: 652

Does service broker use the same session for stored procedure exec / risk of temporary table collisions?

When using a # temporary table in a stored procedure activated by a service broker queue, if ~ simultaneous messages activate the stored procedure multiple times, will the "readers" of the queue use the same session, and, de facto, the same temporary tables ?

ref : this post that sounds like each activation will have its own session.. but am I understanding it wrong ? ref 2 : That msdn doc that left me puzzled

Upvotes: 0

Views: 284

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294307

Each reader thread, up to MAX_QUEUE_READERS, will have its own session. This must be true, otherwise they could not execute in parallel. Within a session (thread), the stored procedure may be called repeatedly. So two simultaneous instances of the stored proc cannot collide on accessing #temp table, but the #temp table may be already populated from a previous execution on the same session.

Upvotes: 1

Related Questions