An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The OLE DB provider “SQL Server” for linked server “(null)” reported an error. One or more arguments were reported invalid by the provider.
Cannot obtain the schema rowset “DBSCHEMA_CATALOGS” for OLE DB provider “SQL Server” for linked server “(null)”. The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)
well I want to merge some navision transactions / bookings (multiple clients) with some data which is stored in an access database into to let us named it dataware house. First I try to use SSIS but it didn’t work either… So I tried the linked server alternative for the navision data. I would implement a stored procedure which select (out off navision) and copy the required data into the datawarehouse (sql server 2005) let’s say every hour…
You have a “dataware house” based on SQL Server 2005?
You have a native Dynamics NAV 4.0 database?
You want the SQL datawarehouse to automatically read data from the native NAV database?
Looking at your first post, then it looks like you’re using the standard Microsoft ODBC driver. When you want to read data from a native NAV database, then you need NODBC, which is Navision’s own ODBC driver.
Just one more thing. I can give a tip I always use. When you create a new data source, then I can only recommend that you test it first. Personally I always use Microsoft Excel, as it is very easy to see the results.
Totally agree there is no better tool to check a new datasource quick and dirty. I’ve done that already and it works (I’ve wrote that in my first “post”).
I have installed the Navision ODBC Driver and created a DSN. I’ve used this DSN to access Navision via Excel. Works Fine.
nav_BeSch_KG is the name of the DSN. That was what I’ve used at my first try (see my first post)…
I would try to setup the connection directly in SQL Linked Servers, rather than using the ODBC driver to refer to the DSN which then connects via Navision driver. I’ve never done this, but it may work. Does the Navision driver appear on the ‘provider’ list in SQL?
Secondly (and I have no idea if this would work or not) but try setting up the DSN on both your client and the server (unless you’re on the server directly already).
Your first approach is what first came into my mind as well but as you mentioned the Navsiosn driver doesn’t appear in the provider list. Do you have any idea hoe to add?
Secondly I’ve done that before and it didn’t help but I am interested in your intention why this could help.
I have no idea how to add it to appear - perhaps do the install of the navision odbc driver on the SQL Server (which may or may not be a good idea from a DBA perspective)? I’ve never had to do this with any kind of driver before. Soembody else here I’m sure would have some thoughts on this.
Secondly, well, with SQL 2000 and DTS, I know when editing a package, many of the references were local to the editing client. For example, if a flat file was referenced to c:\test.txt, then when designing the package from another host, simple tests would look locally for the file. My thought was perhaps, even in SQL 2005, there was a minor quirk that was looking locally for the DSN. Being you’ve tried it, I guess it doesn’t help!
Are you able to extract the data into an MS Access database using your current DSN? If you can, you may be able to setup linked tables, and you could then in theory use an ODBC driver from SQL to look into that Access DB (which would be directly linked to your NAV tables). Its messy, but it might just work.
