[SOLVED] MS SQL Server drop database error
lazarus / 16-Databases

----------------------------------------------------------------------
SymbolicFrank · 2022-09-26 10:06

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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
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 · 2022-09-26 10:49

Did you set your Transaction to Active=False?

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

----------------------------------------------------------------------
SymbolicFrank · 2022-09-26 12:04

This still doesn't work:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
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 · 2022-09-26 12:16

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 · 2022-09-26 12:22

Quote from: af0815 on September 26, 2022, 02:16:36 pmIt 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
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?

----------------------------------------------------------------------
af0815 · 2022-09-26 12:32

BTW, dropping a database should IMHO not be good idea for everday
work. I understand a drop of a table, but not of a db. If i need
shorttime stored data i use the tempdb of the server. If you need a
table for a longer time, it makes sense to create and delete a table.
if this happen often, so i say a truncate of the table is enough.

To create and delete a database on a MS SQL Server you need the
correct rights and often (more normal) this is avoided by the database
administrator for a good reason.

----------------------------------------------------------------------
SymbolicFrank · 2022-09-26 12:41

I first connect as 'sa' (full control) to 'master', then I make my own
database (if needed) and switch to that:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
query.SQL.Text := 'select name from sys.databases'; 
query.SQL.Add('where name = ''MyDatabase''');  query.Open;  if
query.EOF then  begin    MyTransaction.Commit;    query.Close;   
MyConnection.Close;    MyConnection.DatabaseName := 'MyDatabase';   
MyConnection.CreateDB;  end  else query.Close; 
MyConnection.ExecuteDirect('use MyDatabase'); 
MyConnection.ExecuteDirect('set ANSI_NULL_DFLT_ON on');
What is the property to close the connection?


BTW, this is to automatically restore one or more backups to convert
the data inside to a different application:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   fl :=
FindAllFiles(od.FileName, '*.bak', False, faAnyFile);  od.Free;   for
f in fl do  begin    query.SQL.Text := 'insert into FileList';   
query.SQL.Add('exec (''restore headeronly from disk = N''''' + f +
''''''')');    query.ExecSQL;    query.SQL.Text := 'insert into
PartList';    query.SQL.Add('exec (''restore filelistonly from disk =
N''''' + f + ''''''')');    query.ExecSQL;    MyTransaction.Commit; 
end;  fl.Free;   databases.Clear;  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.Active := False;  for s in databases do  begin   
MyConnection.ExecuteDirect('drop database ' + s);  end;  
//query.SQL.Text := ('exec (''restore database ''' + s + ''' from disk
= N''''' + f + ''''''')');

----------------------------------------------------------------------
Zvoni · 2022-09-26 13:14

First codeblock, line 13: You are connected to "MyDatabase" (...
ExecuteDirect "use MyDatabase")
Before you drop, issue/ExecuteDirect a "use sys" (or is it "use
master"?) to connect back to the master-schema (second codeblock after
line 26, before line 27)
Untested though, since  i don't have an MS-SQL-Server

----------------------------------------------------------------------
SymbolicFrank · 2022-09-26 13:34

Quote from: Zvoni on September 26, 2022, 03:14:05 pmFirst codeblock,
line 13: You are connected to "MyDatabase" (... ExecuteDirect "use
MyDatabase")
Before you drop, issue/ExecuteDirect a "use sys" (or is it "use
master"?) to connect back to the master-schema (second codeblock after
line 26, before line 27)
Untested though, since  i don't have an MS-SQL-Server

I tried that, it gives the same error.

I had MS SQL Server management studio open, I closed it, same error.

----------------------------------------------------------------------
rvk · 2022-09-26 14:22

Quote from: SymbolicFrank on September 26, 2022, 02:41:20 pmCode:
Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
MyConnection.ExecuteDirect('drop database ' + s);There is a
TMSSQLConnection.DropDB which uses an internal ExecuteDirectSQL which
is slightly different from ExecuteDirect.
So MyConnection.DropDB might work better.

You do need to call MyConnection.Close first.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
MyConnection.Close;MyConnection.DropDB;(The internal ExecDirectSQL
takes care of reconnecting to the server with an empty Database
property, so probably connecting to [master])

----------------------------------------------------------------------
Zvoni · 2022-09-26 14:28

Just found something for python:
Can you add "autocommit=True" to your connection-Params? (No idea if
it's the equal-sign or colon  "autocommit: True")
https://stackoverflow.com/questions/58557089/sqlalchemy-utils-drop-
database-statement-cannot-be-used-inside-a-user-transactio

EDIT: Just looked at the Source-code: a simple "AUTOCOMMIT=True" in
your Connection-Params should be enough

----------------------------------------------------------------------
SymbolicFrank · 2022-09-27 07:10

Thanks for the help!

This works:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
MyConnection.ExecuteDirect('use master');  i := 0;  while i <
databases.Count do  begin    s := databases[i];    query.SQL.Text :=
'select name from sys.databases';    query.SQL.Add('where name = ''' +
s + '''');    query.Open;    if query.EOF then databases.Delete(i)   
else Inc(i);    query.Close;  end;  for s in databases do  begin   
MyConnection.Close;    MyConnection.DatabaseName := s;   
MyConnection.DropDB;  end;
So, you have to use the special methods in the connection to
manipulate databases.

----------------------------------------------------------------------
SymbolicFrank · 2022-09-27 11:43

Well, in retrospect, it shouldn't be a surprise that restoring the
database doesn't work, either. And there isn't a method on the
connection to do that. And I don't know if this helps:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
MyConnection.Params.Add('AutoCommit=true');
Because it breaks all the regular transactions.


Holy ***, what a very frustrating database to work with! It is just
like something made by Microsoft!

Oh, wait...


Edit: using the autocommit for the restore only doesn't work, either:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
databases.Clear;  MyConnection.ExecuteDirect('use ' + MyDatabase); 
query.SQL.Text := 'select * from BackupTable order by FileName'; 
query.Open;  while not query.EOF do  begin    q2.SQL.Text := 'restore
database ' + query.FieldByName('DatabaseName').AsString;   
q2.SQL.Add('from disk = N''' + query.FieldByName('FileName').AsString
+ '''');    q2.SQL.Add('with file = 1,');    q2.SQL.Add('move N''' +
query.FieldByName('LogicalName').AsString + ''' to ');   
q2.SQL.Add('N''' + db +     
ExtractFileName(query.FieldByName('PhysicalName').AsString) + ''','); 
  query.Next;    q2.SQL.Add('move N''' +
