Timeout - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Timeout

Author

Topic

lbrett
Starting Member

2 Posts

Posted - 2011-08-18 : 05:50:50

Hi,
for what reason can I get a "Timeout Expired" error back from an SQL command to a database other than trying to pull too much data from the data base - let me explain.

Using a VB6 executable, to pull data from an sql2005 database (default instance).

I am trying to pull circa 288 records(1 days data) from a database which sits on the local machine, this used to work fine pulling 30 days worth of data across a network. I am consistently getting this error, and I have extended the connection.commandtimeout property to as much as 500 seconds.

So in short is there any other reason for getting the "Timeout Expired" error e.g. database corruption... ??

Any help would be much appreciated.

Liam

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-08-18 : 07:22:49

This is usually because of some kind of locking/deadlocking in the database cased by poor/non-existing indexes. Post the query in question and we'll be able to give you some proper advice.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

lbrett
Starting Member

2 Posts

Posted - 2011-08-18 : 07:35:11

quote:
Originally posted by Lumbago

This is usually because of some kind of locking/deadlocking in the database cased by poor/non-existing indexes. Post the query in question and we'll be able to give you some proper advice.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Thanks Lumbago,

below is the query:

SELECT Ldate, MPValue,(MPValue*100) As litres FROM dbo.trend WHERE ldate BETWEEN '7/28/2011 00:00:00' And '7/28/2011 23:59:59' And rtuno = 227 AND MP = 10 ORDER BY Ldate

Liam

curtis.sujata
Starting Member

3 Posts

Posted - 2011-08-18 : 09:37:10

Public cn As ADODB.Connection
Public rs As ADODB.Recordset
Public Sub opentable(ByVal str As String)
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "sqloledb"
cn.ConnectionTimeout = 45
cn.Open "Driver=SQL Server;Server=" & Trim(DBServer) & ";Database=" &
Trim(DBpath) & ";uid=user1;pwd=abc;"

rs.ActiveConnection = cn
cn.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open str, cn
End Sub

'Form COde
dim obj as new data1
obj.opentable "select * from party_master_tbl"
msgbox obj.rs.fields!party_name

sujata curtis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources