Reply To: How to connect External SQL with Dynamics NAV?

Network of MS Dynamics D365, AX, NAV, GP, SL, CRM, RMS, POS professionals Forums Reply To: How to connect External SQL with Dynamics NAV?

#12227

Robert
Participant

< ![CDATA[Hi Abigail, There are some steps, you need to follow it carefully. STEPS
1. IDENTIFICATION-
a) External database can be in the same/different database server.
b) Suppose we have ‘TestData’ as SQL database on database server ‘ABC\XYZ001’ (not NAV database).
textdata
c)With two tables ‘dbo.Customers’ and ‘dbo.Products_Staging’
d) Now, what do you think is required to connect this external database with NAV?

  • Maybe the same table structure? (yes)
    Connectivity between NAV and external database? (yes)
    LAN wire? (off course silly!!)

2. TABLE MAPPING-
a) In NAV, we can either use the existing table or create a new table (suggested)
b) Now open new table which you’ve created in NAV
c) Go to Table properties and find something called ‘TableType’ (value of this property varies, depending on NAV’s version)
d) Change ‘TableType’ to ‘ExternalSQL’ and see new properties getting visible below
e) Identify the table name of SQL which you’ll connect with and put its name in ‘ExternalName’.
tabletype
f) Make sure that table name in SQL and ‘ExternalName’ should be same.
testdata
g) ‘ExternalSchema’ (in my case) is said here as ‘dbo’
h) TABLE MAPPING DONE
3. FIELD MAPPING-
a) SQL fields are shown below with its datatype:
b) product id
c) Make fields which you require from external SQL in NAV.
d) product id
e) Mapping between two table’s field are done on Field properties of each field.
f) product id
g) Some notes:

  • ‘Name’ -> can be different from ‘ExternalName’
    ‘Data Type’ -> for this you need to understand basic datatypes in NAV as compared to SQL data types.

h) Complete field mapping process for all required fields in NAV
i) Is field mapping done? (Nope)
j) You need to export NAV table in .txt format / .xml format (Why? Ans: Because of hidden property called ‘ExternalType’. You’ll not find this even after NAV Table object is
exported in .txt / .xml. You need to manually add this property in the exported object file.)
k) properties
l) This has been made easier for CRM Integrated tables. You can generate NAV Table’s .txt file from PowerShell command (Anxious? Let’s see it later)
m) After manual addition of ‘ExternalType’ in .txt / .xml file, save it and import it in NAV and compile.
n) Voila! FIELD MAPPING is done.
4. CONNECTION
a) The connection between NAV Table and SQL Table is not direct and should be registered. NAV Service needs to know this registration before the connection is made.
b) We’ll create a Codeunit in NAV which does this work. (same is readily available for CRM Integration. You may change the codeunit according to your requirement. Never hard code
anything.
c) documentation
d) database
e) Question: Where are the credentials?

  • Ans: For Windows Credentials -> Database credentials can be waved off.
    Ans: For User Credentials -> You can add two more keywords and values on DatabaseConnectionString. (DatabaseConnectionString:= ‘Data Source=\;Initial Catalog=TestData;UserName=;Password=’)
    Make sure you encrypt / decrypt password properly in NAV.

f) Save, Compile and execute the Codeunit.
g) If unsuccessful, you can Google the error message.
h) You may face issue due to wrong Credentials:

  • Wrong datasource
    ExternalType mismatch or incorrect in step 3. g.
    ExternalName mismatch or incorrect in step 3. d.
    If successful, you can Run the new table created in step 2. b.

j) Realtime data synchronization happens between NAV and external SQL.
]]>

Skip to toolbar