Home
%3CLINGO-SUB%20id%3D%22lingo-sub-383461%22%20slang%3D%22en-US%22%3EReusing%20dialogs%20with%20a%20dialog%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383461%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jul%2024%2C%202008%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EAs%20noted%20in%20various%20Service%20Broker%20sources%2C%20it%20is%20often%20advantageous%20to%26nbsp%3Bminimize%20the%20overhead%20of%20creating%20dialogs%20to%20send%20messages%20on.%20This%20blog%20shows%20how%20to%20create%20a%20shared%20pool%20of%20dialogs%20to%20be%20able%20to%20reuse%20dialogs%20instead%20of%20creating%20new%20ones.%20The%20dialog%20pool%20is%20a%20variation%20of%20Remus%20Rusanu's%20reusing%20and%20recycling%20conversations%20as%20shown%20in%20his%20%3CA%20href%3D%22http%3A%2F%2Frusanu.com%2Fblog%2F%22%20mce_href%3D%22http%3A%2F%2Frusanu.com%2Fblog%2F%22%20title%3D%22rusanu.com%2Fblog%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20blog%20%3C%2FA%3E%20.%20One%20of%20the%20main%20differences%20is%20that%20the%20dialog%20pool%20is%20keyed%20only%20on%20services%20and%20contract%2C%20not%20SPID.%20This%20allows%20the%20same%20SPID%20to%20obtain%20multiple%20dialogs%20from%20the%20pool%20should%20the%20need%20arise.%20As%20importantly%2C%20different%20SPIDs%20can%20reuse%20the%20same%20dialog%20sequentially%20instead%20of%20creating%20two%20of%20them.%20Measurements%20show%20equivalent%20performance%20using%20the%20dialog%20pool%20compared%20to%20the%20SPID-based%20reuse%20scheme.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20following%20code%20shows%20how%20to%20get%2C%20free%20and%20delete%20dialogs%20from%20a%20dialog%20pool%20table.%20Initially%20empty%2C%26nbsp%3Ba%20new%20dialog%20is%20created%20in%20the%20pool%26nbsp%3Bwhen%26nbsp%3Ba%20request%20for%20an%20existing%20free%20dialog%20cannot%20be%20met.%20Thus%20the%20pool%20will%20grow%20during%20bursts%20of%20high%20demand.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20dialog%20pool%20entries%20also%20contain%20creation%20time%20and%20send%20count%20fields%26nbsp%3Bthat%20ease%20the%20auditing%20and%20%22recycling%22%20of%20dialogs%20in%20the%20pool%20based%20on%20application%20requirements.%20Recycling%20consists%20of%20gracefully%20ending%20an%20existing%20dialog%20between%20services%20and%20beginning%20a%20new%20one.%20If%20done%20prudently%2C%20this%20technique%20can%20ease%20the%20handling%20of%20dialog%20errors%20by%20limiting%20the%20number%20of%20messages%20affected.%20For%20example%2C%20the%20application%20may%20choose%20to%20contrain%20a%20dialog%20to%20a%20certain%20number%20of%20messages%20before%20it%20is%20recycled.%20This%20might%20also%20be%20done%20according%20to%20the%20age%20of%20a%20dialog.%20See%20the%20end%20of%20the%20usp_send%20procedure%20for%20an%20example%20of%20recycling.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAn%20example%20application%20that%20exercises%20the%20dialog%20pool%20is%20also%20included.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Dialog%20Pool%20Sample.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20This%20sample%20shows%20how%20to%20create%20and%20use%20a%20shared%20pool%20of%20reuseable%20dialogs.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20The%20purpose%20of%20reusing%20dialogs%20is%20to%20reduce%20the%20overhead%20of%20creating%20them.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20The%20sample%20also%20shows%20how%20dialogs%20in%20the%20pool%20can%20be%20%22recycled%22%20by%20deleting%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20dialogs%20based%20on%20application%20criteria%2C%20such%20as%20number%20of%20messages%20sent.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20This%20sample%20is%20largely%20based%20on%20Remus%20Rusanu's%20tutorials%20on%20reusing%20and%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20recycling%20conversations%20(rusanu.com%2Fblog).%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Contents%3A%20dialog%20pool%20and%20application%20using%20the%20pool.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E----------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUSE%20master%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Create%20demo%20database%20section%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20EXISTS%20(SELECT%20name%20FROM%20sys.databases%20WHERE%20name%20%3D%20'SsbDemoDb')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDROP%20DATABASE%20%5BSsbDemoDb%5D%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20DATABASE%20%5BSsbDemoDb%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUSE%26nbsp%3B%5BSsbDemoDb%5D%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Create%20master%20key%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20NOT%20EXISTS(SELECT%20name%20FROM%20sys.symmetric_keys%20WHERE%20name%20%3D%20'%23%23MS_DatabaseMasterKey%23%23')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20MASTER%20KEY%20ENCRYPTION%20BY%20PASSWORD%3D'Password%23123'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Dialog%20pool%20section%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20The%20dialog%20pool%20table.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Obtain%20a%20conversation%20handle%20using%20from%20service%2C%20to%20service%2C%20and%20contract.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Also%20indicates%20age%20and%20usage%20of%20dialog%20for%20auditing%20purposes.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20EXISTS%20(SELECT%20name%20FROM%20sys.tables%20WHERE%20name%20%3D%20'DialogPool')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDROP%20TABLE%20%5BDialogPool%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20%5BDialogPool%5D%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFromService%20SYSNAME%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EToService%20SYSNAME%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOnContract%20SYSNAME%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHandle%20UNIQUEIDENTIFIER%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOwnerSPID%20INT%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECreationTime%20DATETIME%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESendCount%20BIGINT%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUNIQUE%20(Handle))%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Get%20dialog%20procedure.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Reuse%20a%20free%20dialog%20in%20the%20pool%20or%20create%20a%20new%20one%20in%20case%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20no%20free%20dialogs%20exist.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Input%20is%20from%20service%2C%20to%20service%2C%20and%20contract.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Output%20is%20dialog%20handle%20and%20count%20of%20message%20previously%20sent%20on%20dialog.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20EXISTS%20(SELECT%20name%20FROM%20sys.procedures%20WHERE%20name%20%3D%20'usp_get_dialog')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDROP%20PROC%20usp_get_dialog%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20PROCEDURE%20%5Busp_get_dialog%5D%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40fromService%20SYSNAME%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40toService%20SYSNAME%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40onContract%20SYSNAME%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40dialogHandle%20UNIQUEIDENTIFIER%20OUTPUT%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40sendCount%20BIGINT%20OUTPUT)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20NOCOUNT%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40dialog%20TABLE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFromService%20SYSNAME%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EToService%20SYSNAME%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOnContract%20SYSNAME%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHandle%20UNIQUEIDENTIFIER%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOwnerSPID%20INT%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECreationTime%20DATETIME%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESendCount%20BIGINT%20NOT%20NULL%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Try%20to%20claim%20an%20unused%20dialog%20in%20%5BDialogPool%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20READPAST%20option%20avoids%20blocking%20on%20locked%20dialogs.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDELETE%20%40dialog%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUPDATE%20TOP(1)%20%5BDialogPool%5D%20WITH(READPAST)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20OwnerSPID%20%3D%20%40%40SPID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOUTPUT%20INSERTED.*%20INTO%20%40dialog%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20FromService%20%3D%20%40fromService%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAND%20ToService%20%3D%20%40toService%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAND%20OnContract%20%3D%20%40OnContract%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAND%20OwnerSPID%20%3D%20-1%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20%40%40ROWCOUNT%20%26gt%3B%200%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40dialogHandle%20%3D%20(SELECT%20Handle%20FROM%20%40dialog)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40sendCount%20%3D%20(SELECT%20SendCount%20FROM%20%40dialog)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20No%20free%20dialogs%3A%20need%20to%20create%20a%20new%20one%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20DIALOG%20CONVERSATION%20%40dialogHandle%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20SERVICE%20%40fromService%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETO%20SERVICE%20%40toService%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EON%20CONTRACT%20%40onContract%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWITH%20ENCRYPTION%20%3D%20OFF%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINSERT%20INTO%20%5BDialogPool%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E(FromService%2C%20ToService%2C%20OnContract%2C%20Handle%2C%20OwnerSPID%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECreationTime%2C%20SendCount)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVALUES%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E(%40fromService%2C%20%40toService%2C%20%40onContract%2C%20%40dialogHandle%2C%20%40%40SPID%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGETDATE()%2C%200)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40sendCount%20%3D%200%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Free%20dialog%20procedure.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Return%20the%20dialog%20to%20the%20pool.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Inputs%20are%20dialog%20handle%20and%20updated%20send%20count.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20EXISTS%20(SELECT%20name%20FROM%20sys.procedures%20WHERE%20name%20%3D%20'usp_free_dialog')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDROP%20PROC%20usp_free_dialog%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20PROCEDURE%20%5Busp_free_dialog%5D%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40dialogHandle%20UNIQUEIDENTIFIER%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40sendCount%20BIGINT)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20NOCOUNT%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40rowcount%20INT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40string%20VARCHAR(50)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Release%20dialog%20by%20setting%20OwnerSPID%20to%20-1.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUPDATE%20%5BDialogPool%5D%20SET%20OwnerSPID%20%3D%20-1%2C%20SendCount%20%3D%20%40sendCount%20WHERE%20Handle%20%3D%20%40dialogHandle%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20%40rowcount%20%3D%20%40%40ROWCOUNT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20%40rowcount%20%3D%200%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40string%20%3D%20(SELECT%20CAST(%20%40dialogHandle%20AS%20VARCHAR(50)))%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERAISERROR('usp_free_dialog%3A%20dialog%20%25s%20not%20found%20in%20dialog%20pool'%2C%2016%2C%201%2C%20%40string)%20WITH%20LOG%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%20IF%20%40rowcount%20%26gt%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40string%20%3D%20(SELECT%20CAST(%20%40dialogHandle%20AS%20VARCHAR(50)))%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERAISERROR('usp_free_dialog%3A%20duplicate%20dialog%20%25s%20found%20in%20dialog%20pool'%2C%2016%2C%201%2C%20%40string)%20WITH%20LOG%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Delete%20dialog%20procedure.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Delete%20the%20dialog%20from%20the%20pool.%20This%20does%20not%20end%20the%20dialog.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Input%20is%20dialog%20handle.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20EXISTS%20(SELECT%20name%20FROM%20sys.procedures%20WHERE%20name%20%3D%20'usp_delete_dialog')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDROP%20PROC%20usp_delete_dialog%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20PROCEDURE%20%5Busp_delete_dialog%5D%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40dialogHandle%20UNIQUEIDENTIFIER)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20NOCOUNT%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDELETE%20%5BDialogPool%5D%20WHERE%20Handle%20%3D%20%40dialogHandle%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Application%20setup%20section.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Send%20messages%20from%20initiator%20to%20target.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Initiator%20uses%20dialogs%20from%20the%20dialog%20pool.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Initiator%20also%20retires%20dialogs%20based%20on%20application%20criteria%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20which%20results%20in%20recycling%20dialogs%20in%20the%20pool.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20This%20table%20stores%20the%20messages%20on%20the%20target%20side%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20EXISTS%20(SELECT%20name%20FROM%20sys.tables%20WHERE%20name%20%3D%20'MsgTable')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDROP%20TABLE%20MsgTable%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20MsgTable%20(%20message_type%20SYSNAME%2C%20message_body%20NVARCHAR(4000))%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Activated%20store%20proc%20for%20the%20initiator%20to%20receive%20messages.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20PROCEDURE%20initiator_queue_activated_procedure%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40handle%20UNIQUEIDENTIFIER%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40message_type%20SYSNAME%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWAITFOR%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERECEIVE%20TOP(1)%20%40handle%20%3D%20%5Bconversation_handle%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40message_type%20%3D%20%5Bmessage_type_name%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20%5BSsbInitiatorQueue%5D)%2C%20TIMEOUT%205000%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20%40%40ROWCOUNT%20%3D%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Expect%20target%20response%20to%20EndOfStream%20message.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20%40message_type%20%3D%20'%3CA%20href%3D%22http%3A%2F%2Fschemas.microsoft.com%2FSQL%2FServiceBroker%2FEndDialog%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fschemas.microsoft.com%2FSQL%2FServiceBroker%2FEndDialog%3C%2FA%3E'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%20CONVERSATION%20%40handle%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Activated%20store%20proc%20for%20the%20target%20to%20receive%20messages.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20PROCEDURE%20target_queue_activated_procedure%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Variable%20table%20for%20received%20messages.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40receive_table%20TABLE(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Equeuing_order%20BIGINT%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Econversation_handle%20UNIQUEIDENTIFIER%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Emessage_type_name%20SYSNAME%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Emessage_body%20VARCHAR(MAX))%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Cursor%20for%20received%20message%20table.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20message_cursor%20CURSOR%20LOCAL%20FORWARD_ONLY%20READ_ONLY%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFOR%20SELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Econversation_handle%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Emessage_type_name%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Emessage_body%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20%40receive_table%20ORDER%20BY%20queuing_order%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40conversation_handle%20UNIQUEIDENTIFIER%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40message_type%20SYSNAME%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40message_body%20VARCHAR(4000)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Error%20variables.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40error_number%20INT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40error_message%20VARCHAR(4000)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40error_severity%20INT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40error_state%20INT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40error_procedure%20SYSNAME%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40error_line%20INT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40error_dialog%20VARCHAR(50)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRY%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHILE%20(1%20%3D%201)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Receive%20all%20available%20messages%20into%20the%20table.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Wait%205%20seconds%20for%20messages.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWAITFOR%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERECEIVE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5Bqueuing_order%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5Bconversation_handle%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5Bmessage_type_name%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAST(%5Bmessage_body%5D%20AS%20VARCHAR(4000))%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20%5BSsbTargetQueue%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINTO%20%40receive_table%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%2C%20TIMEOUT%205000%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20%40%40ROWCOUNT%20%3D%200%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBREAK%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOPEN%20message_cursor%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHILE%20(1%3D1)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFETCH%20NEXT%20FROM%20message_cursor%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINTO%20%40conversation_handle%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40message_type%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40message_body%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20(%40%40FETCH_STATUS%20!%3D%200)%20BREAK%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Process%20a%20message.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20If%20an%20exception%20occurs%2C%20catch%20and%20attempt%20to%20recover.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRY%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20%40message_type%20%3D%20'SsbMsgType'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20process%20the%20msg.%20Here%20we%20will%20just%20insert%20it%20into%20a%20table%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINSERT%20INTO%20MsgTable%20values(%40message_type%2C%20%40message_body)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%20IF%20%40message_type%20%3D%20'EndOfStream'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20initiator%20is%20signaling%20end%20of%20message%20stream%3A%20end%20the%20dialog%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%20CONVERSATION%20%40conversation_handle%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%20IF%20%40message_type%20%3D%20'%3CA%20href%3D%22http%3A%2F%2Fschemas.microsoft.com%2FSQL%2FServiceBroker%2FError%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fschemas.microsoft.com%2FSQL%2FServiceBroker%2FError%3C%2FA%3E'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20If%20the%20message_type%20indicates%20that%20the%20message%20is%20an%20error%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20raise%20the%20error%20and%20end%20the%20conversation.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWITH%20XMLNAMESPACES%20('%3CA%20href%3D%22http%3A%2F%2Fschemas.microsoft.com%2FSQL%2FServiceBroker%2FError%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fschemas.microsoft.com%2FSQL%2FServiceBroker%2FError%3C%2FA%3E'%20AS%20ssb)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40error_number%20%3D%20CAST(%40message_body%20AS%20XML).value('(%2F%2Fssb%3AError%2Fssb%3ACode)%5B1%5D'%2C%20'INT')%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40error_message%20%3D%20CAST(%40message_body%20AS%20XML).value('(%2F%2Fssb%3AError%2Fssb%3ADescription)%5B1%5D'%2C%20'VARCHAR(4000)')%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_dialog%20%3D%20CAST(%40conversation_handle%20AS%20VARCHAR(50))%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERAISERROR('Error%20in%20dialog%20%25s%3A%20%25s%20(%25i)'%2C%2016%2C%201%2C%20%40error_dialog%2C%20%40error_message%2C%20%40error_number)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%20CONVERSATION%20%40conversation_handle%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%20TRY%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20CATCH%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_number%20%3D%20ERROR_NUMBER()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_message%20%3D%20ERROR_MESSAGE()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_severity%20%3D%20ERROR_SEVERITY()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_state%20%3D%20ERROR_STATE()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_procedure%20%3D%20ERROR_PROCEDURE()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_line%20%3D%20ERROR_LINE()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20XACT_STATE()%20%3D%20-1%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20The%20transaction%20is%20doomed.%20Only%20rollback%20possible.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20This%20could%20disable%20the%20queue%20if%20done%205%20times%20consecutively!%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EROLLBACK%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Record%20the%20error.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINSERT%20INTO%20target_processing_errors%20VALUES(NULL%2C%20%40error_number%2C%20%40error_message%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40error_severity%2C%20%40error_state%2C%20%40error_procedure%2C%20%40error_line%2C%201)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20For%20this%20level%20of%20error%2C%20it%20is%20best%20to%20exit%20the%20proc%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20and%20give%20the%20queue%20monitor%20control.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Breaking%20to%20the%20outer%20catch%20will%20accomplish%20this.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERAISERROR%20('Message%20processing%20error'%2C%2016%2C%201)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%20IF%20XACT_STATE()%20%3D%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Record%20error%20and%20continue%20processing%20messages.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Failing%20message%20could%20also%20be%20put%20aside%20for%20later%20processing%20here.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Otherwise%20it%20will%20be%20discarded.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINSERT%20INTO%20target_processing_errors%20VALUES(NULL%2C%20%40error_number%2C%20%40error_message%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40error_severity%2C%20%40error_state%2C%20%40error_procedure%2C%20%40error_line%2C%200)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%20CATCH%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECLOSE%20message_cursor%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDELETE%20%40receive_table%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%20TRY%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20CATCH%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Process%20the%20error%20and%20exit%20the%20proc%20to%20give%20the%20queue%20monitor%20control%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_number%20%3D%20ERROR_NUMBER()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_message%20%3D%20ERROR_MESSAGE()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_severity%20%3D%20ERROR_SEVERITY()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_state%20%3D%20ERROR_STATE()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_procedure%20%3D%20ERROR_PROCEDURE()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40error_line%20%3D%20ERROR_LINE()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20XACT_STATE()%20%3D%20-1%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20The%20transaction%20is%20doomed.%20Only%20rollback%20possible.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20This%20could%20disable%20the%20queue%20if%20done%205%20times%20consecutively!%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EROLLBACK%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Record%20the%20error.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINSERT%20INTO%20target_processing_errors%20VALUES(NULL%2C%20%40error_number%2C%20%40error_message%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40error_severity%2C%20%40error_state%2C%20%40error_procedure%2C%20%40error_line%2C%201)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%20IF%20XACT_STATE()%20%3D%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Record%20error%20and%20commit%20transaction.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Here%20you%20could%20also%20save%20anything%20else%20you%20want%20before%20exiting.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINSERT%20INTO%20target_processing_errors%20VALUES(NULL%2C%20%40error_number%2C%20%40error_message%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40error_severity%2C%20%40error_state%2C%20%40error_procedure%2C%20%40error_line%2C%200)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%20CATCH%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Table%20to%20store%20processing%20errors.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20EXISTS%20(SELECT%20name%20FROM%20sys.tables%20WHERE%20name%20%3D%20'target_processing_errors')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDROP%20TABLE%20target_processing_errors%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20target_processing_errors%20(error_conversation%20UNIQUEIDENTIFIER%2C%20error_number%20INT%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eerror_message%20VARCHAR(4000)%2C%20error_severity%20INT%2C%20error_state%20INT%2C%20error_procedure%20SYSNAME%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eerror_line%20INT%2C%20doomed_transaction%20TINYINT)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Create%20Initiator%20and%20Target%20side%20SSB%20entities%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20MESSAGE%20TYPE%20SsbMsgType%20VALIDATION%20%3D%20WELL_FORMED_XML%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20MESSAGE%20TYPE%20EndOfStream%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20CONTRACT%20SsbContract%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESsbMsgType%20SENT%20BY%20INITIATOR%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEndOfStream%20SENT%20BY%20INITIATOR%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20QUEUE%20SsbInitiatorQueue%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWITH%20ACTIVATION%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESTATUS%20%3D%20ON%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EMAX_QUEUE_READERS%20%3D%201%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPROCEDURE_NAME%20%3D%20%5Binitiator_queue_activated_procedure%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEXECUTE%20AS%20OWNER)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20QUEUE%20SsbTargetQueue%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWITH%20ACTIVATION%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESTATUS%20%3D%20ON%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EMAX_QUEUE_READERS%20%3D%201%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPROCEDURE_NAME%20%3D%20%5Btarget_queue_activated_procedure%5D%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEXECUTE%20AS%20OWNER)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20SERVICE%20SsbInitiatorService%20ON%20QUEUE%20SsbInitiatorQueue%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20SERVICE%20SsbTargetService%20ON%20QUEUE%20SsbTargetQueue%20(SsbContract)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20SEND%20procedure.%20Uses%20a%20dialog%20from%20the%20dialog%20pool.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20EXISTS%20(SELECT%20name%20FROM%20sys.procedures%20WHERE%20name%20%3D%20'usp_send')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDROP%20PROC%20usp_send%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20PROCEDURE%20%5Busp_send%5D%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40fromService%20SYSNAME%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40toService%20SYSNAME%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40onContract%20SYSNAME%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40messageType%20SYSNAME%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%40messageBody%20NVARCHAR(MAX))%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20NOCOUNT%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40dialogHandle%20UNIQUEIDENTIFIER%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40sendCount%20BIGINT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40counter%20INT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40error%20INT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20%40counter%20%3D%201%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Will%20need%20a%20loop%20to%20retry%20in%20case%20the%20dialog%20is%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20in%20a%20state%20that%20does%20not%20allow%20transmission%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHILE%20(1%3D1)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Claim%20a%20dialog%20from%20the%20dialog%20pool.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20A%20new%20one%20will%20be%20created%20if%20none%20are%20available.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEXEC%20usp_get_dialog%20%40fromService%2C%20%40toService%2C%20%40onContract%2C%20%40dialogHandle%20OUTPUT%2C%20%40sendCount%20OUTPUT%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Attempt%20to%20SEND%20on%20the%20dialog%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20(%40messageBody%20IS%20NOT%20NULL)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20If%20the%20%40messageBody%20is%20not%20null%20it%20must%20be%20sent%20explicitly%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESEND%20ON%20CONVERSATION%20%40dialogHandle%20MESSAGE%20TYPE%20%40messageType%20(%40messageBody)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Messages%20with%20no%20body%20must%20*not*%20specify%20the%20body%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20cannot%20send%20a%20NULL%20value%20argument%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESEND%20ON%20CONVERSATION%20%40dialogHandle%20MESSAGE%20TYPE%20%40messageType%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20%40error%20%3D%20%40%40ERROR%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20%40error%20%3D%200%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Successful%20send%2C%20increment%20count%20and%20exit%20the%20loop%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40sendCount%20%3D%20%40sendCount%20%2B%201%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBREAK%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20%40counter%20%3D%20%40counter%2B1%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20%40counter%20%26gt%3B%2010%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20We%20failed%2010%20times%20in%20a%26nbsp%3B%20row%2C%20something%20must%20be%20broken%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERAISERROR('Failed%20to%20SEND%20on%20a%20conversation%20for%20more%20than%2010%20times.%20Error%20%25i.'%2C%2016%2C%201%2C%20%40error)%20WITH%20LOG%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBREAK%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Delete%20the%20associated%20dialog%20from%20the%20table%20and%20try%20again%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEXEC%20usp_delete_dialog%20%40dialogHandle%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20%40dialogHandle%20%3D%20NULL%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20%22Criterion%22%20for%20dialog%20pool%20removal%20is%20send%20count%20%26gt%3B%201000.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Modify%20to%20suit%20application.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20When%20deleting%20also%20inform%20the%20target%20to%20end%20the%20dialog.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIF%20%40sendCount%20%26gt%3B%201000%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEXEC%20usp_delete_dialog%20%40dialogHandle%20%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESEND%20ON%20CONVERSATION%20%40dialogHandle%20MESSAGE%20TYPE%20%5BEndOfStream%5D%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Free%20the%20dialog.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEXEC%20usp_free_dialog%20%40dialogHandle%2C%20%40sendCount%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOMMIT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E------------------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Run%20application%20section%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E------------------------------------------------------------------------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Send%20some%20messages%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eexec%20usp_send%20N'SsbInitiatorService'%2C%20N'SsbTargetService'%2C%20N'SsbContract'%2C%20N'SsbMsgType'%2C%20N'%3CXML%3EThis%20is%20a%20well%20formed%20XML%20Message1.%3C%2FXML%3E'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eexec%20usp_send%20N'SsbInitiatorService'%2C%20N'SsbTargetService'%2C%20N'SsbContract'%2C%20N'SsbMsgType'%2C%20N'%3CXML%3EThis%20is%20a%20well%20formed%20XML%20Message2.%3C%2FXML%3E'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eexec%20usp_send%20N'SsbInitiatorService'%2C%20N'SsbTargetService'%2C%20N'SsbContract'%2C%20N'SsbMsgType'%2C%20N'%3CXML%3EThis%20is%20a%20well%20formed%20XML%20Message3.%3C%2FXML%3E'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eexec%20usp_send%20N'SsbInitiatorService'%2C%20N'SsbTargetService'%2C%20N'SsbContract'%2C%20N'SsbMsgType'%2C%20N'%3CXML%3EThis%20is%20a%20well%20formed%20XML%20Message4.%3C%2FXML%3E'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eexec%20usp_send%20N'SsbInitiatorService'%2C%20N'SsbTargetService'%2C%20N'SsbContract'%2C%20N'SsbMsgType'%2C%20N'%3CXML%3EThis%20is%20a%20well%20formed%20XML%20Message5.%3C%2FXML%3E'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Show%20the%20dialog%20pool%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20*%20FROM%20%5BDialogPool%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Show%20the%20dialogs%20used.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20*%20FROM%20sys.conversation_endpoints%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Check%20whether%20the%20TARGET%20side%20has%20processed%20the%20messages%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20*%20FROM%20MsgTable%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETRUNCATE%20TABLE%20MsgTable%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383461%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jul%2024%2C%202008%20As%20noted%20in%20various%20Service%20Broker%20sources%2C%20it%20is%20often%20advantageous%20to%26nbsp%3Bminimize%20the%20overhead%20of%20creating%20dialogs%20to%20send%20messages%20on.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383461%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerServiceBroker%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft
First published on MSDN on Jul 24, 2008

