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) }));

Error calling CreateReportHistorySnapshot method.

Hello,

I am getting an error when calling the CreateReportHistorySnapshot method from a custom assembly. The error is:

Response is not well-formed XML.

The data source is an XML file which is updated by the custom assembly before the call into the CreateReportHistorySnapshot method.

Has anyone seen this problem before or have any suggestions?

The Data Source XML is well-formed by the way... :-0)

I have had this error before when i wasnt the db admin. it seems there are a few errors along these lines where error messages flag up issues which are actually unrelated.

Try Permission and i would assume this will fix your error.

Andy

|||

Hi Andy,

Thanks for this!! Can you just clarify what would fix the error.

Many thanks.

|||

The user that is running the createsnapshot method needs to be set as the DB Owner within SQL, this fixed the same problem i was having with this a couple of months ago.

It is often the case with many of these areas, subcriptions is another area where db admin comes into play.

If you hear of another fix please let me know as this is not ideal for me as a long term solution.