Board index » delphi » Time Limit on MS SQL 7 query execution using BDE

Time Limit on MS SQL 7 query execution using BDE

I am having a problem with MS SQL 7.  I have a stored procedure that can
have an execution time of more than 5 min.  It runs fine in Query Analyzer
but when I try to run it under Delphi it gives a "Lock Time Out" error after
five minutes.  I have tried the following code (Creating another TDatabase
connection and a query with that connection) to set the MAX QUERY TIME to a
value other than the default 300 seconds (5 Mins).   The Help says that
setting a param at this level will override the BDE Defaults.  But my stored
procedure still dies after 5 min.   I also tried setting the MAX QUERY TIME
in the BDE Administrator to 5000 and still a five minute limit.

         dbTmp := TDatabase.Create(Self);
         dbTmp.DatabaseName := 'dbTmp';
         dbTmp.AliasName := dbMain.AliasName;
         dbTmp.LoginPrompt := dbMain.LoginPrompt;
         dbTmp.Params.Assign(dbMain.Params);
         dbTmp.Params.Add('MAX QUERY TIME=5000');
         dbTmp.Connected := True;

         qry := TQuery.Create(Self);
         qry.DatabaseName := 'dbTmp';
         qry.SQL.Add("Stored Procedure");
          Try
            qry.ExecSql;
         except
            //
         end;

TIA
Kent

 

Re:Time Limit on MS SQL 7 query execution using BDE


I am getting the same problem.

"Lock time out. SQL Server connection timed out"

I also tried setting MAX QUERY TIME to a rediculously high value to
counteract it but to no evail.  I assume that there is some kind of lock
time setting, but I cannot find such a setting.

Have you solved this problem?
I am using the native SQL 7 driver BDE 5.10  and  BCB4 (D4 equivalent).
The stored proc I am running is retreiving the file list header from a tape
backup...

Sorry I can't help, I need an answer to this one too!

Thanks
Gary Searle
g...@datatask.com.au

Quote
Kent Meyer <ke...@argosoftware.com> wrote in message

news:84tgsm$gge1@bornews.borland.com...
Quote
> I am having a problem with MS SQL 7.  I have a stored procedure that can
> have an execution time of more than 5 min.  It runs fine in Query Analyzer
> but when I try to run it under Delphi it gives a "Lock Time Out" error
after
> five minutes.  I have tried the following code (Creating another TDatabase
> connection and a query with that connection) to set the MAX QUERY TIME to
a
> value other than the default 300 seconds (5 Mins).   The Help says that
> setting a param at this level will override the BDE Defaults.  But my
stored
> procedure still dies after 5 min.   I also tried setting the MAX QUERY
TIME
> in the BDE Administrator to 5000 and still a five minute limit.

>          dbTmp := TDatabase.Create(Self);
>          dbTmp.DatabaseName := 'dbTmp';
>          dbTmp.AliasName := dbMain.AliasName;
>          dbTmp.LoginPrompt := dbMain.LoginPrompt;
>          dbTmp.Params.Assign(dbMain.Params);
>          dbTmp.Params.Add('MAX QUERY TIME=5000');
>          dbTmp.Connected := True;

>          qry := TQuery.Create(Self);
>          qry.DatabaseName := 'dbTmp';
>          qry.SQL.Add("Stored Procedure");
>           Try
>             qry.ExecSql;
>          except
>             //
>          end;

> TIA
> Kent

Re:Time Limit on MS SQL 7 query execution using BDE


In article <84tgsm$g...@bornews.borland.com>, ke...@argosoftware.com (Kent

Quote
Meyer) wrote:
> I am having a problem with MS SQL 7.  I have a stored procedure that can
> have an execution time of more than 5 min.  It runs fine in Query
> Analyzer
> but when I try to run it under Delphi it gives a "Lock Time Out" error
> after
> five minutes.  I have tried the following code (Creating another

Sorry about the delay.

Setting MAX QUERY TIME alone is not enough. You also need to alter the
Driver default time outs as well.

Use the BDE Administrator program, and run:
 Configuration | Drivers | Native | MSSQL
and alter the values for MAX QUERY TIME and TIMEOUT.

This works for us!

Trevor Toms

Re:Time Limit on MS SQL 7 query execution using BDE


Thanks,  changing the TIMEOUT value works perfectly!

I had completely missed the fact that the 'Configuration | Drivers | Native
| MSSQL' settings had more settings available than the actual driver
definition under 'Databases' in the BDE Administrator.

Regards
Gary Searle
g...@datatask.com.au

Quote
Trevor Toms <s...@msn.com> wrote in message

news:memo.20000202222932.200D@tt.msn.com...
Quote
> In article <84tgsm$g...@bornews.borland.com>, ke...@argosoftware.com (Kent
> Meyer) wrote:

> > I am having a problem with MS SQL 7.  I have a stored procedure that can
> > have an execution time of more than 5 min.  It runs fine in Query
> > Analyzer
> > but when I try to run it under Delphi it gives a "Lock Time Out" error
> > after
> > five minutes.  I have tried the following code (Creating another

> Sorry about the delay.

> Setting MAX QUERY TIME alone is not enough. You also need to alter the
> Driver default time outs as well.

> Use the BDE Administrator program, and run:
>  Configuration | Drivers | Native | MSSQL
> and alter the values for MAX QUERY TIME and TIMEOUT.

> This works for us!

> Trevor Toms

Other Threads