The message is for C/AL programmers:

Freaky

HI Guys, I got this error when trying to manually Run a codeunit that synchronises records from a table in Nav to a Table in SQL. This codeunit is one of the Codeunits in the SQL Synchro tool I got from Mibuso downloads it is called the "SQL Synchro Manually Run". This is the Error below

The message is for C/AL programmers:

The call to member Execute failed. Microsoft OLE DB Provider for ODBC Drivers Returned the following message:

[Microsft][ODBC SQL Server Driver][SQL Server]The COMMIT
TRANSACTION request has no corresponding BEGIN TRANSACTION.

This is the Code below

Documentation()--->Trigger

OnRun()--->Trigger
tSQLCommit := 'COMMIT;';
iTeller := 0;
bolDelete := FALSE;
IF NOT ISCLEAR(adoCon) THEN
  IF adoCon.State <> 0 THEN
    adoCon.Close;
CLEAR(adoCon);
CREATE(adoCon);
recSynchroParams.RESET;
IF recSynchroParams.GET() THEN
  bolDelete := recSynchroParams."Delete Lines";
adoCon.Open(cunSynchroFunctions.GetConnString);
recSynchroBuffer.RESET;
recSynchroBuffer.SETCURRENTKEY(Executed);
recSynchroBuffer.SETFILTER(Executed,'%1',FALSE);
recSynchroBuffer.LOCKTABLE;
IF recSynchroBuffer.FIND('-') THEN BEGIN
  REPEAT

    IF (STRPOS(UPPERCASE(recSynchroBuffer.Part1),'DELETE') <> 0) OR (STRPOS(UPPERCASE(recSynchroBuffer.Part1),'INSERT') <> 0) THEN
      COMMIT;

    tSQL := recSynchroBuffer.Part1 + recSynchroBuffer.Part2 + recSynchroBuffer.Part3 + recSynchroBuffer.Part4;
    tSQL += recSynchroBuffer.Part5;
    tSQL2 := recSynchroBuffer.Part6 + recSynchroBuffer.Part7 + recSynchroBuffer.Part8 + recSynchroBuffer.Part9;
    tSQL2 += recSynchroBuffer.Part10;
    adoCon.Execute(tSQL+tSQL2);
    adoCon.Execute(tSQLCommit);
    IF bolDelete THEN BEGIN
      recSynchroBuffer.DELETE;
    END ELSE BEGIN
      recSynchroBuffer.Executed := TRUE;
      recSynchroBuffer.MODIFY;
    END;
    iTeller += 1;
    IF iTeller MOD 100 = 0 THEN BEGIN
      COMMIT;
      SLEEP(200);
    END ELSE
      IF (STRPOS(UPPERCASE(recSynchroBuffer.Part1),'DELETE') <> 0) OR (STRPOS(UPPERCASE(recSynchroBuffer.Part1),'INSERT') <> 0) 
THEN
        COMMIT;
    recSynchroBuffer.LOCKTABLE;
  UNTIL NOT recSynchroBuffer.FIND('-');
  COMMIT;
END;
COMMIT;
adoCon.Close;
CLEAR(adoCon);

ErrorHandler()--->Trigger
adoCon.Close;
CLEAR(adoCon);
recSynchroBuffer.Executed := TRUE;
recSynchroBuffer.Error := TRUE;
recSynchroBuffer.MODIFY;

Can anyone help me out please?.

Thanks in advance.

kolaboy

Hi Freaky,
what version of Nav are you using?

Freaky

Am Using Nav 4.0

Thanks.

kolaboy

"manually Run a codeunit that synchronises records from a table in Nav to a Table in SQL"

Is it on the same database OR different DB and are they all Nav?

matttrax

It looks like you are using COMMIT rather freely. Do you actually need all of those? You should only use a COMMIT statement when absolutely necessary.

Freaky

Thanks Guys solved it.Just commented

//adoCon.Execute(tSQLCommit).

kolaboy

Good Job! =D>