0

I am trying to enable service broker with rollback immediate, per this post: Enable SQL Server Broker taking too long time

here's the query:

alter database fooDB set enable_broker with rollback immediate;

However it's giving me the below syntax errors in SQL Server 2012:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'immediate'.

How can I do this in SQL Sever 2012?

Community
  • 1
  • 1
drew
  • 97
  • 1
  • 11
  • 3
    Post the query giving you issues plz...appears your query is having this issue : "If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon", so we would need your query to help you here. – Twelfth Aug 22 '14 at 20:55
  • I've added the query, it was copied from the other threrad – drew Aug 22 '14 at 21:05
  • Does your real database have a space in the name? – Sean Lange Aug 22 '14 at 21:07
  • no space is in the real dB name... – drew Aug 22 '14 at 21:09
  • If your database has a space in the name it will cause a parsing error. alter database foo DB set enable_broker with rollback immediate; In this case you need to wrap your database name in square brackets. alter database [foo DB] set enable_broker with rollback immediate; – Sean Lange Aug 22 '14 at 21:09
  • @SeanLange, there are no spaces in the DB name. – Andrew Aug 22 '14 at 21:10
  • 1
    There is nothing wrong syntactically with that statement. There must be something else in your query window. – Sean Lange Aug 22 '14 at 21:10
  • @Andrew yeah the OP posted that the same time as my comment. – Sean Lange Aug 22 '14 at 21:11
  • Just FYI, this statement doesn't work if the server/database is participating in an "AlwaysOn" Availability Group cluster. – pmbAustin Aug 24 '14 at 02:44

2 Answers2

0

Maybe wrap it in it's own batch with preceding and terminating "GO" will help?

GO
alter database fooDB set enable_broker with rollback immediate;
GO

I just looked at the other post, are you trying to do this on a specific table or scripting is out to run dynamically?

Charles D.
  • 87
  • 3
0

The enable_broker option requires an exclusive database lock. There should be no sessions having any locks on the altered DB, and it includes the session you run it in, as well:

use master;

alter database fooDB set enable_broker with rollback immediate;
Roger Wolf
  • 7,307
  • 2
  • 24
  • 33