I am trying to create a linked server in SQL Server 2000 that will access a
whole bunch of DBase files.  I am then trying to call the table from a view.
In VB code the following was successful:
Dim rst As New ADODB.Recordset
rst.Open "Select * from stock", "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\path;Extended Properties=DBASE III;User ID=uid;Password=pwd",
adOpenDynamic, adLockBatchOptimistic

However, I need to create a linked server and that just does not want to
work.  Can someone please help me what I should enter in Enterprise Manager
in "Data source" and "provider string"
I tried the following that does not work:
Data source field = c:\path
Provider source field = Extended Properties=DBASE III
I also entered the security information on the next tab.
Still when I want to view the tables in Enterprise Manager it gives me this
error: OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBinitialize::Initialise
returned 0x80004005
The fun part is that the VB code works, but then why won't the Linked Server?

There's some more information over here:

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/37d016b3b0979d19/546309e2c2da0617%23546309e2c2da0617

Johnny Thawte
http://www.johnnysthoughts.com

Mav wrote:
Show quoteHide quote

For anyone that has gad to try and link DBase files, here is my experience -
hope this makes sense:
1
Enterprise manager error when trying to create a view of a linked server:
MSDTC on server 'servername' is unavailable .
Turn the MSDTC (Distributed Transaction Coordinator ) service on.
2
Linked server properties in Enterprise Manager:
The passwords have to be correct: Access uses the first password entry
(Local Login) whereas Enterprise Manager uses “For a login not defined in the
list above” because when using Enterprise Manager you are most likely logging
in as “sa” whereas Access is coming in using the local account.

3: Dbase files
Attempt 1a (Accessing DSN ODBC link – Using OPENROWSET):
SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver
(*.dbf)};DBQ=C:\path\','Select * from STOCK')
o    Works without DTC running
o    Error: Error converting data type DBTYPE_DBDATE to datetime

SELECT  TOP 100 PERCENT CODE FROM  OPENROWSET('MSDASQL', 'Driver={Microsoft
dBase Driver (*.dbf)};DBQ=C:\path\', 'Select * from STOCK') Rowset_1 ORDER BY
CODE

OPENROWSET
Includes all connection information necessary to access remote data from an
OLE DB data source. This method is an alternative to accessing tables in a
linked server and is a one-time, ad hoc method of connecting and accessing
remote data using OLE DB. The OPENROWSET function can be referenced in the
FROM clause of a query as though it is a table name. The OPENROWSET function
can also be referenced as the target table of an INSERT, UPDATE, or DELETE
statement, subject to the capabilities of the OLE DB provider. Although the
query may return multiple result sets, OPENROWSET returns only the first one.

Attempt 1b(Accessing DSN ODBC link – Using OPENROWSET):
SELECT * FROM OPENROWSET('MSDASQL', 'DSN=IQ', 'SELECT * FROM STOCK') Stock
o    Works without DTC running
o    Error: Error converting data type DBTYPE_DBDATE to datetime

Attempt 2 (Accessing DSN ODBC link – Using OPENQUERY):
    SELECT * FROM OPENQUERY(IQ, 'SELECT * FROM STOCK') Rowset_1
o    Works without DTC running
o    Error: Error converting data type DBTYPE_DBDATE to datetime

Attempt 3 (VBA):
This worked from within VBA (note the user ID and passwords = those of the
system.mdw file and NOT the Dbase filed):

    Sub test4()
Dim rst As New ADODB.Recordset
rst.Open "Select * from stock", "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\path;Extended Properties=DBASE III;User ID=pw;Password=pw",
adOpenDynamic, adLockBatchOptimistic
Debug.Print rst!Code
rst.Close
End Sub

This also worked:
rst.Open "Select * from stock.dbf", "….

Attempt 4a (Accessing Jet ODBC link – using OPENQUERY)
    SELECT     * FROM OPENQUERY(IQ3, 'SELECT * FROM STOCK') Rowset_1

Attempt 4a (Accessing Jet ODBC link – using OPENQUERY)
SELECT     *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\docfs\iq2000\001;Extended
Properties=DBASE III;User ID=user;Password=pw', 'Select * from STOCK') Stock


Mav

Ever growing transaction log, using simple recovery?
BEGIN WHILE EOF
EncryptByCert problem
The pains of XML programming
Simple trigger question