As noted in various Service Broker sources, it is often advantageous to minimize the overhead of creating dialogs to send messages on. This blog shows how to create a shared pool of dialogs to be able to reuse dialogs instead of creating new ones. The dialog pool is a variation of Remus Rusanu's reusing and recycling conversations as shown in his blog . One of the main differences is that the dialog pool is keyed only on services and contract, not SPID. This allows the same SPID to obtain multiple dialogs from the pool should the need arise. As importantly, different SPIDs can reuse the same dialog sequentially instead of creating two of them. Measurements show equivalent performance using the dialog pool compared to the SPID-based reuse scheme.



The following code shows how to get, free and delete dialogs from a dialog pool table. Initially empty, a new dialog is created in the pool when a request for an existing free dialog cannot be met. Thus the pool will grow during bursts of high demand.



The dialog pool entries also contain creation time and send count fields that ease the auditing and "recycling" of dialogs in the pool based on application requirements. Recycling consists of gracefully ending an existing dialog between services and beginning a new one. If done prudently, this technique can ease the handling of dialog errors by limiting the number of messages affected. For example, the application may choose to contrain a dialog to a certain number of messages before it is recycled. This might also be done according to the age of a dialog. See the end of the usp_send procedure for an example of recycling.



An example application that exercises the dialog pool is also included.



