Recent

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

SymbolicFrank

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

  • Hero Member
  • *****
  • Posts: 6112
Re: MS SQL Server drop database error
« Reply #16 on: September 27, 2022, 04:26:05 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).
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  [Select][+][-]
  1. var
  2.   Conn: TMSSQLConnection;
  3.   Tran: TSQLTransaction;
  4. begin
  5.   Conn := TMSSQLConnection.create(nil);
  6.   Tran := TSQLTransaction.create(nil);
  7.   try
  8.     //adjust credentials
  9.     Conn.HostName := '127.0.0.1';
  10.     Conn.UserName := ''; //trusted authentication/SSPI
  11.     Conn.Password := ''; //trusted authentication/SSPI
  12.     Conn.DatabaseName := 'master';
  13.     Conn.Params.Add('AutoCommit=true');
  14.     Conn.Transaction := Tran;
  15.     Conn.Open;
  16.     // adjust path  
  17.     Conn.ExecuteDirect('backup database test to disk = N''C:\TEMP\TEST2.bak'' with format, init');
  18.     Conn.Close;
  19.   finally
  20.     Tran.Free;
  21.     Conn.Free;
  22.   end;
  23. end;

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: MS SQL Server drop database error
« Reply #17 on: September 27, 2022, 04:47:42 pm »
Ah, yes, through a fresh connection. I didn't try it like that, but it works indeed :D

Code: Pascal  [Select][+][-]
  1. function ExecuteImmediately(User, Pass, Comm: string): string;
  2. var
  3.   Conn: TMSSQLConnection;
  4.   Tran: TSQLTransaction;
  5. begin
  6.   Result := '';
  7.   Conn := TMSSQLConnection.create(nil);
  8.   Tran := TSQLTransaction.create(nil);
  9.   try
  10.     //adjust credentials
  11.     Conn.HostName := '127.0.0.1';
  12.     Conn.UserName := User;
  13.     Conn.Password := Pass;
  14.     Conn.DatabaseName := 'master';
  15.     Conn.Params.Add('AutoCommit=true');
  16.     Conn.Transaction := Tran;
  17.     Conn.Open;
  18.     // adjust path
  19.     try
  20.       Conn.ExecuteDirect(Comm);
  21.     except
  22.       on e:Exception do Result := e.Message;
  23.     end;
  24.     Conn.Close;
  25.   finally
  26.     Tran.Free;
  27.     Conn.Free;
  28.   end;
  29. end;

I did something like that, but I executed all of the commands through the fresh connection, so only the first command succeeded.

 

TinyPortal © 2005-2018