ADO Sql Timeout

gfarrell

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!

kriki

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).

gfarrell

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

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?

gfarrell

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.