Import Data from one into another SQL2000 Database

drecksgekritzel

Hello,
i have already used the forum search but i have not find the right solution for my problem.

our firm has one server with two SQL 2000 databases.
Now we have the problem that we want to import data from the first into the second DB but i have no idea how i can solve this problem. And we dont want to do this for one time but for periodic. That means we need a static connection to refresh the data at any time...

i have only found solutions for reading data out of a navision DB with an ODBC connect or something but nothing for read into navision from a other DB.

We are using Navision 4.0 with SQL 2000 and the second DB is a SQL 2000 too.

I hope the explanation is understandable and thank you for your answers...

kriki

[Topic moved from Navision forum to SQL General forum]

Waldo

Depends on what you want to do.

If you have to import data into an NAV database, then you'll have to foresee something that validates your data in NAV. Basically, that means you'll have to use a NAS to import the data into the NAV tables.

If this is not the case, there are numerous options:
- SQL Views
- DTS
- ...

Can you describe more in detail what you want to achieve. What types of database, what is the direction the data flows, ... .

stryk

The easiest way could look like this:

INSERT INTO DB1.dbo.Table1 (Field1, Field2, ...)
  SELECT Field1, Field2,... FROM DB2.dbo.Table2 WHERE ...

This copies the data from Database 2/Table 2 into Database 1/Table 1 ...

Waldo

OK, assuming that the second db is not a NAV db ... :whistle:

drecksgekritzel

Ok i'm trying to go more into detail...

The db from which we want to read the data out is a normal SQL db. the other in which we want to read in is NAV 4.0 SQL db.
So the data flows from the normal SQL db into the NAV db....
I think we need C/AL code or something to connect to the "normal" SQL db an refresh our NAV SQL db.

If the NAS is the way which solves the problem you can maybe describe a little bit more in detail what i should do... :oops:

Waldo

Well, data flows into NAV, so you'll need to do customization inside C/SIDE to do the import.

Actually, these are options from the top of my head (all customisation inside C/SIDE):
- using ADO to read your other data
- using SQL Views, create linked table in NAV and use the data displayed in that table

NAS offers just a way to automate the synch process. If you don't want anyone to puch the button to import the data, let NAS do it for you at regular basis... .

drecksgekritzel

Tanks for your fast answers! =D>

But now there are new questions...

Can you explain how we have to use ADO? What are you mean with other data?

And what are SQL Views? Are they only SQL Statements or how can i imagine this?

Unfortunately it is not as easy to solve this problem as i thought at first

Waldo

It seems that you have some reading to do .