--------------------------------------------------------------------------


-- Dialog Pool Sample.


-- This sample shows how to create and use a shared pool of reuseable dialogs.


-- The purpose of reusing dialogs is to reduce the overhead of creating them.


-- The sample also shows how dialogs in the pool can be "recycled" by deleting


-- dialogs based on application criteria, such as number of messages sent.


-- This sample is largely based on Remus Rusanu's tutorials on reusing and


-- recycling conversations (rusanu.com/blog).


-- Contents: dialog pool and application using the pool.


----------------------------------------------------



USE master



GO



--------------------------------------------------------------------------


-- Create demo database section


--------------------------------------------------------------------------



IF EXISTS (SELECT name FROM sys.databases WHERE name = 'SsbDemoDb')


DROP DATABASE [SsbDemoDb];



CREATE DATABASE [SsbDemoDb]



GO



USE [SsbDemoDb];


GO



-- Create master key


IF NOT EXISTS(SELECT name FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')


CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password#123'



GO



--------------------------------------------------------------------------


-- Dialog pool section


--------------------------------------------------------------------------



--------------------------------------------------------------------------


-- The dialog pool table.


-- Obtain a conversation handle using from service, to service, and contract.


-- Also indicates age and usage of dialog for auditing purposes.


--------------------------------------------------------------------------


IF EXISTS (SELECT name FROM sys.tables WHERE name = 'DialogPool')


DROP TABLE [DialogPool]


GO


CREATE TABLE [DialogPool] (


FromService SYSNAME NOT NULL,


ToService SYSNAME NOT NULL,


OnContract SYSNAME NOT NULL,


Handle UNIQUEIDENTIFIER NOT NULL,


OwnerSPID INT NOT NULL,


CreationTime DATETIME NOT NULL,


SendCount BIGINT NOT NULL,


UNIQUE (Handle));


GO



--------------------------------------------------------------------------


-- Get dialog procedure.


-- Reuse a free dialog in the pool or create a new one in case


-- no free dialogs exist.


-- Input is from service, to service, and contract.


-- Output is dialog handle and count of message previously sent on dialog.


--------------------------------------------------------------------------


IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_get_dialog')


DROP PROC usp_get_dialog


GO


CREATE PROCEDURE [usp_get_dialog] (


@fromService SYSNAME,


@toService SYSNAME,


@onContract SYSNAME,


@dialogHandle UNIQUEIDENTIFIER OUTPUT,


@sendCount BIGINT OUTPUT)


AS


BEGIN


SET NOCOUNT ON;


DECLARE @dialog TABLE


(


FromService SYSNAME NOT NULL,


ToService SYSNAME NOT NULL,


OnContract SYSNAME NOT NULL,


Handle UNIQUEIDENTIFIER NOT NULL,


OwnerSPID INT NOT NULL,


CreationTime DATETIME NOT NULL,


SendCount BIGINT NOT NULL


);



-- Try to claim an unused dialog in [DialogPool]


-- READPAST option avoids blocking on locked dialogs.


BEGIN TRANSACTION;


DELETE @dialog;


UPDATE TOP(1) [DialogPool] WITH(READPAST)


SET OwnerSPID = @@SPID


OUTPUT INSERTED.* INTO @dialog


WHERE FromService = @fromService


AND ToService = @toService


AND OnContract = @OnContract


AND OwnerSPID = -1;


IF @@ROWCOUNT > 0


BEGIN


SET @dialogHandle = (SELECT Handle FROM @dialog);


SET @sendCount = (SELECT SendCount FROM @dialog);


END


ELSE


BEGIN


-- No free dialogs: need to create a new one


BEGIN DIALOG CONVERSATION @dialogHandle


FROM SERVICE @fromService


TO SERVICE @toService


ON CONTRACT @onContract


WITH ENCRYPTION = OFF;


INSERT INTO [DialogPool]


(FromService, ToService, OnContract, Handle, OwnerSPID,


CreationTime, SendCount)


VALUES


(@fromService, @toService, @onContract, @dialogHandle, @@SPID,


GETDATE(), 0);


SET @sendCount = 0;


END


COMMIT


END;


GO



--------------------------------------------------------------------------


-- Free dialog procedure.


-- Return the dialog to the pool.


-- Inputs are dialog handle and updated send count.


--------------------------------------------------------------------------


IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_free_dialog')


DROP PROC usp_free_dialog


GO


CREATE PROCEDURE [usp_free_dialog] (


@dialogHandle UNIQUEIDENTIFIER,


@sendCount BIGINT)


AS


BEGIN


SET NOCOUNT ON;


DECLARE @rowcount INT;


DECLARE @string VARCHAR(50);



BEGIN TRANSACTION;



-- Release dialog by setting OwnerSPID to -1.


UPDATE [DialogPool] SET OwnerSPID = -1, SendCount = @sendCount WHERE Handle = @dialogHandle;


SELECT @rowcount = @@ROWCOUNT;


IF @rowcount = 0


BEGIN


SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50)));


