• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3796
  • Last Modified:

sql server 2005 linked server to dbf file

I'm wondering how to setup a linked server for a clipper dbf file.  can someone please post the transact sql to do this??
0
Fraser_Admin
Asked:
Fraser_Admin
  • 5
  • 5
2 Solutions
 
Chris MangusDatabase AdministratorCommented:
Try this:

select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase III;HDR=NO;IMEX=2;DATABASE={path to dbf}','select * from {filename}.dbf')
0
 
Fraser_AdminAuthor Commented:
how do i add the linked server though?
0
 
Chris MangusDatabase AdministratorCommented:
I was never able to do this via linked server.  The best I could ever do was to put the data into a holding table and then work with it.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Fraser_AdminAuthor Commented:
so i need to access this table in a sp to select values to select into another table.  will this be possible using your method?
0
 
Fraser_AdminAuthor Commented:
I tried the 3 ways of calling it.  My table is chipbill.dbf and the path of where it is located is \\serv\apps\chip.  Is there something i'm missing here.  All 3 ways produce the error below...

select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase III;HDR=NO;IMEX=2;DATABASE=\\serv\apps\chip\chipbill.dbf','select * from chipbill.dbf')

select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase III;HDR=NO;IMEX=2;DATABASE=\\serv\apps\chip','select * from chipbill.dbf')

select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase III;HDR=NO;IMEX=2;DATABASE=\\serv\apps\chip\','select * from chipbill.dbf')

OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'\\serv\apps\chip' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".
0
 
Chris MangusDatabase AdministratorCommented:
You don't have the Jet OLEDB provider on your machine.  You can download it from Microsoft.
0
 
Fraser_AdminAuthor Commented:
when i go into the setup for linked servers i see the option:
microsoft jet 4.0 oledb provider.

does that mean it would be installed on the machine?
0
 
Chris MangusDatabase AdministratorCommented:
It should be on the machine then.  I'd try bringing the dbf locally first and see if you can open it.  If so then there is a permissions problem.  If you can't then this provider can't open that dbf and we'll have to find another that can.
0
 
Fraser_AdminAuthor Commented:
yes that fixed it.  it may not like the fact that it is on another machine.  not a biggie, i can move it.

now how do i actually query it...

select * from mytable where myfield = 'BLAH'

i can't seem to get this to work.
0
 
Chris MangusDatabase AdministratorCommented:
select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase III;HDR=NO;IMEX=2;DATABASE=\\serv\apps\chip\chipbill.dbf','select * from chipbill.dbf')  Where myField = 'BLAH'
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now