SQL 2005 to Navision 2.6 Via linked Server

DataCircuit

Hi,

I am working with a customer who has a C/Side 2.6 DB, and 3.6 shell. I am trying to setup an ODBC connection from MSSQL 2005 on Win2003.

I have tried C/ODBC 3.6 drivers and received ISAM errors in SQL, probably due to the MDAC version in Win2003.

The customer has indicated that they have now installed the ODBC granual, and I should be able to use the MS Dynamics Version 5 ODBC drivers.

With the MS Dynamics ODBC Driver my queries(via linked server) just time out, and SQL returns a "Cannot initialize object OLE DB for MSDASQL"

Any thoughts are appreciated.

ara3n

Has the client upgraded their server and all their clients to 5.0 as well?

If not, you will not be able to use 5.0 odbc driver with 3.6 server

Fommo

I've tried every ways possible to do the same between Navision 3.70 and SQL2005 without much of success.
NODBC for 4.0 or higher wont work for Native DB 3.x or lower because they are supporting different API:s. C/ODBC for 2.x and 3.x is API 2.0 and NODBC for 4.0 and 5.0 is API 3.0.
So you are stuck with the C/ODBC 2.0 version.

I tried to set up the Native DB as a Linked Server. I also tried to use ADO in a DTS package wrapped in a SIS package on the SQL2005. Then I could read the data, but when I tried to write I got that ISAM Error. When I googled about it I found out that it mostly luck if you get it to work writing to the C/ODBC driver.
I managed to get a working connection for both reading and writing setting up a Linked Server in an Access database and then use that Access database as a linked server in SQL2005. Those nested servers gives really bad performance though so it was no working solution after all. ](*,) ](*,) ](*,)

THEN I finally tried to use Automation Server and the world just started to shine agian... Automation is GRRRREAT! 8)
Not only it gets so much faster, you also get it independent on database option. So if you are going to use the communication for other customers you only need to do it once and then you can export you package to all Navision installations.

Start with an automation object of 'Microsoft SQLDMO Object Library'.SQLServer2 and use the method Connect. Then use the property Databases and go from there to point out the database you want to communicate with. Then you can shoot T-SQL to it to do what you want.

Sure, the C/ODBC has been developed for some reason, but it's neither stable nor useable.