CODEWITHSUNDEEP
sqlazure-sql-database
Jyothish
Jyothish

Reputation: 561

Drop multiple databases in SQl Azure

I would like to run a script to drop the multiple databases from SQL Azure as soon I finish using it. When I tried as following,

     DECLARE  @dbname varchar(100);
     DECLARE  @stmt nvarchar(3000);
     SET  @dbname = '6A732E0B';

     SELECT @stmt = (SELECT 'DROP DATABASE [' + name + ']; ' FROM sys.databases
     WHERE name LIKE '%' +@dbname +'%');
     EXEC sp_executesql @stmt;

SQL Azure throws error message as “The DROP DATABASE statement must be the only statement in the batch” Can somebody help me on this?

Upvotes: 0

Views: 1012

Answers (2)

Hans Olav Norheim
Hans Olav Norheim

Reputation: 194

This is a known limitation in SQL Azure - certain statements need to be in a batch by themselves to be executed. This includes CREATE/ALTER DATABASE, ALTER DATABASE and a few more.

To solve you problem, you can create a loop in you application where you iterate over all the databases and drop them by issuing DROP DATABASE statements in separate batches.

Upvotes: 1

Optillect Team
Optillect Team

Reputation: 1747

I believe this is a bug of SQL Azure. I've recently reported it to Microsoft: https://connect.microsoft.com/SQLServer/feedback/details/684160/sp-executesql-the-drop-database-statement-must-be-the-only-statement-in-the-batch

Upvotes: 0

Related Questions