Automation

kvb

Hi, guys!

I have a stored procedure in MS SQL.
I execute it from Navision by using ADO component.
It takes a lot of time to execute it so i need something like Progress Bar.

Any ideas how i can image progress while SQL procedure is running?

Thanx in advance. :?:

ATACH

xyievo

RobertMo

if you can measure progress in SQL procedure (like 3 of 7 steps completed) you can make a new table in navision called progress with a PK filed and with text field "SQL status".

show the "SQL status" on form.

onopen form start your SQL procedure. in this SQL procedure regulary update your progress table (like 1 of 7 steps completed, 2 of 7 ...)

on form's OnTimer event just read the progress table...

of course instead of making "text progress" you can make something more measurable (numbers) so you can show real progrrees bar...

kvb

Thanx, RobertMo.

Unfourtunately, since my SQL stored procedure begins to execute trigger OnTimer will not run till procedure finished...

Sorry for my English :roll:

RobertMo

maybe you can put your call to SQL procedure in OnTimer event itself (and make sure the sql procedure is called only once...)

kvb

Unhelpfull..

ATACH

kvb


ti ne ponial? :shock:


HYIEVO!

fb

How about something like the following:

CREATE(conn);
conn.Open('Driver={SQL Server},Server=YourServer,Database=YourDB');
conn.BeginTrans();

// options = adAsyncExecute + adCmdStoredProc...
conn.Execute('sp_LongRunningProc', RecordsAffected, 20);

StillExecuting := TRUE;
ElapsedTime := 0;
TooMuchTime := 300; 

WHILE StillExecuting DO BEGIN
  SLEEP(1000);
  ElapsedTime += 1;
  IF (conn.State() > 3) THEN BEGIN
    IF ElapsedTime < TooMuchTime THEN BEGIN

      ; // do something here to show the user a little progress...

    END ELSE BEGIN
      conn.Cancel();
      conn.RollbackTrans();
      StillExecuting := FALSE;
      MESSAGE('Too much time elapsed.  Transaction canceled.');
    END;
  END ELSE BEGIN
    conn.CommitTrans();
    StillExecuting := FALSE;
    MESSAGE('Transaction completed.');
  END;
END;
conn.Close();
CLEAR(conn);

kvb

thanks all, it seems to me i got my problem! :P

My problem is thai i need _ASync_ execution of stored procedure.

fb, in your code there is a string:

// options = adAsyncExecute + adCmdStoredProc...

Can u(or anyone else) say how i can set this params on ADOConnection object in Navision?

fb

That is what the magic number 20 does in the line:

conn.Execute('sp_LongRunningProc', RecordsAffected, 20);

ATACH

KVB


do u want KASHA?

srini_shetty

:idea: .... pinga

kvb

There is a little problem again..

My code was:

aut_ADOConn.Execute('Consolidation_RequirementForPayment' + '''' + pt_ConsCompany + '''' + ', ' + '''' + pt_BuhCompany + '''' + ', ' + FORMAT(pb_ToCons, 0, 2));

It worked perfect.
But as far as i need async execution of procedure i change my code
(according to fb advice) to:

aut_ADOConn.Execute('Consolidation_RequirementForPayment' + ''''  + pt_ConsCompany + '''' + ', ' + '''' + pt_BuhCompany + '''' + ', ' + FORMAT(pb_ToCons, 0, 2)
, 20);// additional parameter to make async execution

When i try to compile there is an error in that line. The error message is:

"A variable was expected. For examle:

MyVar
Customer.Name"

What`s the problem?
I`m using 'Microsoft ActiveX Data Objects 2.8 Library'.

fb

The ADO Execute method that allows you to specify async execution requires 3 parameters.

The method signature (in VB form, see http://msdn.microsoft.com/library/en-us ... xecute.asp ) is as follows:

connection.Execute CommandText, RecordsAffected, Options

Your problem is that you are trying to add the 'Options' argument without adding the 'RecordsAffected' argument. To correct this, first create a local variable (let's call it 'intRecsAffected') of type Integer, and then extend your original code as follows:

...FORMAT(pb_ToCons, 0, 2)
, intRecsAffected, 20);  // two additional params for async execute

kvb

Yes! It works

Thanx, fb.

I need to read the documentation more closely :roll:

fb

8)