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

Sl1m4erSl1m4er Member Posts: 42
edited 2014-10-22 in NAV Three Tier
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.

Answers

  • thegunzothegunzo Member Posts: 274
    Here is an example http://www.dynamics.is/?p=1231
    ________________________________
    Gunnar Gestsson
    Microsoft Certified IT Professional
    Dynamics NAV MVP
    http://www.dynamics.is
    http://Objects4NAV.com
  • Sl1m4erSl1m4er Member Posts: 42
    @Gunnar, thanks for the hint.
  • Sl1m4erSl1m4er Member Posts: 42
    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
Sign In or Register to comment.