Forum > Databases

[SOLVED] MS SQL Server drop database error

<< < (2/4) > >>

af0815:
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:
I first connect as 'sa' (full control) to 'master', then I make my own database (if needed) and switch to that:


--- 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 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  [+][-]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";}};} ---  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:
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:

--- Quote from: Zvoni 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

--- End quote ---

I tried that, it gives the same error.

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

 :(

rvk:

--- Quote from: SymbolicFrank on September 26, 2022, 02:41:20 pm ---
--- 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";}};} ---    MyConnection.ExecuteDirect('drop database ' + s);
--- End quote ---
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  [+][-]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";}};} ---MyConnection.Close;MyConnection.DropDB;(The internal ExecDirectSQL takes care of reconnecting to the server with an empty Database property, so probably connecting to [master])

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version