ODBC problems with SQL Server

hipogrito

Hi:

Navision Financials 2.60
SQL Server 2000 SP4
Windows 2000

I've got two Navision databases in the same hard disk, and I want to access both of them from SQL Server (automaticaly). Trying this I've found some weird things that probably are familiar to someone around here.

As I don't know how to set up two Navision servers in the same machine (Any suggestions?) I thought in reading one database setting up a Navision server (no problem here) and another one being local (here's the problem).

When I set up a System DSN for a local database it seems it's ok as I could access to all the companies. I open SQL Server and make a DTS, to pass data from one Navision table to one SQL Server table and it works... BUT only the first time!!! The rest of the times I try to execute the DTS I find an error: [Navision Financials ODBC Driver] ISAM error.

This is very strange... I close SQL Server, open it again, and I can run this DTS only the first time, the rest of the times again I find [Navision Financials ODBC Driver] ISAM error.

By the way, the DTS is soooo simple, just one connection for Navision, one for SQL Server and a data transformation line...

This is weird!

Any suggestions?

Thank you very much,

Best regards
Fran

DenSter

Bypassing Navision to write directly into the SQL Database also bypasses important business logic, and DBMS type logic, for instance how key values are built, and possible flowfield values, etc.

Using DTS is not the right way to copy data from one Navision database to another, unless you know exactly what you are doing, and you're only doing it for static data (like units of measure or simple stuff like that).

Could you tell us why you are doing this? If you want a local test database and you want it kept up to date you're better off periodically refreshing the entire database.

hipogrito

Hello Daniel:

Thanks for your answer.

(more info: Navision is working with a Navision own database mode, not SQL Server)

Maybe my poor English doesn't help to explain myself correctly, sorry.

This is a datawarehousing project, in the design time.

My sources of data will be, among others, two Navision Servers. There won't be problems here as I think I know how to read from Navision Servers.

The problems come for me in my design time. I have both Navision databases in a hard disk in one machine. So, I could start a Navision Server with just one of the Navision databases. I don't know how to do it with both in the same machine. In my production environment I won't have that problem as each Navision database will be in its own machine but now, for design purposes, I just have one machine to read data from the two Navision databases.

That's the reason I've tried to put a System DSN in local mode for one of the Navision databases, and accesing directly to the database files... but I've found those strange problems that I wrote in the other post.

In short, I just want to read all the data from both Navision databases and pass it to SQL Server, which will be a part of a datawarehouse system. I could do this manually, I mean, I can start the Nav server with one database, read the data, stop the server, start again the server with the other database and read the data, but I'd like to do it periodically with DTS, as it will happen in the datawarehouse.

I hope this time I've explained the problem better :-)

Thank you,

Best regards
Fran

DenSter

Alright, sorry about that then. I thought you were copying data from one Navision into another. Reading from any type of database should always be ok of course.

You can run more than one Navision database server on one machine. It's been a long time since I've done this, because I now use the SQL option only. I think it's as simple as installing it twice into two different folders, with two different names. You'll have to add both servernames to your services and hosts files, and from there you shold be able to access them both. Check out the w1w1ism.pdf document in the doc folder of the product cd, that should give you enough information to do this.

Savatage

http://www.mibuso.com/forum/viewtopic.php?t=4608

hipogrito

Hi:

Thank you for the info!

Regards
Fran