Forum > Databases
[SOLVED] MS SQL Server drop database error
SymbolicFrank:
- 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:
--- 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).
--- End quote ---
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.html#msg131769
--- 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";}};} ---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:
Ah, yes, through a fresh connection. I didn't try it like that, but it works indeed :D
--- 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";}};} ---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.
Navigation
[0] Message Index
[*] Previous page