ADO Sql Timeout

gfarrell
gfarrell Member Posts: 8
edited 2010-10-25 in NAV/Navision Classic Client
I created a codeunit to execute a stored procedure via ado as illustrated by several threads here and it works great. Thanks to all those who contributed.

I want to call the code from a processing only report, but I keep getting sql timeouts after several minutes. When the codeunit is run directly it runs in about one second.

I have also tried to copy the code from the codeunit into a function on the report, but I am having the same timeout issue with that approach.

We are stumped!

Comments

  • kriki
    kriki Member, Moderator Posts: 9,124
    Did you set this: 
    ADOConnection.CommandTimeout(0);

    before calling the sp?
    If you don't do it, the timeout of a command is a standard value (I think it is the value you find in the properties of the SQL-server=>Connections=>"Remote query timeout" but i am not sure. I never tested it).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • gfarrell
    gfarrell Member Posts: 8
    Yes we have the timeout property set. As I said before the codeunit executes just fine, but when it's called from the report sql times out.

    The code we're using is below:
    //CREATE CONNECTION AND OPEN
IF ISCLEAR(lADOConnection) THEN
CREATE(lADOConnection);
lADOConnection.ConnectionString:= 'Driver={SQL Server}; Server=EFORCE07\MSPOSINSTANCE;'+
  'Database=fromVBSO; UID=sa; PWD=xxxx';
lADOConnection.Open;

IF ISCLEAR(lADOCommand) THEN
CREATE(lADOCommand);

lvarActiveConnection := lADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;

//Execute Stored Procedure
lADOCommand.CommandText := 'NAVPopulateTable' ;

ADOParameter := lADOCommand.CreateParameter('@begindate', 200, 1, 10,BeginDate);
lADOCommand.Parameters.Append(ADOParameter);

ADOParameter := lADOCommand.CreateParameter('@enddate', 200, 1, 10,EndDate);
lADOCommand.Parameters.Append(ADOParameter);

lADOCommand.CommandType := 4; //Stored Procedure
lADOCommand.CommandTimeout := 0;
lADOCommand.Execute;

//CLOSE CONNECTION
lADOConnection.Close;
CLEAR(lADOConnection);
  • rmv_RU
    rmv_RU Member Posts: 119
    gfarrell wrote:
    Yes we have the timeout property set. As I said before the codeunit executes just fine, but when it's called from the report sql times out.
    Did you set EndDate and BeginDate values?
    Are you sure that those values (both report and codeunit) are equal?
    Looking for part-time work.
    Nav, T-SQL.
  • gfarrell
    gfarrell Member Posts: 8
    Problem solved.

    As long as I put a COMMIT just before calling the codeunit, the reports runs the codeunit without any problem.

    Thanks everybody for your help.

Categories