Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Increase Timeout for a View?

Posted on 2003-11-04
16
Medium Priority
?
305 Views
Last Modified: 2012-06-21
Access Client, SQL Server backend.

In the Access client I am linking to a view on SQL Server.

The view is taking a long time to process, and eventually times out.

The same view takes a long time to return (in SQL Server Query Analyzer) but finishes.

I am looking at 2 options:

1)  Wrap a Pass Through Query around the View and use the Pass Through Query ODBC Timeout property to help with the timeout issue.

2)  Increase the timout for the View in the Access client (Is this possible?  I didn't see a "Timeout" property for a View like I did for a Pass Through query)
0
Comment
Question by:Tom Knowlton
[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
  • 9
  • 7
16 Comments
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9683308
I'm guessing the "view" comes from a query of some sort in Access, and you can increase the timeout in the design view of the query properties. If you set the timeout to 0, no timeout error occurs.

You may want to see what else is happening in the query/sql behind the scenews of the view, if it continues to be slow
Karen
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9683309
I'm guessing the "view" comes from a query of some sort in Access, and you can increase the timeout in the design view of the query properties. If you set the timeout to 0, no timeout error occurs.

You may want to see what else is happening in the query/sql behind the scenews of the view, if it continues to be slow
Karen
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 9683334
"View" in this case is a special read-only recordset available in SQL Server

I see the "View" I have to link to it via ODBC or some other means.  The code behind the View resides on SQL Server.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9683353
Sorry to be so dense, but with Access as the client, how are you accessing the view? Is it listed with the table objects?
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 9683368
Correct.

The ICON for the link to the SQL Server View looks different....it is a globe icon.

When I go into the Design View for the SQL Server View object, and righ-click on the Title Bar and go to Properties....there seems to be no "ODBC Timeout" property (like you would see for a SQL Server Pass Through Query object)
0
 
LVL 18

Accepted Solution

by:
Karen Falandays earned 2000 total points
ID: 9683383
OK, that's good. It's a linked table to Access
Now try this:
Create a query based on the view, add all of the fields and change the properties to see the odbc timeout to 0. Will that work?
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 9683428
RE:  setting the timeout to 0...yes I will try that in a minute.

As an example:

SELECT Count(homebuyerThanksView.EntryID) AS HBCount FROM homebuyerThanksView;

Gives a timeout error in Access.

If I run the SAME thing in SQL Server's Query Analyzer, it will finish, but takes a long time.

Hope this helps,

Tom
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9683461
Oh I see what you are doing. I have to think about that some more.

On another note, are you friendly with the dba who owns/maintains the data? Sounds like they need to add an index here or there to help speed things up. THat almost always does the trick.
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 9683469
The DBA has admitted that the problem is on his end.

As a temporary work-around I am trying to increase the timeout duration so the program can still FUNCTION, albeit very very slowly.
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9683503
OK, good luck with that. In the meanwhile, in the main screen of Access, try to go to Tools, Options, Advanced and manipulate some of these settings..it may help!
Karen

0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 9683514
Thanks, Karen.

Tom
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9683524
Anytime, and let me know if any of the other things worked!
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 9683548
As a matter of fact, setting the ODBC Timeout to 0 worked for the 3 queries I'm using for counting the number of records.

Looks like you'll be getting partial if not full credit for this question.

I need a few more minutes to test....and then I'll be back here to award your points!

:)

Thanks,

Tom
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9683572
Oh very cool. I know how frustrating that can be! Hope the dba can get those indexes for you too, that will REALLY speed them up! I had views accessing 6 million records, in a multiuser that was dragging until he re-indexed correctly!
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 9683889
Your advice allowed me to workaround the problem.

The points are yours, with my sincere gratitude!

Tom
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 9683908
Wowee! I'm so glad for you, and I love the points!
Karen
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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