Setting up a linked server - looking for tips and tricks

Resender
Resender Member Posts: 119
edited 2012-04-18 in Navision Attain
OK, I got to setup a linked server in the following environment
*Windows 7
*MS SQL Server 2008 R2
*MS Virtual PC

We got a Virtual PC running Windows XP, and on that XP (called VIRTUALXP-88...) we got Navision Attain 3.6 installed.
The fdb file containing our database is not located on the virtual machines virtual hard drive (no pun intended) but on a secondary partition of our physical machine.
We run Navision either as a virtual application or in the virtual machine itself depending on what we are working on.

On the physical machine running Windows 7 we now want to create a linked server from the SQL Server to the Navision Attain one.
I've used the following:
Linked Server:Nav
Provider: Microsoft OLE DB Provider for ODBC Drivers
Data Source: VIRTUALXP-88...
Provider String: UID & PWD
Location: D:\name_of_our_database.fdb
Catalog:

When I try to create it it runs into error,
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "NAV".
OLE DB provider "MSDASQL" for linked server "NAV" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

There people I could ask it inhouse but their on leave for another week or 2 and I need to resolve this.
Thank you for any input

Comments

  • Resender
    Resender Member Posts: 119
    Ok first tip check whether the systems language settings are correct for the Identifiers you use to connect to your SQL server.
  • Resender
    Resender Member Posts: 119
    OK, when I test the ODBC via Excel or any other program (for instance a 'hello world' type of quick program) it runs fast.
    If I run the same query through the linked server it takes hours with no result.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    What are you actually trying to do?
    David Singleton
  • Resender
    Resender Member Posts: 119
    Setting up a linked server to use reporting services on the navision data, I know you could just use ODBC on the reports but I mean to understand by using a linked server you have more options on your reports
  • Resender
    Resender Member Posts: 119
    OK, got it working as fast as the sql request :D

    To do this I had in the Provider opstions for MSDASQL, which apparently Microsoft OLE DB Provider for ODBC Drivers, I had to check Nestled Queries,Level Zero Only and Support 'Like' operator. From what I found their is an advisement to turn off Allow Inprocess but I haven't noticed much difference.(Off course then again haven't done much with it)

Categories