Recent

Author Topic: [SOLVED] MS SQL Server drop database error  (Read 1282 times)

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
[SOLVED] MS SQL Server drop database error
« on: September 26, 2022, 12:06:21 pm »
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][+][-]
  1.   query.SQL.Text := 'select * From fileList';
  2.   query.Open;
  3.   while not query.EOF do
  4.   begin
  5.     databases.Add(query.FieldByName('DatabaseName').AsString);
  6.     query.Next;
  7.   end;
  8.   query.Close;
  9.   MyTransaction.Commit;
  10.   MyTransaction.EndTransaction;
  11.   for s in databases do
  12.   begin
  13.     MyConnection.Close;
  14.     MyConnection.ExecuteDirect('drop database ' + s);
  15.   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.
« Last Edit: September 27, 2022, 04:52:07 pm by SymbolicFrank »

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: MS SQL Server drop database error
« Reply #1 on: September 26, 2022, 12:49:16 pm »
Did you set your Transaction to Active=False?

btw: Why are you closing your Connection? How is that supposed to work?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: MS SQL Server drop database error
« Reply #2 on: September 26, 2022, 02:04:29 pm »
This still doesn't work:

Code: Pascal  [Select][+][-]
  1.   MyTransaction.Commit;
  2.   MyTransaction.Active := False;
  3.   for s in databases do
  4.   begin
  5.     MyConnection.ExecuteDirect('drop database ' + s);
  6.   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

  • Hero Member
  • *****
  • Posts: 1284
Re: MS SQL Server drop database error
« Reply #3 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
« Last Edit: September 26, 2022, 02:20:35 pm by af0815 »
regards
Andreas

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: MS SQL Server drop database error
« Reply #4 on: September 26, 2022, 02:22:14 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
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?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

af0815

  • Hero Member
  • *****
  • Posts: 1284
Re: MS SQL Server drop database error
« Reply #5 on: September 26, 2022, 02:32:00 pm »
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.

   
regards
Andreas

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: MS SQL Server drop database error
« Reply #6 on: September 26, 2022, 02:41:20 pm »
I first connect as 'sa' (full control) to 'master', then I make my own database (if needed) and switch to that:

Code: Pascal  [Select][+][-]
  1.   query.SQL.Text := 'select name from sys.databases';
  2.   query.SQL.Add('where name = ''MyDatabase''');
  3.   query.Open;
  4.   if query.EOF then
  5.   begin
  6.     MyTransaction.Commit;
  7.     query.Close;
  8.     MyConnection.Close;
  9.     MyConnection.DatabaseName := 'MyDatabase';
  10.     MyConnection.CreateDB;
  11.   end
  12.   else query.Close;
  13.   MyConnection.ExecuteDirect('use MyDatabase');
  14.   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][+][-]
  1.   fl := FindAllFiles(od.FileName, '*.bak', False, faAnyFile);
  2.   od.Free;
  3.  
  4.   for f in fl do
  5.   begin
  6.     query.SQL.Text := 'insert into FileList';
  7.     query.SQL.Add('exec (''restore headeronly from disk = N''''' + f + ''''''')');
  8.     query.ExecSQL;
  9.     query.SQL.Text := 'insert into PartList';
  10.     query.SQL.Add('exec (''restore filelistonly from disk = N''''' + f + ''''''')');
  11.     query.ExecSQL;
  12.     MyTransaction.Commit;
  13.   end;
  14.   fl.Free;
  15.  
  16.   databases.Clear;
  17.   query.SQL.Text := 'select * From fileList';
  18.   query.Open;
  19.   while not query.EOF do
  20.   begin
  21.     databases.Add(query.FieldByName('DatabaseName').AsString);
  22.     query.Next;
  23.   end;
  24.   query.Close;
  25.   MyTransaction.Commit;
  26.   MyTransaction.Active := False;
  27.   for s in databases do
  28.   begin
  29.     MyConnection.ExecuteDirect('drop database ' + s);
  30.   end;
  31.  
  32.   //query.SQL.Text := ('exec (''restore database ''' + s + ''' from disk = N''''' + f + ''''''')');
  33.  

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: MS SQL Server drop database error
« Reply #7 on: September 26, 2022, 03:14:05 pm »
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
« Last Edit: September 26, 2022, 03:17:05 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: MS SQL Server drop database error
« Reply #8 on: September 26, 2022, 03:34:42 pm »
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

