Solved

Timeout Expired

Posted on 2001-07-27
14
1,367 Views
Last Modified: 2013-12-25
I'm using an ODBC connection to SQL Server.
I make querys that return sums from the database normaly those querys don't have more than 5.000 rows.
My App Works fine up to 5.000.000 millions records in the database.
My problem is that when the records increase i get the error timeout expired.
This is what i have done to try solve the problem:
1st:I configured the pooling database to don't pool.
2nd:In the SQL server i've tryied and change the property query timeout in the connection tab from 600 to 0 but when i run the server taht property is still in the same value, i have an option that says running values but when i click it everything becomes enabled false.

PS1:I'm using SQL Server 2000

PS2:50 Points bonus for quick answer

PS3:As fallow up to this question i woul'd like to know what configurations shoul'd i change in the sql server, to optimize the work, i only do select querys, i never change values. This is obviously another question so is extra 75 points to whoever answer it.


THANKS
0
Comment
Question by:ElvasLion
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6327392
Use the Query Analyzer. Copy your query there. From the Query menu, select "Show execution plan". Now run your query. When the execution is done, have a look at the plan generated, search for the highest percentage and see if you can add indexes to help the SQL parser.
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6327581
what is your sql statement?
0
 
LVL 1

Author Comment

by:ElvasLion
ID: 6327627
doesn't matter the sql statement the problem is the timeout, i reajusted the query timeout to 0 and it works fine in sql now but from vb i still get the timeout message.
0
Industry Leaders: 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!

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6327662
What about the Execution plan?
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6327908
the sql matters very much.  it it isn't good it will run very slow.
0
 

Expert Comment

by:JeffGrigg
ID: 6329052
Use SQLSetConnectAttr to set SQL_ATTR_CONNECTION_TIMEOUT.  If zero doesn't work, try a very large value.

See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlsetconnectattr.asp
0
 
LVL 1

Author Comment

by:ElvasLion
ID: 6333839
My sql is an normal sql statement that selects a few sums from a table like this:

SELECT SUM(Field1), AS Xpto1, SUM(Field2) AS Xpto2, Field3 FROM MyTable GROUP BY Field3

As you can see this is not the problem, the problem is the table that has up to 12.000.000 Records.
I'm going to explain it again:

First i set the query timeout in the sql server to 0 and then i've chosen don't pool in the connection pooling from the odbc. This way i managed to do querys from within the sql server and from linked tables in access. My problem is in my VB app because it works fine up to 5.000.000 recors, and if i pass that limit it just gives me ODBC Error - Timeout Expired.


Please JeffGrigg could you give me an example of hoe i shoul'd use that API.

THANKS
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6334056
Do you have an index on Field3?

Can't you have a Where clause to minimize the number of lines retreived?
0
 
LVL 1

Author Comment

by:ElvasLion
ID: 6337352
I think i've already posted this yesterday don't know why it isn't here.

I've solved the problem, the problem was in the connection i've used:
 
set conn= new adodb.connection
conn.connectionstring="Dsn=MyDsn"
conn.commandtimeout=0
conn.open

As you can see the sql and the indexes are irrelevant what i wanted was being able to do a select that can take all the time needed.

Thank you all but i'm going to delete the question in 2 days if nobody opposes.

Thank you all again
0
 

Expert Comment

by:JeffGrigg
ID: 6340383
Sure; go ahead and delete this; none of these comments really answers your question.
_ _ _

Hmmm...  Interesting:
Your question about my SQLSetConnectAttr posting is also missing.  I *KNOW* it was here before.
_ _ _

You said you were using ODBC.  Now you're using ADO.  No wonder my "SQLSetConnectAttr" comment was puzzling.
0
 
LVL 1

Author Comment

by:ElvasLion
ID: 6344439
i'm using ado but the connection is made trough odbc.
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6344624
don't delete it post a question in community support and ask them to make it a PAQ and refund your points.  Your solution can benefit other users.
0
 
LVL 1

Author Comment

by:ElvasLion
ID: 6366337
Ok jrspano i'm going to follow your advise thank you very much.
0
 
LVL 1

Accepted Solution

by:
Moondancer earned 0 total points
ID: 6366531
I have refunded your points and will now move this to our PAQ.

Moondancer
Community Support Moderator @ Experts Exchange
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

685 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