opendatasource and linked server

>.I can see the tables under the linked server but I am unable to query them. <<

Are you able to create linked server and can't query tables?

setup linked server

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp

To query the tables after the linked server is set up you should do as

select * from linkedservername.databasename..tablename

Correct.  If I try 'Select * From VestaDBF."V:\DATA\AHH"..COUNTY'  it does not give me data.  It times out.  However, with the OpenDataSource line, it gives me the county list in less than 3 seconds.

>>If I try 'Select * From VestaDBF."V:\DATA\AHH"..COUNTY'  it does not give me data.  <<

To do this you should create a linked server. You cannot directly query from the dbf file.

>>However, with the OpenDataSource line, it gives me the county list in less than 3 seconds.<<

OpenDataSource does not need a linked server.

I thought the VestaDBF was the Linked Server?

If it is then and COUNTY is your table name

try this

Select * from VestaDBF...COUNTY

I tried that and it timesd out also.

Looking at the Linked Server, I see the table name, schema column is null, catalog=v:\ahh\data, and last column = user

The current linked server I created was using the microsoft link you gave me earlier.

Can you do

Select Count(*)  from VestaDBF...COUNTY

and see the number of rows.

No result yet.  It shouldn't take longer than the opendatasource, should it?

>> It shouldn't take longer than the opendatasource, should it? <<
No.

If I can see the list of tables under the linked server, that should enable me to do the Select syntax right?  Also, since I can do an opendatasource (that points to the same directory used in the linked server data source), it shows that I have access to the data files.

Any further things I should check?

Can you use opendatasource as part of your query?  Can you open multiple data sources in one query and perform the joins while using the opendatasource function?  If so, maybe I'll just create all of my views using this method that works so far.

>>If I can see the list of tables under the linked server, that should enable me to do the Select syntax right? <<
yes

>>Any further things I should check? <<

Right clik on your linked server go to properties ->server options and see the values of connection timeout and Query timeout.

The connection timeout and Query timeout are both zeros (0).

I'll check out the link.

Her is an update:

1.  I have created a View for each of the tables I need to access.
2.  I then created Views that reference the views above.  This seems to work with only a couple of issues:
     A.  With the syntax below, everytime it runs each of the Views in item 1 it references all of the tables in the sub-directory used int he syntax.  This takes up a whole lot of time.
     B.  When it runs on the Query Analyzer it gives me the right data.  When it runs in Access (which I am using for a test) it does not match the data the View presented in QA.  It also gives me an error when I attempt to go to the last record.

The question(s) I have are:

1.  How can I adjust the "OpenDataSource" syntax/parameters to not have it open all of the tables in the "V:\DATA\AHH" sub-directory?  (This should improve the response time - hopefully.)

2.  How come the results in the QA run of the View gives me the correct results, but the external access to the View does not?

3.  The error it gives me indicates that the index can not do a scan.  However, it does not tell me this in the QA run on the server.

Here is the syntax used to create each "OpenDataSource" view and the syntax for the joins:

---------------------------------------------------------------------------------------------------------
CREATE VIEW dbo.VestaDetail
AS

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...DETAIL

GO


---------------------------------------------------------------------------------------------------------
CREATE VIEW dbo.VestaClients
AS

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...CLIENTS

GO

---------------------------------------------------------------------------------------------------------
CREATE VIEW dbo.VestaHeader
AS

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...HEADER

GO

---

---------------------------------------------------------------------------------------------------------
CREATE VIEW dbo.VestaAR4VI1
AS

SELECT  A.[KEY] AS BATCH, A.BILLKEYDET AS VINVNO, B.PROVADDRES AS DIVISION, CAST(A.CLIENT_NBR AS DECIMAL(9,0)) AS CLIENT_NBR,
CONVERT(DATETIME,C.CLM_DATE,102) AS INV_DATE, A.HCPCS,
CAST(A.UNITS AS DECIMAL(7,2)) AS UNITS,
CAST(A.RATE AS DECIMAL(7,2)) AS RATE,
CAST(A.AMOUNT AS DECIMAL(14,2)) AS AMOUNT
FROM dbo.VestaDetail AS A
INNER JOIN
dbo.VestaClients AS B
ON A.CLIENT_NBR = B.CLNTNUMBER
INNER JOIN
dbo.VestaHeader AS C
ON A.[KEY] = C.[KEY]

GO