Thursday, March 22, 2012

Error Dropping Database using SMO

I use the method shown below to drop a SQL Server database using SMO. On occasion, I'll get the following error message when this method is called. I'm not sure what I can do to prevent an error like this from happening. I don't think I have any control over it. The error message is this:

Drop all active database connections failed for Server 'SDTPLCHost1\SQLServer2005'. : An exception occurred while executing a Transact-SQL statement or batch.: Process ID 57 is not an active process ID.

Here is the method I wrote that uses SMO to drop a database:

public void DropDatabase(string serverName, string databaseName)

{

Server smoServer;

try

{

smoServer = new Server(serverName);

if (!smoServer.Databases.Contains(databaseName))

return;

smoServer.KillAllProcesses(databaseName);

smoServer.KillDatabase(databaseName);

}

catch (Exception ex)

{

throw new Exception(BuildExceptionMessage(ex));

}

}

Use the KILLDatabase method, they changed it to bahave the way you would expect to:

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9


Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

I don't understand. You said to use the KillDatabase method. I am using the KillDatabase method! You then include a link to an article which seems to imply that the KillDatabase method doesn't work the way it should and then you supply some sample code to make dropping a database work the way it should.

So, am I correct that in order to drop a database, I should use the code you referenced in the link? It seems I should. I've used this technique before to drop a database (without using SMO) and it's always worked.

Amos.

|||Sorry, forgot to mentioned that they kept track on that issue with the SMO connect bug, reflecting the sources they changed it to:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));

No comments:

Post a Comment