RAISERROR('usp_free_dialog: dialog %s not found in dialog pool', 16, 1, @string) WITH LOG;


END


ELSE IF @rowcount > 1


BEGIN


SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50)));


RAISERROR('usp_free_dialog: duplicate dialog %s found in dialog pool', 16, 1, @string) WITH LOG;


END



COMMIT


END;


GO



--------------------------------------------------------------------------


-- Delete dialog procedure.


-- Delete the dialog from the pool. This does not end the dialog.


-- Input is dialog handle.


--------------------------------------------------------------------------


IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_delete_dialog')


DROP PROC usp_delete_dialog


GO


CREATE PROCEDURE [usp_delete_dialog] (


@dialogHandle UNIQUEIDENTIFIER)


AS


BEGIN


SET NOCOUNT ON;



BEGIN TRANSACTION;


DELETE [DialogPool] WHERE Handle = @dialogHandle;


COMMIT


END;


GO



--------------------------------------------------------------------------


-- Application setup section.


--------------------------------------------------------------------------



--------------------------------------------------------------------------


-- Send messages from initiator to target.


-- Initiator uses dialogs from the dialog pool.


-- Initiator also retires dialogs based on application criteria,


-- which results in recycling dialogs in the pool.


--------------------------------------------------------------------------



-- This table stores the messages on the target side


