Time Out

Question

Time Out

er sql

SSC Eights!

Points: 943
More actions
January 6, 2005 at 4:00 pm

#164116

Hi, I'm running a Visual Basic 6 Application that connects with my SQL Server 2000. The application makes a select statement that builds an account report, the query is running well in Query Analyzer tool but in the application environment fails, a error message appears "Time Out Expired".
How can I see database tiem out value? and how can I set a new value for it?
Thanks a lot.

Sincerely ER_SQL

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 1

The time out value you want to set is on your ADO connection / command object. You can set it as a property of the connection or command object as:

objMyConnection.CommandTimeout = 90 ' 90 seconds

If you know how long the report takes to run in Query Analyzer, you should set the CommandTimeout to being longer.

Julian Kuiters
juliankuiters.id.au

techstuff SSC Journeyman Points: 80 More actions · Answer 2

set

objMyConnection.CommandTimeout =0 for an infinite timeout.

Try to run the query on the server and see why is it taking such a long time to execute.

Steve Adams Mr or Mrs. 500 Points: 520 More actions · Answer 3

I have had time out nightmares in the past. What I normally have done to resolve them is to tweak my query and add or adjust indexes on the tables involved. With these methods I have been ablt to eliminate the time out problems. Run your qury in query analyzer and put it through the Index Tuning Wizard to determine if indexes are needed. 90% of the time it set you in the right direction. There is also a handy little setting you can turn on full time or just use it when you need it. SET STATISTICS IO ON. This will tell you which tables are bogging down your query by the number of logical reads on a table. The higher it is the longer it will take. Usually by adding some indexes you can get this number down and better the performance of your query. However, adding too many indexes may slow down performance and you have to keep in mind other large queries that may be hitting these tables as well.

rplcmint Grasshopper Points: 18 More actions · Answer 4

I always found that:

conn.commandtimeout = 0

works a lot better than

conn.connectiontimeout = 0

I think there is a bug in Visual Basic concerning this.

er sql SSC Eights! Points: 943 More actions · Answer 5

Ok, thanks. Finally it's working. The problem was with an .dll in VB that had a bad time out value. The solution was to program a nex conecction.

Thanks a lot everyone.

Sincerely ER_SQL