I tried that, it gives the same error.

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

 :(

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: MS SQL Server drop database error
« Reply #9 on: September 26, 2022, 04:22:28 pm »
Code: Pascal  [Select][+][-]
  1.     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][+][-]
  1. MyConnection.Close;
  2. MyConnection.DropDB;
(The internal ExecDirectSQL takes care of reconnecting to the server with an empty Database property, so probably connecting to [master])
« Last Edit: September 26, 2022, 04:41:34 pm by rvk »

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: MS SQL Server drop database error
« Reply #10 on: September 26, 2022, 04:28:32 pm »
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
« Last Edit: September 26, 2022, 04:39:06 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: MS SQL Server drop database error
« Reply #11 on: September 27, 2022, 09:10:33 am »
Thanks for the help!

This works:

Code: Pascal  [Select][+][-]
  1.   MyConnection.ExecuteDirect('use master');
  2.   i := 0;
  3.   while i < databases.Count do
  4.   begin
  5.     s := databases[i];
  6.     query.SQL.Text := 'select name from sys.databases';
  7.     query.SQL.Add('where name = ''' + s + '''');
  8.     query.Open;
  9.     if query.EOF then databases.Delete(i)
  10.     else Inc(i);
  11.     query.Close;
  12.   end;
  13.   for s in databases do
  14.   begin
  15.     MyConnection.Close;
  16.     MyConnection.DatabaseName := s;
  17.     MyConnection.DropDB;
  18.   end;

So, you have to use the special methods in the connection to manipulate databases.
« Last Edit: September 27, 2022, 10:27:04 am by SymbolicFrank »

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: [SOLVED] MS SQL Server drop database error
« Reply #12 on: September 27, 2022, 01:43:19 pm »
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][+][-]
  1. 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][+][-]
  1.   databases.Clear;
  2.   MyConnection.ExecuteDirect('use ' + MyDatabase);
  3.   query.SQL.Text := 'select * from BackupTable order by FileName';
  4.   query.Open;
  5.   while not query.EOF do
  6.   begin
  7.     q2.SQL.Text := 'restore database ' + query.FieldByName('DatabaseName').AsString;
  8.     q2.SQL.Add('from disk = N''' + query.FieldByName('FileName').AsString + '''');
  9.     q2.SQL.Add('with file = 1,');
  10.     q2.SQL.Add('move N''' + query.FieldByName('LogicalName').AsString + ''' to ');
  11.     q2.SQL.Add('N''' + db +
  12.       ExtractFileName(query.FieldByName('PhysicalName').AsString) + ''',');
  13.     query.Next;
  14.     q2.SQL.Add('move N''' + query.FieldByName('LogicalName').AsString + ''' to ');
  15.     q2.SQL.Add('N''' + db +
  16.       ExtractFileName(query.FieldByName('PhysicalName').AsString) + ''',');
  17.     q2.SQL.Add('nounload,');
  18.     q2.SQL.Add('stats = 5');
  19.     databases.Add(q2.SQL.Text);
  20.     query.Next;
  21.   end;
  22.  
  23.   MyConnection.Connected := False;
  24.   MyConnection.DatabaseName := 'master';
  25.   MyConnection.Params.Add('AutoCommit=true');
  26.   for s in databases do
  27.   begin
  28.     MyConnection.ExecuteDirect(s);
  29.     MyConnection.Close;
  30.   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.)
« Last Edit: September 27, 2022, 02:00:50 pm by SymbolicFrank »

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: MS SQL Server drop database error
« Reply #13 on: September 27, 2022, 03:18:23 pm »
Creating a descendant of TMSSQLConnection and making ExecuteDirectSQL public doesn't work, because it's not protected but private.  %)

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: MS SQL Server drop database error
« Reply #14 on: September 27, 2022, 03:28:48 pm »
Creating 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).

 

TinyPortal © 2005-2018