Access MS SQL Database from MS Dynamics NAV Native Database

One of my clients wants to synchronize data with different database. 

01 ) I  Created a new codeunit and created below new global/Global variables

RsReceiveData     -- Automation  --  'Microsoft ActiveX Data Objects 2.7 Library'.Recordset  

RsFReceiveData   --Automation  --  'Microsoft ActiveX Data Objects 2.7 Library'.Fields_Deprecated 

FieldReceiveData  -- Automation--   'Microsoft ActiveX Data Objects 2.7 Library'.Fields           

ADO                    --  Automation--  'Microsoft ActiveX Data Objects 2.7 Library'.Connection

01)   Connection Open.

You should Create 4 fields in a setup table. I created in Synchronize Setup(Customize new table)

SynchSetup.GET;

SynchSetup.TESTFIELD("SQL Server Name");

SynchSetup.TESTFIELD("Database Name");

SynchSetup.TESTFIELD("User ID");

CREATE(ADO);

ADO.Open('Provider=SQLOLEDB.1;initial catalog=' + SynchSetup."Database Name" + ';' +

'data source=' + SynchSetup."SQL Server Name" + ';' +

'user id=' + SynchSetup."User ID" + ';' +

'password=' + SynchSetup.Password);

ADO.CommandTimeout(600);

02)    After this you should write the sql update sql command as below.

//Open RecordSet

CREATE(RsReceiveData);

RsReceiveData.Open  ('Select isnull(t11_010,'''') as "Vendor Code",isnull(t11_020,'''') as "Store Code",isnull(t11_030,'''') as "Invoice date",' +  'isnull(t11_050,'''') as "Invoice Number", isnull(t11_090,'''') as "Item Group", isnull(t11_100,'''') as "Item Code",' +  'convert(char(20),isnull(t11_170,0)) as "Real Receive Amount", isnull(t11_220,'''') as "Order Number",' +   'isnull(t11_040,'''') as "Receive Div",isnull(t11_900,'''') as "Update Div",isnull(t11_990,'''') as "Disposal Date", ' +   'isnull((Select top 1 isnull(m1_600,'''') from mgsm01 where m1_020=t11_100 and t11_090=''6''),'''') '+   'as "Item Sub Group" from mgst11 ' +  'where t11_900=''1'' and t11_990 = '+ConvertToSQLDate(RecSynchLog."Disposal Date")+' '+

  'and t11_020 = '+ RecSynchLog."Store Code" +' '+  'ORDER BY "Disposal Date" DESC',ADO);

// Get the Data to the Variables define in Globals.

RsFReceiveData :=RsReceiveData.Fields();

RecordCount := 0;

WHILE (NOT RsReceiveData.EOF) AND (NOT RsReceiveData.BOF) DO BEGIN

  VendorCode := RsFReceiveData.Item('Vendor Code').Value;

  StoreCode := RsFReceiveData.Item('Store Code').Value;

  DateVar := FORMAT(RsFReceiveData.Item('Invoice date').Value);

  InvoiceDate := ConvertToDate(DateVar);

  InvoiceNo := RsFReceiveData.Item('Invoice Number').Value;

  ItemGroup := RsFReceiveData.Item('Item Group').Value;

  ItemCode := RsFReceiveData.Item('Item Code').Value;

  ItemSubGroup := FORMAT(RsFReceiveData.Item('Item Sub Group').Value);

  EVALUATE(RealReceiveAmount,FORMAT(RsFReceiveData.Item('Real Receive Amount').Value));

  OrderNo := RsFReceiveData.Item('Order Number').Value;

  ReceiveDiv := RsFReceiveData.Item('Receive Div').Value;

  UpdateDiv := RsFReceiveData.Item('Update Div').Value;

  DateVar := FORMAT(RsFReceiveData.Item('Disposal Date').Value);

  DisposalDate := ConvertToDate(DateVar);

  IF NOT(SetGlobalDisposalDate) THEN BEGIN

    GlobalDisposalDate := DisposalDate;

    SetGlobalDisposalDate := TRUE;

  END;

  RecordCount := RecordCount + 1;

  InsertReceiveData;

  RsReceiveData.MoveNext;

END;

//Close RecordSet

RsReceiveData.Close;

//Close Connection

CloseConnection;