opendatasource and linked server

I ran this against my dbf files and it showed me the data.  However I still cannot seem to create a linked server I can successfully query.

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

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

I cannot get a linked server established with 'Microsoft.Jet.OLEDB.4.0' but only the Microsoft ODBC one.  As I said above. I can see the tables under the ODBC but can only see the data on the opendatasource pointing to the Jet.
itatahhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SireesCommented:
>.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?
0
SireesCommented:
To query the tables after the linked server is set up you should do as

select * from linkedservername.databasename..tablename
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

itatahhAuthor Commented:
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.
0
SireesCommented:
>>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.
0
itatahhAuthor Commented:
I thought the VestaDBF was the Linked Server?
0
SireesCommented:
If it is then and COUNTY is your table name

try this

Select * from VestaDBF...COUNTY
0
itatahhAuthor Commented:
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.
0
SireesCommented:
Can you do

Select Count(*)  from VestaDBF...COUNTY

and see the number of rows.
0
itatahhAuthor Commented:
No result yet.  It shouldn't take longer than the opendatasource, should it?
0
SireesCommented:
>> It shouldn't take longer than the opendatasource, should it? <<
No.
0
itatahhAuthor Commented:
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.
0
SireesCommented:
>>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.

0
SireesCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itatahhAuthor Commented:
The connection timeout and Query timeout are both zeros (0).

I'll check out the link.
0
itatahhAuthor Commented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.