ODBC Access from MS SQL 2005 Server to Navision 4.0

sodexho

Hello,

I need help for connecting from a MS SQL Server 2005 to Navision 4.0 SP1.

when I try to create the Linked Server in MS SQL Server Management Studio, I get the following error:

---

Server: Msg 7399, Level 16, State 1, Line 1
[b]OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[/b]Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TestNav"

---

I did the following installation steps:

- install the ODBC Navision driver from the Navision CD (version equal to Navision Server) on the MS SQL Server
- created an System ODBC driver with the name NAVISION and tested it successfully with a small tool
- created a Linked Server in the MS Management Studio as MSDASQL (=Ole DB Provider) , i also tried this as an TSQL statement:

EXEC sp_addlinkedserver 
   @server = 'TestNav', 
   @srvproduct = '',
   @provider = 'MSDASQL', 
   @datasrc = 'NAVISION'
GO

EXEC sp_addlinkedsrvlogin 'TestNav', 'false', 'DOMAIN\Administrator', 'administrator', '**********'

SELECT * from OPENQUERY(TestNav,
	'select * from Salutation')

But I always receive the 7399 error mentioned above.

Can you please help me?

ara3n

I do not believe Linked server is supported under Native server.

I would move to sql server as backend for Navision and then you can setup a linked server between sql to sql.

sodexho

Thanks for your anwser,

but it's a strategic decision from my company to keep the Navision file database. I read a lot of postings from people which established a connection from MS SQL 2000 Server over the C/ODBC-Driver to the Navision Database. So it can't be impossible to do the same connect in MS SQL 2005. The ODBC-Driver is working, i checked that. So i think the main problem is to configure the Linked Server correctly and get some infos about the error.

Perhaps the ODBC Driver from Navision 4.00 SP1 cannot operate with Linked Servers in MS SQL 2005 Server? I'm a litte bit confused that two Microsoft products cannot cooperate?!

Truby

This should work fine. I have this same setup running perfectly on a 40gb database at the moment.

Check the server firewall, i had to manually add the "Server.exe" and port 2407 manually to the exceptions for Navision.

To check if its the windows firewall, just turn it off on the server for testing.