IF EXISTS (SELECT name FROM sys.tables WHERE name = 'MsgTable')


DROP TABLE MsgTable


GO


CREATE TABLE MsgTable ( message_type SYSNAME, message_body NVARCHAR(4000))


GO



-- Activated store proc for the initiator to receive messages.


CREATE PROCEDURE initiator_queue_activated_procedure


AS


BEGIN


DECLARE @handle UNIQUEIDENTIFIER;


DECLARE @message_type SYSNAME;



BEGIN TRANSACTION;


WAITFOR (


RECEIVE TOP(1) @handle = [conversation_handle],


@message_type = [message_type_name]


FROM [SsbInitiatorQueue]), TIMEOUT 5000;



IF @@ROWCOUNT = 1


BEGIN


-- Expect target response to EndOfStream message.


IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'


BEGIN


END CONVERSATION @handle;


END


END


COMMIT


END;


GO



-- Activated store proc for the target to receive messages.


CREATE PROCEDURE target_queue_activated_procedure


AS


BEGIN


-- Variable table for received messages.


DECLARE @receive_table TABLE(


queuing_order BIGINT,


conversation_handle UNIQUEIDENTIFIER,


message_type_name SYSNAME,


message_body VARCHAR(MAX));



-- Cursor for received message table.


DECLARE message_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY


