CODEWITHSUNDEEP
sqlazure-sql-database
Harish Mohanan
Harish Mohanan

Reputation: 184

'The ALTER DATABASE statement must be the only statement in the batch'

I am trying to execute the following query on my Windows Azure database use master

ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE Test SET MULTI_USER

When try to run it I get the error

The ALTER DATABASE statement must be the only statement in the batch.

Any suggestions on how to tackle this. What I basically want is to close all present connection on my database.

Upvotes: 2

Views: 1724

Answers (2)

Sirisha Chamarthi
Sirisha Chamarthi

Reputation: 1293

If you just want to close the connections, you may try setting it to restricted_user. ALTER DATABASE SINGLE USER IS NOT SUPPORTED in V12. If you want to close all the connections and is ok if they reconnect, you can kill them by using Kill statement in combination with sys.dm_Exec_Requests/ The other option is to set ReadOnly and then Read_write. There is no single user option in SQL DB.

Upvotes: 0

Kritner
Kritner

Reputation: 13765

As the error implies, your alter statement can be the only in the batch - something like this should work:

GO
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO
ALTER DATABASE Test SET MULTI_USER 
GO

GO signifies the end of a batch so each alter statement would be a separate batch.

Upvotes: 4

Related Questions