jontebgood
asked on
Ado vs MSSQL 7/2k and backup/restore (500+)
Hi out there.
Im trying to do a application witch does a backup/restore procedure. The app is multithreaded and when I restore and take a backup by ADO I would like to have a Progressbar in a Window. If i try to send the command to MS SQL by adoquery it responds that there is no answer at the adocommand. and that i should use a sqlexec command instead. anyway if i use the queryanalyzer and run the restore/backup command i get a respond telling the progress 10% complete, 20% complete osv. and I want to have this information connected to the previus mantioned progress bad. plz help me.
and excute me for my bad english.
Best regards
JGB
Im trying to do a application witch does a backup/restore procedure. The app is multithreaded and when I restore and take a backup by ADO I would like to have a Progressbar in a Window. If i try to send the command to MS SQL by adoquery it responds that there is no answer at the adocommand. and that i should use a sqlexec command instead. anyway if i use the queryanalyzer and run the restore/backup command i get a respond telling the progress 10% complete, 20% complete osv. and I want to have this information connected to the previus mantioned progress bad. plz help me.
and excute me for my bad english.
Best regards
JGB
ASKER
Hi again,
U missunderstood me, I know HOW TO do the backup up and so on. I want to catch the answer from the sql server WHEN i have sended the ado command and the job has started. if u do it with a asual ado.sql - open I get the answer that the query doesnt has any answer. if i do it with a ado.sqlexec I dont get any progress answers BUT the backup/restore starts and complete. I hope U understands me bette now.
BR
JBG
U missunderstood me, I know HOW TO do the backup up and so on. I want to catch the answer from the sql server WHEN i have sended the ado command and the job has started. if u do it with a asual ado.sql - open I get the answer that the query doesnt has any answer. if i do it with a ado.sqlexec I dont get any progress answers BUT the backup/restore starts and complete. I hope U understands me bette now.
BR
JBG
Yes, it is clear now.
You can execute the ExecSQL in Thread. set the FreeOnTerminate of the thread to true. In OnTerminate event of the thread send some user message to you form where you can handle it.
For example :
const
WM_OPERATION_DONE = WM_USER + 1;
TMSBackupThread = class(TThread)
private
SQLStatement : String;
ParentHandle : THandle;
procedure ExecuteQueryOnDB (.............)
procedure Execute;
public
constructor Create(OwnerFormHandle : THandle; SQLText : String; CreateSuspended : boolean = True);
end;
in Create you send Form1.Handle, the SQL statement. In your execute method you can take care about executing the backup / restore.
procedure TMSBackupThread.Execute;
begin
// ...
// YOUR CODE HERE
// ...
SendMessage(ParentHandle, WM_OPERATION_DONE, 0, 0);
end;
{-------------------------
in your form you should handle the message sent by thread :
TForm1 = class(TForm)
private
{ Private declarations }
procedure OnThreadFinished (var Msg : TMessage); message WM_OPERATION_DONE;
public
{ Public declarations }
end;
ASKER
im sorry.... i have tgo refrease me becuz u dont understand me..... i will be back soon...
Hi there,
The progress is not returned as a dataset but as errormessages (with native error set to zero). The messages are issued by "print" statements. This is what OnLine books says:
"The PRINT statement takes either one character or a Unicode string expression as a parameter. It returns the string as a message to the application. The message is returned as an informational error in ADO, OLE DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement. The string is returned to the message handler call-back function in DB-Library applications."
On the ADOConnection there is an event OnInfoMessage in which the "error" is passed.
procedure TForm1.ADOConnection1InfoM essage(Con nection: TADOConnection; const Error: Error; var EventStatus: TEventStatus);
begin
Memo1.Lines.Add(Error.Desc ription);
end;
I tested this with my version of ADODb but the event is not called... bad luck...
I will try to find if it is possible to hook the event up.
Reagrds Jacco
The progress is not returned as a dataset but as errormessages (with native error set to zero). The messages are issued by "print" statements. This is what OnLine books says:
"The PRINT statement takes either one character or a Unicode string expression as a parameter. It returns the string as a message to the application. The message is returned as an informational error in ADO, OLE DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement. The string is returned to the message handler call-back function in DB-Library applications."
On the ADOConnection there is an event OnInfoMessage in which the "error" is passed.
procedure TForm1.ADOConnection1InfoM
begin
Memo1.Lines.Add(Error.Desc
end;
I tested this with my version of ADODb but the event is not called... bad luck...
I will try to find if it is possible to hook the event up.
Reagrds Jacco
If you use a TADOCommand object it will give 1 message (but still not all and only when the command is finished...)
Got it :)
Use a TADOQuery set.
Set CursorLocation of both the TADOConnection and TADOQuery to clUseServer.
The SQL in the TADOQuery should look like this: "backup database MYDB to MYBACKUP with STATS = 1" (STATS = 1 makes sure the progress is fed back)
Now normally you would call ExecSQL but then no InfoMessages are returned. So use Open in stead then InfoMessage will be returned. (Although not all messages when the backup is very fast).
This will end in an exception which you can ignore. (By the way is you set EventStatus to esCancel then the operation is aborted!!)
Here is the dfm and pas file I used to test:
<<START OF DFM>>
object Form1: TForm1
Left = 192
Top = 194
Width = 870
Height = 640
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Button1: TButton
Left = 88
Top = 144
Width = 75
Height = 25
Caption = 'Button1'
TabOrder = 0
OnClick = Button1Click
end
object Memo1: TMemo
Left = 232
Top = 24
Width = 425
Height = 281
Lines.Strings = (
'Memo1')
TabOrder = 1
end
object ADOConnection1: TADOConnection
Connected = True
ConnectionString =
'Provider=SQLOLEDB.1;Passw ord=passwo rd;Persist Security Info=True' +
';User ID=username;Initial Catalog=TestCentral;Data Source=dsbrnd1.dsbg' +
'roep.dsb'
CursorLocation = clUseServer
LoginPrompt = False
Provider = 'SQLOLEDB.1'
OnInfoMessage = ADOConnection1InfoMessage
Left = 80
Top = 56
end
object ADOQuery1: TADOQuery
Connection = ADOConnection1
CursorLocation = clUseServer
Parameters = <>
SQL.Strings = (
'backup database OLAP_Dimensions to TeamGeest_Bck with STATS = 1'
'')
Left = 168
Top = 56
end
end
<<END OF DFM>>
<<START OF PAS>>
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids;
type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
Button1: TButton;
Memo1: TMemo;
ADOQuery1: TADOQuery;
procedure ADOConnection1InfoMessage( Connection : TADOConnection;
const Error: Error; var EventStatus: TEventStatus);
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.ADOConnection1InfoM essage(Con nection: TADOConnection; const Error: Error; var EventStatus: TEventStatus);
begin
Memo1.Lines.Add(Error.Desc ription);
end;
procedure TForm1.Button1Click(Sender : TObject);
begin
ADOQuery1.Open;
end;
end.
<<END OF PAS>>
Use a TADOQuery set.
Set CursorLocation of both the TADOConnection and TADOQuery to clUseServer.
The SQL in the TADOQuery should look like this: "backup database MYDB to MYBACKUP with STATS = 1" (STATS = 1 makes sure the progress is fed back)
Now normally you would call ExecSQL but then no InfoMessages are returned. So use Open in stead then InfoMessage will be returned. (Although not all messages when the backup is very fast).
This will end in an exception which you can ignore. (By the way is you set EventStatus to esCancel then the operation is aborted!!)
Here is the dfm and pas file I used to test:
<<START OF DFM>>
object Form1: TForm1
Left = 192
Top = 194
Width = 870
Height = 640
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Button1: TButton
Left = 88
Top = 144
Width = 75
Height = 25
Caption = 'Button1'
TabOrder = 0
OnClick = Button1Click
end
object Memo1: TMemo
Left = 232
Top = 24
Width = 425
Height = 281
Lines.Strings = (
'Memo1')
TabOrder = 1
end
object ADOConnection1: TADOConnection
Connected = True
ConnectionString =
'Provider=SQLOLEDB.1;Passw
';User ID=username;Initial Catalog=TestCentral;Data Source=dsbrnd1.dsbg' +
'roep.dsb'
CursorLocation = clUseServer
LoginPrompt = False
Provider = 'SQLOLEDB.1'
OnInfoMessage = ADOConnection1InfoMessage
Left = 80
Top = 56
end
object ADOQuery1: TADOQuery
Connection = ADOConnection1
CursorLocation = clUseServer
Parameters = <>
SQL.Strings = (
'backup database OLAP_Dimensions to TeamGeest_Bck with STATS = 1'
'')
Left = 168
Top = 56
end
end
<<END OF DFM>>
<<START OF PAS>>
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids;
type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
Button1: TButton;
Memo1: TMemo;
ADOQuery1: TADOQuery;
procedure ADOConnection1InfoMessage(
const Error: Error; var EventStatus: TEventStatus);
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.ADOConnection1InfoM
begin
Memo1.Lines.Add(Error.Desc
end;
procedure TForm1.Button1Click(Sender
begin
ADOQuery1.Open;
end;
end.
<<END OF PAS>>
ASKER
havent test yet but it seem to be right solution Thanks dude!!!
BR
JBG
BR
JBG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks man :)
http://www.bstsoftware.com/tsug/Sep99/Recovering2.htm
https://www.experts-exchange.com/questions/20915340/backup-and-restore-database.html