Forum > Databases

[SOLVED] MS SQL Server drop database error

(1/4) > >>

SymbolicFrank:
I have a connection to an MS SQL Server. I'm running queries. At some point, I want to drop some databases. (Not the one that is in use.)


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---  query.SQL.Text := 'select * From fileList';  query.Open;  while not query.EOF do  begin    databases.Add(query.FieldByName('DatabaseName').AsString);    query.Next;  end;  query.Close;  MyTransaction.Commit;  MyTransaction.EndTransaction;  for s in databases do  begin    MyConnection.Close;    MyConnection.ExecuteDirect('drop database ' + s);  end;
This gives the error: "statement cannot be used inside a user transaction".

I tried a lot of other things, but none of them work.

Zvoni:
Did you set your Transaction to Active=False?

btw: Why are you closing your Connection? How is that supposed to work?

SymbolicFrank:
This still doesn't work:


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---  MyTransaction.Commit;  MyTransaction.Active := False;  for s in databases do  begin    MyConnection.ExecuteDirect('drop database ' + s);  end;
If I close the connection, it gets automatically reconnected, that was to try to "break" the transaction. If I remove the transaction altogether, I get a different error.

af0815:
It is not possible to drop a database if one is connected. Use explicit a connection to the master database,then you can make a try, but if a connection is still to the db, the delete is avoided. In the MSSMS you have an extra checkbox to explicit close all connection to the db by the server.

Edit: Some typos

Zvoni:

--- Quote from: af0815 on September 26, 2022, 02:16:36 pm ---It is not possible to drop a database if one is connected. Use explicit a connection to the master database,then you can make a try, but if a connection is still to the db, the delete is avoided. In the MSSMS you have an extra checkbox to explicit close all connection to the db by the server.

Edit: Some typos

--- End quote ---
Didn't think about that, but it would make sense.
OP, what's your connection-string look like? Specifically, are you connecting to the "Maintenance"-Schema/Database?

EDIT: Forgot: The User connected to your MS-SQL-Server does have the DROP DATABASE Grant?

Navigation

[0] Message Index

[#] Next page

Go to full version