FOR SELECT


conversation_handle,


message_type_name,


message_body


FROM @receive_table ORDER BY queuing_order;



DECLARE @conversation_handle UNIQUEIDENTIFIER;


DECLARE @message_type SYSNAME;


DECLARE @message_body VARCHAR(4000);



-- Error variables.


DECLARE @error_number INT;


DECLARE @error_message VARCHAR(4000);


DECLARE @error_severity INT;


DECLARE @error_state INT;


DECLARE @error_procedure SYSNAME;


DECLARE @error_line INT;


DECLARE @error_dialog VARCHAR(50);



BEGIN TRY


WHILE (1 = 1)


BEGIN


BEGIN TRANSACTION;



-- Receive all available messages into the table.


-- Wait 5 seconds for messages.


WAITFOR (


RECEIVE


[queuing_order],


[conversation_handle],


[message_type_name],


CAST([message_body] AS VARCHAR(4000))


FROM [SsbTargetQueue]


INTO @receive_table


), TIMEOUT 5000;



IF @@ROWCOUNT = 0


BEGIN


COMMIT;


BREAK;


END


ELSE


BEGIN


OPEN message_cursor;


WHILE (1=1)


BEGIN


FETCH NEXT FROM message_cursor


INTO @conversation_handle,


@message_type,


