CODEWITHSUNDEEP
sqlsql-server
Johnny Circle
Johnny Circle

Reputation: 41

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

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?

Upvotes: 2

Views: 13924

Answers (3)

AutoCiudad
AutoCiudad

Reputation: 779

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

Upvotes: 6

smoore4
smoore4

Reputation: 4866

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.

Upvotes: 0

RThomas
RThomas

Reputation: 10882

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

Upvotes: 2

Related Questions