I seems that you can solve your problem with SQL Views.
If I remember correctly, there is a chapter in the bible (Application Designer's Guide - w1w1adg.pdf on the product cd) about this. It just comes down on defining a view in SQL, and linking that view to a table in NAV. This way, you can read data just like it was a table in NAV.

To catch up on views, you should google on views. There is a lot to find:
- http://www.sql-server-performance.com/articles/dba/view_basics_p1.aspx
- http://msdn2.microsoft.com/En-US/library/aa214068(SQL.80).aspx
- ...

Second: ADO. This is slightly more complicated.
It comes down to connecting to a database just like you do in VB.NET or something. You'll be using the same component (ADO) to connect to the database in C/AL. If you search this forum, you'll find some interesting posts about ADO. Plus, there is a very interesting download as well:
http://www.mibuso.com/dlinfo.asp?FileID=589

Good luck!

stryk

But anyway: the INSERT/SELECT option should still be considered! There is no "NAV DB vs. Non-NAV DB" - it's both SQL databases!
Thus, if there's no NAV business logic involved, but just a simple transfer of data, this would be the fastest way! Of course, you MUST regard the compatibility of datataypes, date- and time-requirments, uppercase characters in Code fields, etc. ...

If this is not an option you should look into the "View" thing first, as it's less complex than the ADO thing ...

Tomas

stryk wrote:

But anyway: the INSERT/SELECT option should still be considered! There is no "NAV DB vs. Non-NAV DB" - it's both SQL databases!
Thus, if there's no NAV business logic involved, but just a simple transfer of data, this would be the fastest way! Of course, you MUST regard the compatibility of datataypes, date- and time-requirments, uppercase characters in Code fields, etc. ...

If this is not an option you should look into the "View" thing first, as it's less complex than the ADO thing ...

After such "there is no NAV business logic involved" data transfers, usually clients lose ability to do backups from Navision (if data isn't checked, it doesn't mean it gets corrupted. however, it might be corrupted before transfering).

Waldo

Don't know what you mean with that ... :-k

drecksgekritzel

Good morning,

now i will try it with a sql view but i have a lot of problems

can someone explain what is a sql view exactly? i just know that it is a sql statement but how do i create it, where do i und how do i get it into navision?
i have already searched the forum but there are no posts which can explain it to me understandable...

best regards and thanks for your answers...

Waldo

Here is what you could do:

take the pdf on the product cd: CD:\Doc\w1w1adg.pdf and read the chapter about "Linked Objects" under "Customizing and Maintaining Tables".

You'll need the table property "LinkedObject" or "linked and tables". If you search on this property on this forum, you'll get a bunch of links that are interesting:
- http://www.mibuso.com/forum/viewtopic.php?t=21743&highlight=linkedobject
- http://www.mibuso.com/forum/viewtopic.php?t=19361&highlight=linked+tables
- ...

stryk

Tomas wrote:

After such "there is no NAV business logic involved" data transfers, usually clients lose ability to do backups from Navision (if data isn't checked, it doesn't mean it gets corrupted. however, it might be corrupted before transfering).

Well, I'm doing this for more than 5 years now, and I NEVER experienced this. Again: It is both SQL Server Databases, so transferring data between them is "a piece of cake". There is NO difference if you execute an INSERT from Management Studio, or if C/SIDE is executing it - it is the SAME INSERT!
The only chance to "corrupt" the data from a NAV point is, if for example, you're inserting lowercase characters into Code fields, or invalid DateTimes in Date or Time fields etc..

@drecksgekritzel (funny ):
If you could specify the tables and fields, then we maybe could advise an example ...

Tomas

stryk wrote:

Tomas wrote:

After such "there is no NAV business logic involved" data transfers, usually clients lose ability to do backups from Navision (if data isn't checked, it doesn't mean it gets corrupted. however, it might be corrupted before transfering).

Well, I'm doing this for more than 5 years now, and I NEVER experienced this. Again: It is both SQL Server Databases, so transferring data between them is "a piece of cake". There is NO difference if you execute an INSERT from Management Studio, or if C/SIDE is executing it - it is the SAME INSERT!
The only chance to "corrupt" the data from a NAV point is, if for example, you're inserting lowercase characters into Code fields, or invalid DateTimes in Date or Time fields etc..

@drecksgekritzel (funny ):
If you could specify the tables and fields, then we maybe could advise an example ...

I've seen NAV with random error on some forms. I couldn't do backups. And only after some time I realise, that there was a CODE field, which was imported directly into SQL. And it was in lowercase!

As simple as that prevents you from doing backups. Of course, the way, why it was imported/changed/entered in lowercase was unknown to me.

st0328

Hi All

I have the same Situation but, in my case both the DB are NAV.

One DB in Server and another in Local. Periodically, the data from server DB Should go and sit in the Local NAV DB for tables (a)Sales Header. (b)Sales Line

I am Designing to write to code in Server DB.

Can some one guide to solve this situation.

NAV DB Ver 2009 sp1.
SQL SERVER 2008.

Waldo

I don't understand. You're saying that both database are in NAV (which I think it means that they're native. One local, one database server), but still, you're telling us that you're on SQL Server 2008?

Can you elaborate more?

stryk

Waldo wrote:

I don't understand. You're saying that both database are in NAV (which I think it means that they're native. One local, one database server), but still, you're telling us that you're on SQL Server 2008?

Can you elaborate more?

Ditto.

Assuming you're running indeed SQL Server databases: for the scenario Server-DB to Local-DB (which is actually a Server-DB, too, just on a local machine I suppose) you should look into these features:

- Linked Server: THen you could transfer with INSERT/SELECT directly
- SSIS: Create packages to transfer the data
- Replication

See "Books Online" about details.

Cheers,
Jörg