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

sp_addlinkedserver provider/connection string problem

I would like to Import data in dBase III format to sql server. I am trying to use sp_addlinkedserver to achieve this.

when i run
     sp_addlinkedserver 'dbf', '', 'MSDASQL', 'dBase Files', Null, Null, 'C:\dbf Folder'

     where 'dBase Files' is an ODBC User DSN configured to access dBase Files
               'C:\dbf Folder' is the folder where my dbf files are located

executing this stored procedure doesn't give me any error. but when i try accessing any dbf files through
    Select * from  OPENQUERY(dbf, 'Select * from Tags')
or when i execute
    sp_catalogs 'dbf'

it gives me:

Server: Msg 7399
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] '(unknown)' 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.]

I do not know where I went wrong. I already tried doing a couple of variations in creating the linked server. using the connection string below:

    sp_addlinkedserver 'dbf', '', 'MSDASQL', Null, Null,
                                'Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\dbf Folder'

gives me a similar result.


I'd appreciate any help. Thanks !!!





0
PePi
Asked:
PePi
  • 5
  • 4
1 Solution
 
PePiAuthor Commented:
additional note:
   I cannot use DTS in importing the dbf files. Thanks again!
0
 
jdlambert1Commented:
Try opening the properties of the linked server in Enterprise Manager and see if everything got setup correctly. Also, try expanding the linked server to the tables and see if the table names show up that way.
0
 
DaveHavardCommented:
Try:

EXEC sp_addlinkedserver
   @server = 'dbf',
   @provider = 'MSDASQL',
   @datasrc = 'dBase Files'

You should need the path to the dBase files as that should all be configured in the ODBC source.

Dave
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
PePiAuthor Commented:
Thanks for responding guys

jdlambert1:
    I did open the properties and as I've stated earlier I am not sure if it did git setup correctly. I can see the linked server from Enterprise Manager but when I click on the TaBles I get these Error Messages:

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
Driver's SQLSetConnectAttr failed]
Driver's SQLSetConnectAttr failed]
'(unknown)' 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.]

Dave:
   The path to the dBase files changes from time to time depending on the selection of the user. I did not set a specific path when I created the ODBC DSN. My DSN connection has 'Use Default Directory' as the default. Is there any way where I can set up the path on the fly? I am not sure if 'Catalog' in sp_addlinkedserver is the same as 'Initial Catalog=myPath' in my connection string when i open these dbf files in VB6.

Thanks again guys. I really need your help here.

P.S. have any of you  guys tried using OPENDATASOURCE? maybe i can use this instead of creating a linked server.

Thanks again.


0
 
jdlambert1Commented:
OPENDATASOURCE would be good for troubleshooting, even if you go back to a linked server later. Here's how I referenced and opened a dBase file named "assist.dbf" from Query Analyzer:

SELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\temp\";Extended Properties=dBASE IV;')...assist
0
 
PePiAuthor Commented:
thanks for the reply jd. i know that the syntax for OPENDATASOURCE is pretty much straight forward but i keep having this
   OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
   [OLE/DB provider returned message: 'C:\VBP\' 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.]
and its driving me crazy.

i've checked and double checked if this path/folder exists. sure enough, it does. I've tried removing the last "\" and still got the same error message. i've also made sure that my dbf file is in that folder. am i missing a step here? do i have to set up something even if i only needed to access a local dbf file?

i surely appreciate your help jd
0
 
jdlambert1Commented:
Yeah, I encountered a lot of error messages while testing, and 90% were completely misleading. You can get that error, for instance, when the extended properties value is incorrect. Also, the file path has to be from the perspective of the SQL Server you're connected to while running the query. For example, if you're using QA on your workstation, connected to a different computer running SQL Server, the file path would have to be for the SQL Server box, not your workstation.

You can try importing the dBase file into Access to confirm which version of dBase, if you're not sure.
0
 
PePiAuthor Commented:
that makes sense.

my dbf files actually resides on a different server. the path is mapped to a particular drive. when using the mapped drive (e.g. F:\New\dbf Files\) gives the not valid path error. but when i use the server name (e.g. \\myServer\New\dbf Files\) it works just fine.

muchos gracias jd.
0
 
jdlambert1Commented:
Anytime, PePi!

When ya gonna post an EE profile?
0
 
PePiAuthor Commented:
soon. i still want to be a mystery. ;-p
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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