Forum > Databases
[SOLVED] MS SQL Server drop database error
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