Solved

sp_addlinkedserver provider/connection string problem

Posted on 2004-09-13
10
9,089 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
  • 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
 
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 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now