Linked Server to dBASE

[scott]

I'm trying to create a "Linked Server" in QA to a dBASE file. Below are some
examples I've tried. They seem to create the "Linked Server" in Ent.
Manager, but give errors when i run a SELECT .. OPENQUERY statement. They
also give errors with Ent. Manager when trying to expand the linked server's
node.

The errors say the path is wrong, which it isn't. Other errors when clicking
"tables" under the linked server are "OLE DB" errors.

Does anyone have the correct syntax for creating a linked server to a dBASE
file? Which provider should I use?

-- Example 1:

Exec sp_addlinkedserver @server ='alarm_dBASE',
@srvproduct ='DBASE',
@Provider ='Microsoft.Jet.OLEDB.4.0',
@datasrc='E:\data\databases\myDBASEfile.dbf'

-- example 2

exec sp_addlinkedserver 'alarm_dBASE2',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'E:\data\databases\myDBASEfile.dbf',
NULL,
'dBase IV'
go
exec sp_addlinkedsrvlogin @rmtsrvname='alarm_dBASE2',
@useself = 'FALSE',
@locallogin = NULL,
@rmtuser = NULL,
@rmtpassword= NULL

go

[Erland Sommarskog]

scott (sba...@mileslumber.com) writes:
> I'm trying to create a "Linked Server" in QA to a dBASE file. Below are
> some examples I've tried. They seem to create the "Linked Server" in
> Ent. Manager, but give errors when i run a SELECT .. OPENQUERY
> statement. They also give errors with Ent. Manager when trying to expand
> the linked server's
> node.
>
> The errors say the path is wrong, which it isn't. Other errors when
> clicking "tables" under the linked server are "OLE DB" errors.

Note that the path is from the SQL Server machine, not on your local
machine. (Unless SQL Server runs on your local machine.)

Since I don't know anything about dBase, this is all I can contribute
with.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[scott]

i'm doing all of this from the server.

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns97BCD2B8B...@127.0.0.1...

[Milan Kosanovic]

You're using the wrong driver so it cannot work. You're trying to use the
Jet engine (Microsoft Access .mdb engine).
You have an ODBC driver for .dbf files. I'm not shure if it works with Dbase
IV (most probably it'll do the trick).
Go to Control Panel - Data Sources (ODBC) and make a DSN.
change @Provider to OLEDB for ODBC and supply your DSN (search the web on
exact syntax)

Milan

[Woo]

Use "Microsoft OLE DB Provider for ODBC Drivers" instead of MS.Jet
NO "@datasrc" but use provider string as of "Driver={Microsoft dBASE Driver
(*.dbf)};DriverID=21;Dbq=(you file, no filename);Mode=Read;

i.e. @provstr = 'c:\' if you file is under c drive