1

I have a 32bit SQL Server 2014 Express (BenVM) set up on a virtual machine running Windows 7 64bit. This instance of SQL Server has a linked server connected to it called MSCRIBE2 using the VFPOLEDB (foxpro) provider. From my virtual machine I am able to use openquery to query my linked server.

I also have a local machine (Windows 10 64bit) which also has 32bit SQL Server 2014 Express set up on it (BenLocal). This instance of SQL Server has a linked server connected to it called MSCRIBE using the VFPOLEDB (foxpro) provider. From my local machine I am able to use openquery to query my linked server.

From my local machine I am able to connect to the SQL Server instance running on my virtual machine (BenVM) and query native tables, however when I try to run the exact same query that worked when logged directly into my VM I get the following error:

OLE DB provider "VFPOLEDB" for linked server "MSCRIBE2" returned message "Invalid path or file name."

Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "VFPOLEDB" for linked server "MSCRIBE2"

I have tried using both windows authentication and a SQL User as my login for BenVM from my local machine, however this does not seem to matter. I have also tried both a Local Service and a Named Account as the "Log On" for the SQL Server running on my VM but this did not seem to help either.

Both of the linked servers MSCRIBE and MSCRIBE2 point to the exact same network resource to which i have full read/write permissions. and I am not using a mapped drive address rather \\\ServerName\Folder

The query being used is

Select * from openquery(MSCRIBE2, '
Select ORDER.DATE,ORDER.ORDERNR
    from [ORDER]
    where cast(ORDER.DATE as date) = {^2018-03-08}
')

I would appreciate any help.

**** Update It looks like the issue is directly related to my openquery above, I did not try this earlier but just now I tried it as a regular query and I was able to connect. The query I used now is:

    Select [ORDER].[DATE],[ORDER].[ORDERNR]
    from MSCRIBE2...[ORDER]
    where cast([ORDER].[DATE] as date) = '2018-03-08'
Ben
  • 37
  • 1
  • 7
  • Since ORDER and DATE are reserved words in VFP, it's not surprising that you need to bracket them. – Tamar E. Granor Apr 10 '18 at 20:32
  • @TamarE.Granor I tried using brackets in the openquery but I still get the same error message. Im guessing it has to do with how the query is being packaged when its part of the openquery but I cannot for the life of me figure out what. Are there any settings that would affect how the openquery operates which would make a difference if I am logged into the SQL Server from the local machine vs a workstation? – Ben Apr 11 '18 at 11:50
  • I believe the issue is in security. In either case, I would double check if I am doing the call with an 32 bits driver (ie: a C# code compiles to 64 bits on a 64 bits machine unless explicitly targeted x86). With OpenQuery, would you at least try giving the full path instead of just table name (like " ... from ([c:\My Folder\Order]) ). You don't need to use brackets and keywords work well provided you put the aliases (like Order.Date - both order and Date are keywords in fact). – Cetin Basoz Apr 12 '18 at 14:13
  • Also keep in mind VFP linked server is only for querying and doesn't support CUD of CRUD operations (frankly for me it has always been a trouble only). You might want to use Sybase ADS driver instead. – Cetin Basoz Apr 12 '18 at 14:13
  • @CetinBasoz Bingo! I used the full network path and was able to complete the query successfully.... Im using the query in Power BI to create interactive Dashboards and Reports for an archaic DOS based ERP system that we still have running, so being able to write a filtered query is all i need for importing purposes. If you want to post your comment as an Answer I can mark it as such. – Ben Apr 12 '18 at 17:22
  • For the sake of closing as solved I will post as an answer. Thanks. – Cetin Basoz Apr 12 '18 at 17:41

1 Answers1

1

I believe the issue is in security. In either case, I would double check if I am doing the call with an 32 bits driver (ie: a C# code compiles to 64 bits on a 64 bits machine unless explicitly targeted x86). With OpenQuery, would you at least try giving the full path instead of just table name (like " ... from ([c:\My Folder\Order]) ).

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39