@message_body;



IF (@@FETCH_STATUS != 0) BREAK;



-- Process a message.


-- If an exception occurs, catch and attempt to recover.


BEGIN TRY



IF @message_type = 'SsbMsgType'


BEGIN


-- process the msg. Here we will just insert it into a table


INSERT INTO MsgTable values(@message_type, @message_body);


END


ELSE IF @message_type = 'EndOfStream'


BEGIN


-- initiator is signaling end of message stream: end the dialog


END CONVERSATION @conversation_handle;


END


ELSE IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'


BEGIN


-- If the message_type indicates that the message is an error,


-- raise the error and end the conversation.


WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb)


SELECT


@error_number = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Code)[1]', 'INT'),


@error_message = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Description)[1]', 'VARCHAR(4000)');


SET @error_dialog = CAST(@conversation_handle AS VARCHAR(50));


RAISERROR('Error in dialog %s: %s (%i)', 16, 1, @error_dialog, @error_message, @error_number);


END CONVERSATION @conversation_handle;


END


END TRY


BEGIN CATCH


SET @error_number = ERROR_NUMBER();


SET @error_message = ERROR_MESSAGE();


SET @error_severity = ERROR_SEVERITY();


SET @error_state = ERROR_STATE();


SET @error_procedure = ERROR_PROCEDURE();


SET @error_line = ERROR_LINE();



IF XACT_STATE() = -1


BEGIN


-- The transaction is doomed. Only rollback possible.


-- This could disable the queue if done 5 times consecutively!


ROLLBACK TRANSACTION;



-- Record the error.


BEGIN TRANSACTION;


INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message,


@error_severity, @error_state, @error_procedure, @error_line, 1);


COMMIT;



-- For this level of error, it is best to exit the proc


-- and give the queue monitor control.


-- Breaking to the outer catch will accomplish this.


RAISERROR ('Message processing error', 16, 1);


END


ELSE IF XACT_STATE() = 1


BEGIN


-- Record error and continue processing messages.


-- Failing message could also be put aside for later processing here.


-- Otherwise it will be discarded.


INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message,


@error_severity, @error_state, @error_procedure, @error_line, 0);


END


END CATCH


END


CLOSE message_cursor;


DELETE @receive_table;


END


COMMIT;


END


END TRY


BEGIN CATCH


-- Process the error and exit the proc to give the queue monitor control


SET @error_number = ERROR_NUMBER();


SET @error_message = ERROR_MESSAGE();


SET @error_severity = ERROR_SEVERITY();


SET @error_state = ERROR_STATE();


SET @error_procedure = ERROR_PROCEDURE();


SET @error_line = ERROR_LINE();



IF XACT_STATE() = -1


BEGIN


-- The transaction is doomed. Only rollback possible.


-- This could disable the queue if done 5 times consecutively!


ROLLBACK TRANSACTION;



