Darren Guy
Guest
|
 Posted: Wed Apr 27, 2005 4:32 pm Post subject: Timeout Expired using Excel and QueryTable automation |
 |
|
I am using Delphi 7, and Excel 2003, with MS SQL 2000 on Win XP pro + sp2
I have some code that calls Excel's QueryTable to call several stored
procedures. However, I am getting a timeout expired error after 30 seconds.
There are several sp's that can run for several minutes.
This is my code, what can i do to get rid of these timeout errors.
procedure TPreRules.PopulateSpreadsheet(const recGetParamName: string);
const
sspi = 'ODBC;DSN=%s;Trusted_Connection=Yes;';
up = 'ODBC;DSN=%s;UID=%s;PWD=%s;';
var ExcelQuery: Variant;
sql, connection: Variant;
destination: variant;
ss: string;
begin
if ( fSSPI ) then
connection := Format(sspi, [fODBC])
else
connection := Format(up, [fODBC, fUserName, fPassword]);
sql := 'EXEC ' + recGetParamName;
Destination := fWorkSheet.Range['A2'];
ExcelQuery := fWorkSheet.QueryTables.Add(connection,destination, sql);
ExcelQuery.Name := 'Query from ' + fODBC;
ExcelQuery.FieldNames := true;
ExcelQuery.RowNumbers := false;
ExcelQuery.FillAdjacentFormulas := False;
ExcelQuery.PreserveFormatting := True;
ExcelQuery.RefreshOnFileOpen := False;
ExcelQuery.BackgroundQuery := false;
ExcelQuery.RefreshStyle := $00000001;
ExcelQuery.SavePassword := False;
ExcelQuery.SaveData := True;
ExcelQuery.AdjustColumnWidth := True;
ExcelQuery.RefreshPeriod := 0;
ExcelQuery.PreserveColumnInfo := True;
//ExcelQuery.RefreshBackgroundQuery :=False; // not recognized by
automation object
ExcelQuery.Refresh;
end;
I created a macro in excel to run the stored proc that I am having problems
with in my delphi app, and it will execute with no timeout in Excel. IT was
the excel macro code that i used in my Delphi Code.
thanks in advance
Darren
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