query.FieldByName('LogicalName').AsString + ''' to ');   
q2.SQL.Add('N''' + db +     
ExtractFileName(query.FieldByName('PhysicalName').AsString) + ''','); 
  q2.SQL.Add('nounload,');    q2.SQL.Add('stats = 5');   
databases.Add(q2.SQL.Text);    query.Next;  end;  
MyConnection.Connected := False;  MyConnection.DatabaseName :=
'master';  MyConnection.Params.Add('AutoCommit=true');  for s in
databases do  begin    MyConnection.ExecuteDirect(s);   
MyConnection.Close;  end;
(There's two records in the BackupTable for each database: one for the
.mdf and one for the .ldf. They're from the 'restore headeronly' and
'restore filelistonly' actions.)

----------------------------------------------------------------------
SymbolicFrank · 2022-09-27 13:18

Creating a descendant of TMSSQLConnection and making ExecuteDirectSQL
public doesn't work, because it's not protected but private.

----------------------------------------------------------------------
rvk · 2022-09-27 13:28

Quote from: SymbolicFrank on September 27, 2022, 03:18:23 pmCreating a
descendant of TMSSQLConnection and making ExecuteDirectSQL public
doesn't work, because it's not protected but private. 
Why would you want that? It doesn't do anything special.
Just reconnecting with an empty databasename (presumably it'll connect
to sys/master then) and dropping the database which was previously in
database-property.

So you might try to use an empty database-property and see if that
works.
Or tell us what you want and what's not working (because that's all
over the place now).

----------------------------------------------------------------------
SymbolicFrank · 2022-09-27 13:58

- The Create Database now works (CreateDB method).
- SETting options now works (has to be the first action).
- DROPping the database now works (DropDB method).
- RESTORE-ing the database refuses to work, even if I save the SQL
commands to a TStrings and do it on a fresh connection with Autocommit
(see last code fragment).

All have the same problem: they don't work if you use a transaction,
which you have to do, otherwise the Connection doesn't work. So that's
probably why those methods exist.

As if they do something differently: I didn't see it, but it does
work. Ergo, they do.

----------------------------------------------------------------------
rvk · 2022-09-27 14:26

Quote from: SymbolicFrank on September 27, 2022, 03:58:08 pm- RESTORE-
ing the database refuses to work, even if I save the SQL commands to a
TStrings and do it on a fresh connection with Autocommit (see last
code fragment).Yeah, I feel your pain. If they just made the
Execute(SQL: String) public (or at least protected) you could do
something direct with the database-server.
Now it's just a pain.

Here is supposedly working code for BACKUP in MSSQL.
Does this work for RESTORE too?

https://forum.lazarus.freepascal.org/index.php/topic,22346.msg131769.h
tml#msg131769

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  Conn:
TMSSQLConnection;  Tran: TSQLTransaction;begin  Conn :=
TMSSQLConnection.create(nil);  Tran := TSQLTransaction.create(nil); 
try    //adjust credentials    Conn.HostName := '127.0.0.1';   
Conn.UserName := ''; //trusted authentication/SSPI    Conn.Password :=
''; //trusted authentication/SSPI    Conn.DatabaseName := 'master';  
Conn.Params.Add('AutoCommit=true');    Conn.Transaction := Tran;   
Conn.Open;    // adjust path       Conn.ExecuteDirect('backup database
test to disk = N''C:\TEMP\TEST2.bak'' with format, init');   
Conn.Close;  finally    Tran.Free;    Conn.Free;  end;end;

----------------------------------------------------------------------
SymbolicFrank · 2022-09-27 14:47

Ah, yes, through a fresh connection. I didn't try it like that, but it
works indeed

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
ExecuteImmediately(User, Pass, Comm: string): string;var  Conn:
TMSSQLConnection;  Tran: TSQLTransaction;begin  Result := '';  Conn :=
TMSSQLConnection.create(nil);  Tran := TSQLTransaction.create(nil); 
try    //adjust credentials    Conn.HostName := '127.0.0.1';   
Conn.UserName := User;    Conn.Password := Pass;    Conn.DatabaseName
:= 'master';    Conn.Params.Add('AutoCommit=true');   
Conn.Transaction := Tran;    Conn.Open;    // adjust path    try     
Conn.ExecuteDirect(Comm);    except      on e:Exception do Result :=
e.Message;    end;    Conn.Close;  finally    Tran.Free;   
Conn.Free;  end;end;
I did something like that, but I executed all of the commands through
the fresh connection, so only the first command succeeded.