Problem with NAS in NAV 2013 R2. ADO and ADO.NET.

Sl1m4er

Hi guys,

Here is my issue experienced in NAV 2013 R2:
- There is a report that is using ADO automation and its components to export data from NAV into external SQL database.
- Running this report using NAS generates the following error message:

“Microsoft Dynamics NAV Server tried to send a callback to the client to create an automation object…Callback functions are not allowed…”.

Apparently, the issue is that NAS in NAV 2013 no longer supports creation of automation objects on the server, as already described in this post.

With this, I was thinking about redisigning the code to use ADO.NET through .NET Interop instead of ADO. (Also, there was an idea to do the same via web-services, so that instead of NAV exporting data, external application would connect to the web-service and do the 'export' into SQL database). I still wonder which approach is the best in this scenario, but the following questions are related to ADO.NET.

So, previously when using ADO automation, the code for exporting data was built as follows:
- Create connection to the external SQL database
- Create and open a recordSet linked to a specifiic table of the external SQL database
- In a loop and using recordSet's cursor, populate recordSet with values from NAV database
- Insert values from the recordSet into a linked table of the external SQL database by running 'recordSet.UpdateBatch' method.

Now, when using ADO.NET the whole idea is changed a little and instead of using recordSets, ADO.NET operates with DataSets/DataTables and DataAdapters, where DataAdapter is acting like a bridge between a DataSet and a data source, in my case external SQL database.

Can someone please explain the logic or provide an example of how can I populate a very simple table in the external database (e.g. vendors) by using DataAdapter and DataSet/DataTable classes.

Thanks in advance.

thegunzo

Here is an example http://www.dynamics.is/?p=1231

Sl1m4er

@Gunnar, thanks for the hint.

Sl1m4er

Hi guys,

Here is a working example for exporting vendors from NAV into a table in external database:

  {
    OnRun=BEGIN
            ServerConnect;
            ExportVendors;            
            ServerDisconnect;
          END;

  }
  CODE
  {
    VAR
      SQLConnection@1161051000 : DotNet "'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlConnection";
      SQLDataAdapter@1161051001 : DotNet "'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlDataAdapter";
      SQLCommandBuilder@1161051003 : DotNet "'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommandBuilder";
      SelectQuery@1161051004 : TextConst 'ENU=SELECT * FROM %1';
      DeleteQuery@1161051012 : TextConst 'ENU=DELETE FROM %1';
      IntegrationSetup@1161051013 : Record 6083563;
      ExecSPprocUpdateErpData@1161051006 : TextConst 'ENU=EXEC procUpdateErpData %1,%2';            

    PROCEDURE ServerConnect@1161051000();
    VAR
      lConnectionString@1161051000 : Text[1024];
    BEGIN
      IntegrationSetup.GET;

      //connect to SQL server
      GetConnectionString(lConnectionString);
      SQLConnection := SQLConnection.SqlConnection(lConnectionString);
      SQLConnection.Open;
    END;

    PROCEDURE GetConnectionString@1161051001(VAR ConnectionString@1161051000 : Text[1024]);
    BEGIN
      //database authentication
      IF IntegrationSetup.Authentication = IntegrationSetup.Authentication::"Database Server Authentication" THEN
        ConnectionString :=
          'Server='+IntegrationSetup."Server Name"+';' +
          'Database='+IntegrationSetup."Database Name"+';' +
          'Uid='+IntegrationSetup."User Name"+';' +
          'Pwd='+IntegrationSetup.Password+';'
      //windows authentication
      ELSE
        ConnectionString :=
          'Server='+IntegrationSetup."Server Name"+';' +
          'Database='+IntegrationSetup."Database Name"+';' +
          'Trusted_Connection=True;';
    END;

    PROCEDURE ExportVendors@1161051004();
    VAR
      lSQLiVendorsDataTable@1161051002 : DotNet "'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataTable";
      lSQLiVendorsDataRow@1161051001 : DotNet "'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataRow";
      lVendor@1161051000 : Record 23;
      lGeneralLedgerSetup@1161051003 : Record 98;
    BEGIN
      InitializeDataAdapter(STRSUBSTNO(SelectQuery,'iVendors'));
      ExecuteSQLQuery(STRSUBSTNO(DeleteQuery,'iVendors')); //clear intermediate table

      //Fill DataTable with table structure obtained by DataAdapter
      lSQLiVendorsDataTable := lSQLiVendorsDataTable.DataTable;
      SQLDataAdapter.Fill(lSQLiVendorsDataTable);

      //Fill DataRow with values to be exported
      lVendor.SETCURRENTKEY("No.");
      IF lVendor.FIND('-') THEN
      REPEAT
        lSQLiVendorsDataRow := lSQLiVendorsDataTable.NewRow;
        lSQLiVendorsDataRow.Item(0,IntegrationSetup."Company ID"); //externalTable.field1
        lSQLiVendorsDataRow.Item(1,lVendor."No."); //externalTable.field2
        lSQLiVendorsDataRow.Item(2,lVendor.Name); //externalTable.field3
        ...
        etc.
        ...
        lSQLiVendorsDataTable.Rows.Add(lSQLiVendorsDataRow);
      UNTIL lVendor.NEXT = 0;

      //Update iTable in integration db with the values from DataTable
      SQLDataAdapter.Update(lSQLiVendorsDataTable);

      //release SQLDataAdapter object
      SQLDataAdapter.Dispose;

      //run stored procedure to transfer values from iTables to real tables
      ExecuteSQLQuery(STRSUBSTNO(ExecSPprocUpdateErpData,IntegrationSetup."Company ID",'vendor'));
    END;

    
    PROCEDURE InitializeDataAdapter@1161051005(SelectQueryString@1161051000 : Text[1024]);
    BEGIN
      SQLDataAdapter := SQLDataAdapter.SqlDataAdapter(SelectQueryString,SQLConnection);

      //build default insert command for DataAdapter
      SQLCommandBuilder := SQLCommandBuilder.SqlCommandBuilder(SQLDataAdapter);
      SQLDataAdapter.InsertCommand := SQLCommandBuilder.GetInsertCommand;
    END;

    PROCEDURE ExecuteSQLQuery@1161051003(QueryString@1161051000 : Text[1024]);
    VAR
      lSQLCommand@1161051001 : DotNet "'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommand";
    BEGIN
      lSQLCommand := lSQLCommand.SqlCommand(QueryString,SQLConnection);
      lSQLCommand.ExecuteNonQuery;

      lSQLCommand.Dispose;
    END;

    PROCEDURE ServerDisconnect@1161051002();
    BEGIN
      SQLConnection.Close;
      SQLConnection.Dispose;
    END;   

I hope this helps someone.

The export is made into intermediary tables first, which are always cleared before the export. Then, data from intermediary tables is transferred to live tables using stored procedure. This is just in case you are wondering why am I doing it this way

Useful links:
http://msdn.microsoft.com/en-us/library/ms973217.aspx
http://dynamicsuser.net/blogs/waldo/arc ... ple-1.aspx