tag:blogger.com,1999:blog-89391783300571461722012-02-16T14:20:16.227ZTips, tricks and just plain rants about MS-SQL.[]v[]0rph1uZhttp://www.blogger.com/profile/05010333857594337196noreply@blogger.comBlogger21100tag:blogger.com,1999:blog-8939178330057146172.post-43879973010690506762008-02-12T11:49:00.000Z2008-02-12T12:50:40.525ZWe were troubleshooting a problem with Service broker today, and realised the queue was being populated but wasn't being processed.<br /><br />Further investigation revealed that since our database had been migrated from SQL 2000 to SQL 2005 the "is_broker_enabled" was set to "off" and hence the queue wasn't being processed.<br /><br />To enable it we ran the following command<br /><br />ALTER DATABASE <em>[databasename]</em> SET ENABLE_BROKER<br /><br />Now, this command when executed requires the session to have exclusive access to the database, so make sure you either close all other sessions active as well as inactive; or run the command with the termination options:<br /><br />ROLLBACK AFTER integer [SECONDS] - Specifies whether to roll back after the specified number of seconds.<br /><br />ROLLBACK IMMEDIATE - Specifies whether to roll back immediately.<br /><br />NO_WAIT - Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/8939178330057146172-4387997301069050676?l=chiragroy.blogspot.com' alt='' /></div>[]v[]0rph1uZhttp://www.blogger.com/profile/05010333857594337196noreply@blogger.com0tag:blogger.com,1999:blog-8939178330057146172.post-2253248956838702102008-02-03T14:13:00.000Z2008-02-03T14:17:02.092ZThis is my very first blogging experience and will try to make this a regular rant on my varied experiences with the world of SQL. I consider this to be my way of giving back to the SQL community as I myself have gained a lot from the vast resources of knowledge and personal experiences of SQL guru's and novices alike. So let the quest begin.....<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/8939178330057146172-225324895683870210?l=chiragroy.blogspot.com' alt='' /></div>[]v[]0rph1uZhttp://www.blogger.com/profile/05010333857594337196noreply@blogger.com