Push data into a Navision SQL Server Database?

Smirre

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?

kriki

Smirre wrote:

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?

You just answerred the question yourself =D>
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.

Smirre

Ok, so how do I go about and initiate the NAS to do this "import" from the new table? I mean I want to schedule this or activate NAS to do this when my little vb.net app runs the data extraction from the access db to the Navision SQL server.

kriki

1) you can use messagequeues to send a message to NAS to do something. This takes some installing and setup. (I never did this)

2) I use the Navision timer to check from time to time. Check this topic:http://www.mibuso.com/forum/viewtopic.php?t=10359

Smirre

Wouldnt the timer function slow the system down? I mean its running the whole time consuming processor time?

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?

kriki

Smirre wrote:

Wouldnt the timer function slow the system down? I mean its running the whole time consuming processor time?

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?

Empty or put a Status=Imported.
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.

Smirre

Found a software that can do this:

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.

danlindstrom

I do agree with the advice to use a separate table for sharing data between Nav and SQL.

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

Smirre

Thanks for the advice. I will check into this. So I better turn all varchar fields to Uppercase when inserting them into the SQL server.

DenSter

If you're familiar with XML and message queing you could write something that doesn't require you to have any extra table. Instead you construct an xml document object out of the records you wish t send into Navision, and then send it into a message queue. This is really easy to do in VBA if you use the MSXML object model (I use version 3.0 and 4.0 in Navision). You can then set up a NAS that monitors the message queue, and this will pick up these messages as they come in, so you will have real time information.

ara3n

You can also use xml ports. They are realy handy. No programming is required to parse the xml document.

DenSter

VBA doesn't have XMLPorts... he's going to have to program the XML object on the Access side, and of course on the Navision side XMLPorts is the quickest way to get the data in.

lzr

Ok, so code is not recommended to use when inserting data. How about integer and decimal?

DenSter

Create the table in NAV, using the NAV designer. That is by far the easiest way to make sure you can program NAV to manipulate data in that table.

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.

DenSter

no no no....

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.

lzr

Thanks Daniel

David_Singleton

BlackTiger wrote:

You don't need NAS to schedule import.

DTS (for MSSQL2000) (or "Integration Services Project" for MSSQL2005) is enough to transfer data between your app and SQL-based NAV.

Just two things.

1/ Please never do this.
2/ Please never recommend to someone else that they do it.

gulamdastagir

DenSter wrote:

If you're familiar with XML and message queing you could write something that doesn't require you to have any extra table. Instead you construct an xml document object out of the records you wish t send into Navision, and then send it into a message queue. This is really easy to do in VBA if you use the MSXML object model (I use version 3.0 and 4.0 in Navision). You can then set up a NAS that monitors the message queue, and this will pick up these messages as they come in, so you will have real time information.

but u also say this

Create the table in NAV, using the NAV designer. That is by far the easiest way to make sure you can program NAV to manipulate data in that table.

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

which one quote 1 or 2

i think quote 1 is way cool =D>

DenSter

Just mentioning the possibilities, just saying that it is one of the options available. If you process information straight into core NAV tables, you will need to develop around exceptions and error logging. My personal choice will always be to have staging tables in NAV and process records from there. That way you have a record of all transactions and you can easily develop functionality around status management.

DenSter

So in that case you would have both. Staging tables in NAV and a message queue monitor to import incoming message into those staging tables. Then a separate process (that could be called from the MSMQ monitor of course) to process data into core application tables. Successful transactions get status 'OK', unsuccessful transactions get status 'not OK', and you can decide to add error logging to that or not.

Miklos_Hollender

I agree to Blackie. Writing into custom buffer tables and having a Navision codeunit to read the data and post them is perfectly OK. I takes a while to figure out some bits and things on the SQL side like the ridiculous decimal formats but otherwise it's OK.

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

diptish.naskar

Well agree to somewhat with denster but for SQL server SSIS is one of the most powerful things to push data in SQL from other data sources. I have tried the same and I have found this to be running fine. Just to be on the safe side you can try this.

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.

David_Singleton

What I said no to, was Tigers suggestion of importing data via SQL directly into NAV tables.

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.