I want to push data into a Navision SQL Server Database. The customer has a shipping software that uses an access database for storing information about shipping, like parcel numbers and such.
The customer wants to push the data from the access db into navision now. This should be scheduled to run once a day. So at the end of the day, parcel numbers and such should be posted to Navision.
What is the best way to import this data? Maybe create a table in the Navision DB that I can push the data to, and then use NAS to "add" this data into the real tables and such?
0
Comments
There is a lot of questions, but it always comes to this. Creating a new table to put the external data in and then use NAS to put it in the real tables or post it. This because from an external tool, you cannot use the Navision business-logic.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
2) I use the Navision timer to check from time to time. Check this topic:http://www.mibuso.com/forum/viewtopic.php?t=10359
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
Do you empty the table after running nas to "import" the data? So every time the "timer" runs if no data is in the table it does nothing, just "sleeps" until the next try?
Exactly. Processor time used=almost 0. Just 1 read (using the correct index and filters) and that is it for another N seconds or minutes. I am using this system on my portable and when I look into performance-stats, I don't see any of it.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
http://www.gointegration.com/UDPNE.htm
Ever tested this? Seams like this is what I want to do.
Well going to test to time NAS to do this first.
A extra advise:
Create the table in Navision and use only the fieldproperties that are directly mappable to SQL, like text = VARCHAR
Avoid the type 'code' because SQL can write with lower case and Navision expect only upper case literals. You could end up a situation that it's impossible to take a backup of the database if there is lower case literals in the 'code'-fields
Avoid the 'code' type in tables that SQL is allowed to write directly to.
Dan
MCSD
Dan Lindström
NCSD Navision 2.00 since 1999 (Navision Certified Solution Developer)
MBSP Developer for Microsoft Dynamics NAV 2009
RIS Plus, LLC
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
RIS Plus, LLC
Code data type turns into varchar on the SQL Server side. Integer turns into int, decimal turns into decimal. Text however, turns into varchar as well. A text data type in SQL Server is not compatible with text type in NAV.
You can prevent any and all type mismatches by creating the table in NAV.
RIS Plus, LLC
Man you don't learn do you....
One rule of thumb: NEVER put data directly into NAV tables from anywhere but NAV, because you will miss business logic. I don't care if you know what you're doing, but you just plain and simple will not be able to duplicate NAV business logic.
You can use integration services or DTS to transfer data into a SQL Server table in the NAV database, but you MUST use a NAV routine to process that data into NAV application tables.
RIS Plus, LLC
Just two things.
1/ Please never do this.
2/ Please never recommend to someone else that they do it.
but u also say this
which one quote 1 or 2
i think quote 1 is way cool =D>
GD
RIS Plus, LLC
RIS Plus, LLC
Let's look at it this way - the data in the buffer table can be bollocks but so can be a text file. Excel sometimes makes miracles, turning a date into 43,328 and when you format the cell back you don't have the same date back Integration always needs to be tested, probably having some human overview/approval in the first months, and being able to reverse everything.
But anyway, if you are afraid then have a buffer table outside NAV and read it via ADO, that's why I've put this example on: http://mibuso.com/dlinfo.asp?FileID=589
1. create a SSIS /DTS to transfer the data from acess to SQL(Do keep in mind about the table structure)
2. RUN the NAS to create another stack of records which are eventually not meant for posting but just a dummy of the same.
3. Schedule the postings through NAS of the stacked records, so the user also gets the chance viewing the data.
For any queries you can also visit my blog site: http://msnavarena.blogspot.com/
Of course there is nothing wrong with importing to a buffer table, then using NAS to then move from the buffer table to a journal and then post to the NAV tables, that is exactly the best way to do it. I have done this many times, especially when doing initial go live data conversion. The difference between Dataports and DTS is converting hours of Dataports to seconds of DTS.
In fact this is so fast, I once did the dataconversion into a SQL NAV, then restored the backup into Navision. Though by the time we went live, they decided to go direct to SQL instead.