Forum > Databases

[SOLVED] MS SQL Server drop database error

<< < (4/4)

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

Go to full version