Solved

Timeout Expired

Posted on 2001-07-27
14
1,352 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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
what is your sql statement?
0
 
LVL 1

Author Comment

by:ElvasLion
Comment Utility
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
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
What about the Execution plan?
0
 
LVL 3

Expert Comment

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

Expert Comment

by:JeffGrigg
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
i'm using ado but the connection is made trough odbc.
0
 
LVL 3

Expert Comment

by:jrspano
Comment Utility
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
Comment Utility
Ok jrspano i'm going to follow your advise thank you very much.
0
 
LVL 1

Accepted Solution

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

Moondancer
Community Support Moderator @ Experts Exchange
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now