11

Using SQL Server 2005 and VB6

When I executing for yearly data or more than 3 months' data, it is showing "Timeout Expired" error. It is not executing completely.

My Connection String

ConnectionString = "Provider=SQLOLEDB.1;" & _
    "Persist Security Info=False; " & _
    "User ID=" & Settings.SQL_Username & _
    "; Password = " & Settings.SQL_Password & "; " & _
    "Initial Catalog=" & Settings.SQL_DatabaseName & ";" & _
    "Data Source=" & Settings.SQL_ServerAddress

How do I solve this problem?

Plz...

Dinah
  • 52,922
  • 30
  • 133
  • 149
Gopal
  • 11,712
  • 52
  • 154
  • 229
  • Can you show the query you are running? Often times there are simple changes that can be made to a query that improves performance. Obviously, if you make the query faster, you won't need to worry about timeouts. – George Mastros Oct 14 '09 at 16:00

4 Answers4

18

There's no "black voodoo magic" out there - either you can make your query go faster (return less data, improve the database design, find and apply indices that make your queries execute faster), or then increase the timeout you allow the query to run before a timeout is thrown.

Those are your two options - take your pick.

UPDATE: a little googling reveals:

Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandTimeout = 120   ' number of seconds

Marc

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • In ConnectionString Itself i have to give the timeout or select from the database. Where I have to mentioned the timeout. Plz – Gopal Oct 13 '09 at 15:19
  • 2
    You *cannot* solve this in your connection string. Work on fixing the query (optimizing) and take the above advice and add a CommandTimeout value. – marcc Oct 13 '09 at 16:12
3

You have to set .CommandTimeout on the command. It doesn't work if you set it in the connection string.

Dinah
  • 52,922
  • 30
  • 133
  • 149
1

I would guess you're either trying to pull back a lot of data and it's taking SQL Server more than the default ADO timeout (either 30 or 40 seconds?) to pull that data back.

Or it's not really a lot of data, but you've not got a decent index on the table - so I'd check the indexes against your query (execution plan is your friend here).

Or it's a mix of both.

How long does the query take if you run it direct in SQL Management Studio?

Chris J
  • 30,688
  • 6
  • 69
  • 111
0

Obviously you are trying to bring up a lot of data, and your database is taking a lot of time doing this. Post your connection string so I can tell you what parameter to change so you can increase the connection time out.

Or you can try to optimize your app, it should take that long to bring up data.

Hector Minaya
  • 1,695
  • 3
  • 25
  • 45
  • I Posted My connection String. – Gopal Oct 13 '09 at 15:27
  • 3
    the connection timeout is unlikely to be the problem - the connection seems to be in place, but the **command** issued then times out. You need to set the `.CommandTimeout` property - and that's not on the connection string – marc_s Oct 13 '09 at 16:08