Linked Server With DBF Files

In my previous post I wrote about Linked Server with Excel. Now we will see how to create a linked server for DBF files. That is accessing and querying DBF files from SQL Server Management Studio.

First I’ll show you how to set up the Linked Server from Management Studio.

Open the Management Studio and navigate to Server Objects and then to Linked Server. Right click on the Linked Servers node and select “Create New Linked Server”.
Enter in the name of your linked server in the text box that appears next to the “Linked Server” label. This can be any name that you would recognize to describe the object.

Then under Server Type, choose the “Other Data Source” Radio button. And select the provider “Microsoft Jet 4.0 OLE DB Provider” from the list.
For “Product Name” enter “Microsoft Jet”
In the “Data Source” text box, enter the full folder path to your dbf files. For mine I entered this: “D:\DBF Project\Data”.
For the “Provider String” enter “dBASE 5.0”

Then go to the security page. Here you have 2 options.
Either select “Be made without using a security context”
OR
Select “Be made using this security context” radio button found near the bottom of the window. The “Remote login” and “With password” text boxes become active to be filled in.
In the “Remote login”, enter “Admin” as the login user. Leave the password text box blank. Well I don’t know the exact answer why this needs; I tried searching on Net but couldn’t find the answer, but this how it works.

And say “ok”, now your Linked Server DBF files is ready. Just expand the Linked Server and then now created Linked server, after expanding the Default you will see Tables and Views. Expand the Tables node to see a list of table objects for the folder.

Now you can query the DBF files just like any other table from management studio. Now open the new query window to write a query on DBF files. Just remember you have to include the linked server and table name in a four part address. Namely, [LinkedServername]…[TableName].

So your select query will be like
SELECT * FROM [Linked_Server_Name]…[TableName]

e.g.
SELECT * FROM DbfLinked…Items.

Here DbfLinked is the linked server name I gave and “Items” is the table name. Observe that there are 3 dots between linked server name and table name.

We can use almost any select statement to retrieve our dbf data. For example JOINS with other tables in Sql Server or with other DBF tables, WHERE conditions, GROUP BY, HAVING, ORDER BY, even the UPDATE, DELETE or for that matter any valid Sql statement you normally do with tables.


Same Linked Server you can create using Sql Statement.
-- To create Linked Server With DBF Files
EXEC master.dbo.sp_addlinkedserver
@server = 'DbfLinked',
@srvproduct = 'Microsoft Jet',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'D:\DBF Project\Data',
@provstr = 'dBASE 5.0'


/* Now use either of two secuirty Context*/
-- To select security Context “Be made without using a security context”

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

-- Or using “Be made using this security context”
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'DB',
@useself = 'False',
@locallogin = NULL,
@rmtuser = 'Admin',
@rmtpassword = ''


- Mangal Pardeshi.