phillip.snipes (1/19/2010)
Jason,I'd like to give this approach a try as it fits our use case scenario quite well but, I do not see the table creation scripts for the objects dbo.packageQueueLog and dbo.package_Queue; Would you have the table creation scripts, or parhaps could you post a replywith their schemas?
Thanks,
Phillip,
The table dbo.packageQueueLog isn't used in this solution and neither is the procedure dbo.spUpdatePackageQueueLog_executionID. Initially there was a lot more going on in this article, (logging, throttling, dynamic package variable values, etc...) but in order to get it approved by the editor I had to strip it down to the basics. It was a bit too busy I suppose.
The object dbo.package_Queue isn't a table, it is a service broker queue. The ddl for this object is:
create queue
package_Queue
with status =on,
activation (procedure_name = spExecQueuedPackages,
max_queue_readers = 1, --Change this value to allow more or less packages to be executed on this queue simultaneously.
execute as 'dbo');
Also, don't forget to end the conversation somewhere in your process (dbo.spExecQueuedPackages is a good spot) as I forgot to do this. Thanks to macqueen for pointing that out.