Automation

kvb · 2004-06-01

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 · 2004-06-01

xyievo

RobertMo · 2004-06-01

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 · 2004-06-02

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 · 2004-06-02

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

kvb · 2004-06-03

Unhelpfull..

ATACH · 2004-06-03

kvb

ti ne ponial? :shock:

HYIEVO!

fb · 2004-06-03

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 · 2004-06-04

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 · 2004-06-04

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

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

ATACH · 2004-06-07

KVB

do u want KASHA?

srini_shetty · 2004-06-09

:idea: .... pinga

kvb · 2004-06-09

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 · 2004-06-09

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 · 2004-06-10

Yes! It works

Thanx, fb.

I need to read the documentation more closely :roll:

fb · 2004-06-10

8)

Automation

Comments

Categories