Drop Database SQL Server 2008 R2 Express Edition - Exception

Question

I am working on a a solution in C# where I can DROP a Database in SQL Server 2008 R2 Express Edition. I searched the WWW and found the following solution: Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I also tried to create the script from the SQL Server:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'apm4reform'
GO
USE [master]
GO
ALTER DATABASE [apm4reform] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object:  Database [apm4reform]    Script Date: 10/10/2013 00:58:06 ******/
DROP DATABASE [apm4reform]
GO

Here my code I use:

private static bool DropDatabase(SqlConnection tmpConn, string connectionString, string databaseName)
        {
            string sqlDropDBQuery;
            bool result = false;

            try
            {
                tmpConn.ConnectionString = connectionString;
                tmpConn.Open();
                SqlCommand thisCommand = new SqlCommand();
                thisCommand.Connection = tmpConn;
                sqlDropDBQuery = string.Format("EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'{0}'\n GO\n USE [master]\n GO\n ALTER DATABASE [{0}] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE GO\n USE [master]\n GO\n DROP DATABASE [{0}]\n GO\n", databaseName);
                thisCommand.CommandText = sqlDropDBQuery;

                thisCommand.ExecuteNonQuery();
                result = true;

            }
            catch (Exception ex)
            {
                result = false;
            }
            finally
            {
                tmpConn.Close();
            }

            return result;
        }

Unfortunately, I always get an exception:

Incorrect syntax near 'GO'. Incorrect syntax near 'GO'. Incorrect syntax near 'GO'.

I also tried without the escapes \n!

Any suggestions?

Thanks, tro

had the same issue yesterday, if you need all those commands executed seperately consider splitting the command string by `GO` and execute each of those command strings individually http://stackoverflow.com/questions/19247542/this-sql-code-works-when-being-executed-in-smms-but-not-when-called-via-executen — DrCopyPaste, Oct 10 '13 at 08:49

score 0 · Answer 1 · answered Oct 10 '13 at 08:14

0

GO is a statement delimiter undestood only by the Sql Server Management Studio.
Cannot be used in a statement by your C# code through an ExecuteNonQuery. Just remove it and be sure that every statement is terminated by a semicolon

sqlDropDBQuery = string.Format("EXEC msdb.dbo.sp_delete_database_backuphistory " + 
                 "@database_name = N'{0}';USE [master];" + 
                 "ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;" +
                 "USE [master];DROP DATABASE [{0}];", databaseName);

answered Oct 10 '13 at 08:14

Steve

213,761
22
232
286

got it, thanks! sqlDropDBQuery = string.Format("EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'{0}'; USE [master] ; ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; USE [master]; DROP DATABASE [{0}];", databaseName); – tro Oct 10 '13 at 08:18
And you don't need `USE master` in query. You should just set your connection string to target master. – Nenad Zivkovic Oct 10 '13 at 08:22

score 0 · Answer 2 · edited Oct 10 '13 at 08:39

0

You can try this. one

var s =string. Format( @"EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'{0}';

    USE master;

    ALTER DATABASE {0} SET  SINGLE_USER WITH ROLLBACK IMMEDIATE




    DROP DATABASE {0}",yourdbname);

edited Oct 10 '13 at 08:39

Nenad Zivkovic

18,221
6
42
55

answered Oct 10 '13 at 08:23

Drop Database SQL Server 2008 R2 Express Edition - Exception

2 Answers2