Solved

sp_addlinkedserver provider/connection string problem

Posted on 2004-09-13
10
9,066 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
Comment Utility
additional note:
   I cannot use DTS in importing the dbf files. Thanks again!
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 6

Author Comment

by:PePi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Anytime, PePi!

When ya gonna post an EE profile?
0
 
LVL 6

Author Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

8 Experts available now in Live!

Get 1:1 Help Now