Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

opendatasource and linked server

Posted on 2006-03-22
17
Medium Priority
?
944 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:itatahh
  • 9
  • 7
16 Comments
 
LVL 20

Expert Comment

by:Sirees
ID: 16262616
>.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
 
LVL 20

Expert Comment

by:Sirees
ID: 16262651
0
 
LVL 20

Expert Comment

by:Sirees
ID: 16262673
To query the tables after the linked server is set up you should do as

select * from linkedservername.databasename..tablename
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:itatahh
ID: 16262773
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
 
LVL 20

Expert Comment

by:Sirees
ID: 16262831
>>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
 

Author Comment

by:itatahh
ID: 16262850
I thought the VestaDBF was the Linked Server?
0
 
LVL 20

Expert Comment

by:Sirees
ID: 16262940
If it is then and COUNTY is your table name

try this

Select * from VestaDBF...COUNTY
0
 

Author Comment

by:itatahh
ID: 16263003
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
 
LVL 20

Expert Comment

by:Sirees
ID: 16263032
Can you do

Select Count(*)  from VestaDBF...COUNTY

and see the number of rows.
0
 

Author Comment

by:itatahh
ID: 16263116
No result yet.  It shouldn't take longer than the opendatasource, should it?
0
 
LVL 20

Expert Comment

by:Sirees
ID: 16268643
>> It shouldn't take longer than the opendatasource, should it? <<
No.
0
 

Author Comment

by:itatahh
ID: 16269125
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
 
LVL 20

Expert Comment

by:Sirees
ID: 16271610
>>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
 
LVL 20

Accepted Solution

by:
Sirees earned 1500 total points
ID: 16271628
0
 

Author Comment

by:itatahh
ID: 16271824
The connection timeout and Query timeout are both zeros (0).

I'll check out the link.
0
 

Author Comment

by:itatahh
ID: 16301954
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 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