how to create linked server to .dbf in sql 2008?

Question

We have a sql 2008 and would like to create a linked server to a dbf file. which provider should I choose, the Providers I have (ADsDSOObject, MSDAOSP, MSDASQL,MSIDXS,MSOLAP, SQLNCLI10, SQLOLEDB)

if none of these will do, is there any other providers?

Answer 1

My Step by Step was:

• Intall Drivers Microsoft Access Database Engine 2010 Redistributable AccessDatabaseEngine.exe 32Bit AccessDatabaseEngine_x64.exe 64Bit

• Create Sql 2012 LinkedServer

  EXEC master.dbo.sp_addlinkedserver
  @server = 'LinkedServerName',
  @srvproduct = 'Microsoft ACE OLEDB 12',
  @provider = 'Microsoft.ACE.OLEDB.12.0',
  @datasrc = 'C:\DBF_Directory',
  @provstr = 'dBASE 5.0'

  EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = 'LinkedServerName',
  @useself = 'False',
  @locallogin = NULL,
  @rmtuser = 'Admin',
  @rmtpassword = ''

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

  select * from LinkedServerName...TableName

• If you get this error:

  • OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "LinkedServerName" returned message "Unspecified error".
  • Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "LinkedServerName"

  The problem that I found is that the user running the qry does not have permition on the sqlserver temporary directory

• In this case y use SysinternalsSuite ProcessMon.exe Downloaded from https://technet.microsoft.com/en-us/sysinternals/bb842062.aspx

  • Apply Filter Ctr+L and
  • filter Result = 'ACCESS DENIED' click Add
  • Select Show Only File System Activity
    and when the sql query runs, you will see somthing like this:

  sqlservr.exe:000 QUERY INFORMATION
  C:\Users\MSSQLSERVER\AppData\Local\Temp ACCESS DENIED
  grant permission to the C:\Users\MSSQLSERVER\ directory
  or
  C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp ACCESS DENIED
  grant permission to the C:\Windows\ServiceProfiles\NetworkService\ directory

  So I granted Read/Write permissions to the user that it is running que sql qry and everything runs ok.

  NOTE: grant permissions to the MSSQLSERVER object is a little tricky.

  1. I have done:
     • Properties in the folder
     • Select Security Tab
     • Click Edit Button Click
     • Add Button
     • Enter the object name: NT Service\MsSqlServer Click Check Names
     • Select MSSQLSERVER object Click Ok
     • Select Allow Full Control

Answer 2

I've seen a few approaches to linking to a dbf. I think the one I like best is to use the Visual FoxPro provider that you can download from Microsoft.

Once it's installed the most common way I've seen is to use the following settings:

From EM Linked Server Properties select "OLE DB Provider for Visual FoxPro" as the Provider name

Product name: leave blank

Data source: location of the folder containing the dbf files

Provider string: VFPOLEDB

---

Other options for linking ot a dbf file include MDAC, ODBC, etc. but what you can do via the link through these options is limited at best, buggy and unpredictable at worst depending on the exact versions. The visual foxpro provider seems to do the best job.

---

This related SO link also has some additional information regarding linking up to a DBF

Answer 3

You cannot create a linked server with 64-bit SQL Server and 32-bit DBF drivers.

However, you can open a new SqlDataSource in Visual Studio using the .NET Framework Provider for OLE DB then MS OLE DB Provider for VFP. The connection string should look like this (or with UNC)

Provider=VFPOLEDB.1;Data Source=h:\Programs\Data;Persist Security Info=True;User    ID=Your_user;Password=your_password 

The config wizard includes a part to test your SQL. You can use this run ad hoc SQL.