?
Solved

Setting up a linked server to dBase 5 database in SQL Server 2000

Posted on 2003-03-14
4
Medium Priority
?
342 Views
Last Modified: 2013-11-24
I've hit a wall trying to establish a linked server to a dBase database from within SQL Server 2000. I have tried all kinds of combinations in the connection properties. I've tried OLE DB and using a system DSN (which works if I try to bring the data into Excel!)

SQL Server lets me create the linked server and I'll see it in the list, but if I try to see any of the tables I get the following error.

OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:  
Error: 7399, Severity: 16, State: 1

Doing a Google search, I see lots of folks trying to do the same thing, being unsucessful, asking help and getting no responses. It has to be possible. I can connnect using the System DSN and Excel as well as in DTS with the standard dBase 5 connection.

I'm really under the gun on this one and will bump up the points if someone can get me an answer that works quickly.

Thanks

David
0
Comment
Question by:David Little
[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
  • 2
  • 2
4 Comments
 
LVL 4

Expert Comment

by:mcmahon_s
ID: 8142024
Try this:

1) Create an ODBC connection (TestDbase5) to the directory where you have the dbase files stored (in my case E:\data\test)

2) Add your linked server
sp_addlinkedserver @server='dbase5', @srvproduct='db5', @provider='MSDASQL',@datasrc='TestDbase5'

note: the value for srvproduct doesn't matter

3) Then to access a table named address
SELECT * FROM dbase5.[E:\DATA\TEST]..ADDRESS

The [E:\DATA\TEST] is the catalog name that is assigned which you can see by executing:  sp_catalogs dbase5

This works for me.
0
 
LVL 2

Author Comment

by:David Little
ID: 8142751
I tried this command ...

exec sp_addlinkedserver
  @server='dbase5',
  @srvproduct='db5',
  @provider='MSDASQL',
  @datasrc='Cougar'

"Cougar" is the DSN I have set up and tested for connectivity with Excel. Using the DSN, I can import data into Excel from my dBase tables.

The linked server is created (I can see it in EM) but if I try to view a list of tables, I get the following error message ...

Error 7399: OLE DB provider 'MSDASQL' reported an error. Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

Executing "sp_catalogs dbase5" within Query Analyzer gives me a similar error message ...

Server: Msg 7399, Level 16, State 1, Procedure sp_catalogs, Line 4
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].

Could this be a vestage of the tighter security in SP3? I know a few other things that got "flaky" after we applied the latest service pack.

David

I'm running SP3 on both the server and workstation and have tried it on multiple clients so it seems to be a server issue.
0
 
LVL 4

Accepted Solution

by:
mcmahon_s earned 1000 total points
ID: 8145742
Did you create the DSN on the SQL Server machine as a system DSN?
0
 
LVL 2

Author Comment

by:David Little
ID: 8542540
I'm a little red faced to admit that it was this simple. Live and learn I guess!

Thanks
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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