Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

SQL Server ENABLE_BROKER 2

By: emailgregn on Nov 1st, 2011  |  syntax: T-SQL  |  size: 1.94 KB  |  views: 71  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. USE master
  2. GO
  3. -- <gn>Drop the snapshot if it exists
  4. if db_id('Accounting_snapshot') is not null
  5. begin
  6.     alter database Accounting set single_user with rollback immediate;
  7.     drop database Accounting_snapshot;
  8. end
  9. go
  10. -- </gn>
  11. if db_id('Accounting') is not null
  12. begin
  13.     alter database Accounting set single_user with rollback immediate;
  14.     drop database Accounting;
  15. end
  16. go
  17.  
  18. create database Accounting;
  19. go
  20.  
  21. alter authorization on database::Accounting to sa;
  22.  
  23. use Accounting;
  24. go
  25.  
  26. create queue q;
  27. create service s on queue q ([DEFAULT]);
  28. go
  29.  
  30. -- <gn> 1 - Create a snapshot
  31. CREATE DATABASE [Accounting_Snapshot] ON ( NAME = Accounting, FILENAME = 'c:\projects\Accounting_Snapshot_dat.ss') AS SNAPSHOT OF Accounting
  32. go
  33. -- </gn>
  34. ALTER DATABASE [Accounting] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  35. go
  36. -- <gn> 2 - Restore with ENABLE_BROKER from the snapshot while in single user mode
  37. USE MASTER;
  38. RESTORE DATABASE Accounting FROM DATABASE_SNAPSHOT = 'Accounting_Snapshot' WITH ENABLE_BROKER;
  39. USE Accounting;
  40. go
  41. -- </gn>
  42.  
  43. declare @h uniqueidentifier;
  44. begin dialog conversation @h
  45.     from service [s]
  46.     to service N's'
  47.     with encryption = off;
  48. send on conversation @h ('Hello, World');
  49. go  
  50.  
  51. -- Give the queue a bit of time to process
  52. WAITFOR DELAY '0:00:02';
  53. GO
  54. -- Take a look after sending a message
  55. SELECT * FROM sys.transmission_queue; -- 1 row, transmission_status = "...single user ..."
  56. GO
  57.  
  58. -- Retry same multiuser with ENABLE_BROKER
  59. USE master
  60. GO
  61. ALTER DATABASE Accounting SET MULTI_USER, ENABLE_BROKER WITH NO_WAIT
  62. GO
  63.  
  64. use Accounting;
  65. go
  66.  
  67. -- wait for the message delivery
  68. WAITFOR (receive cast(message_body as varchar(8000)), * from q), timeout 60;
  69. GO
  70.  
  71. -- Look at xmit queue
  72. USE Accounting;
  73. SELECT * FROM sys.transmission_queue; -- 1 row, no error status.
  74. GO
  75.  
  76. -- Look again at xmit queue, in 10 seconds
  77. waitfor delay '00:00:10';
  78. USE Accounting;
  79. SELECT * FROM sys.transmission_queue; -- row is gone
  80. GO
  81.