Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sp_addlinkedserver provider/connection string problem

Posted on 2004-09-13
10
Medium Priority
?
9,231 Views
Last Modified: 2013-11-24
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
Comment
Question by:PePi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 6

Author Comment

by:PePi
ID: 12049499
additional note:
   I cannot use DTS in importing the dbf files. Thanks again!
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12049898
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
 
LVL 2

Expert Comment

by:DaveHavard
ID: 12052869
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:PePi
ID: 12067139
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
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 2000 total points
ID: 12068022
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
 
LVL 6

Author Comment

by:PePi
ID: 12068124
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12068192
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
 
LVL 6

Author Comment

by:PePi
ID: 12069192
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12069299
Anytime, PePi!

When ya gonna post an EE profile?
0
 
LVL 6

Author Comment

by:PePi
ID: 12070033
soon. i still want to be a mystery. ;-p
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question