-- Record the error.


BEGIN TRANSACTION;


INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message,


@error_severity, @error_state, @error_procedure, @error_line, 1);


COMMIT;


END


ELSE IF XACT_STATE() = 1


BEGIN


-- Record error and commit transaction.


-- Here you could also save anything else you want before exiting.


INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message,


@error_severity, @error_state, @error_procedure, @error_line, 0);


COMMIT;


END


END CATCH


END;


GO



-- Table to store processing errors.


IF EXISTS (SELECT name FROM sys.tables WHERE name = 'target_processing_errors')


DROP TABLE target_processing_errors;


GO



CREATE TABLE target_processing_errors (error_conversation UNIQUEIDENTIFIER, error_number INT,


error_message VARCHAR(4000), error_severity INT, error_state INT, error_procedure SYSNAME NULL,


error_line INT, doomed_transaction TINYINT)


GO



-- Create Initiator and Target side SSB entities


CREATE MESSAGE TYPE SsbMsgType VALIDATION = WELL_FORMED_XML;


CREATE MESSAGE TYPE EndOfStream;


CREATE CONTRACT SsbContract


(


SsbMsgType SENT BY INITIATOR,


EndOfStream SENT BY INITIATOR


);


CREATE QUEUE SsbInitiatorQueue


WITH ACTIVATION (


STATUS = ON,


MAX_QUEUE_READERS = 1,


PROCEDURE_NAME = [initiator_queue_activated_procedure],


EXECUTE AS OWNER);


CREATE QUEUE SsbTargetQueue


WITH ACTIVATION (


STATUS = ON,


MAX_QUEUE_READERS = 1,


PROCEDURE_NAME = [target_queue_activated_procedure],


EXECUTE AS OWNER);



CREATE SERVICE SsbInitiatorService ON QUEUE SsbInitiatorQueue;


CREATE SERVICE SsbTargetService ON QUEUE SsbTargetQueue (SsbContract);


GO



-- SEND procedure. Uses a dialog from the dialog pool.


--


IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_send')


DROP PROC usp_send


GO


CREATE PROCEDURE [usp_send] (


@fromService SYSNAME,


@toService SYSNAME,


@onContract SYSNAME,


@messageType SYSNAME,


@messageBody NVARCHAR(MAX))


AS


BEGIN


SET NOCOUNT ON;


DECLARE @dialogHandle UNIQUEIDENTIFIER;


DECLARE @sendCount BIGINT;


DECLARE @counter INT;


DECLARE @error INT;



SELECT @counter = 1;


BEGIN TRANSACTION;


-- Will need a loop to retry in case the dialog is


-- in a state that does not allow transmission


--


WHILE (1=1)


BEGIN


-- Claim a dialog from the dialog pool.


-- A new one will be created if none are available.


--


EXEC usp_get_dialog @fromService, @toService, @onContract, @dialogHandle OUTPUT, @sendCount OUTPUT;



-- Attempt to SEND on the dialog


--


IF (@messageBody IS NOT NULL)


BEGIN


-- If the @messageBody is not null it must be sent explicitly


SEND ON CONVERSATION @dialogHandle MESSAGE TYPE @messageType (@messageBody);


END


ELSE


BEGIN


-- Messages with no body must *not* specify the body,


-- cannot send a NULL value argument


SEND ON CONVERSATION @dialogHandle MESSAGE TYPE @messageType;


END



SELECT @error = @@ERROR;


IF @error = 0


BEGIN


-- Successful send, increment count and exit the loop


--


SET @sendCount = @sendCount + 1;


BREAK;


END



SELECT @counter = @counter+1;


IF @counter > 10


BEGIN


-- We failed 10 times in a  row, something must be broken


--


RAISERROR('Failed to SEND on a conversation for more than 10 times. Error %i.', 16, 1, @error) WITH LOG;


BREAK;


END



-- Delete the associated dialog from the table and try again


--


EXEC usp_delete_dialog @dialogHandle;


SELECT @dialogHandle = NULL;


END



-- "Criterion" for dialog pool removal is send count > 1000.


-- Modify to suit application.


-- When deleting also inform the target to end the dialog.


IF @sendCount > 1000


BEGIN


EXEC usp_delete_dialog @dialogHandle ;


SEND ON CONVERSATION @dialogHandle MESSAGE TYPE [EndOfStream];


END


ELSE


BEGIN


-- Free the dialog.


EXEC usp_free_dialog @dialogHandle, @sendCount;


END


COMMIT


END;


GO



------------------------------------------------------------------------------------


-- Run application section


------------------------------------------------------------------------------------



-- Send some messages


exec usp_send N'SsbInitiatorService', N'SsbTargetService', N'SsbContract', N'SsbMsgType', N'<xml>This is a well formed XML Message1.</xml>'


exec usp_send N'SsbInitiatorService', N'SsbTargetService', N'SsbContract', N'SsbMsgType', N'<xml>This is a well formed XML Message2.</xml>'


exec usp_send N'SsbInitiatorService', N'SsbTargetService', N'SsbContract', N'SsbMsgType', N'<xml>This is a well formed XML Message3.</xml>'


exec usp_send N'SsbInitiatorService', N'SsbTargetService', N'SsbContract', N'SsbMsgType', N'<xml>This is a well formed XML Message4.</xml>'


exec usp_send N'SsbInitiatorService', N'SsbTargetService', N'SsbContract', N'SsbMsgType', N'<xml>This is a well formed XML Message5.</xml>'


GO



-- Show the dialog pool


SELECT * FROM [DialogPool]


GO



-- Show the dialogs used.


SELECT * FROM sys.conversation_endpoints;


GO



-- Check whether the TARGET side has processed the messages


SELECT * FROM MsgTable


GO


TRUNCATE TABLE MsgTable


GO