timeout error on an open for a SQL table

[needhelppls]

i am converting ACCESS 2000 to SQL 2000. When i open a large table, i get a timeout error. i ran the select in SQL Query Anal. and it works fine. How can i change the amount of time to open a table before it times out? my open statement is

rsDetailRecord.Open strDetailSelect, _
ActiveConnection:=conSQLDir, _
CursorType:=adOpenStatic, _
LockType:=adLockOptimistic

 

[TysonLPrice]

When you create conSQLDir use:

.ConnectionTimeout = 0

You can also try the Private Cmd As New ADODB.Command structure.

With Cmd
.CommandType = adCmdText
.CommandText = sSql
.ActiveConnection = conn
.CommandTimeout = 0
Set rs = .Execute
End With

If you are exceeding the system default you are SOL.

 

[needhelppls]

Thanks, there is so much to know. I don't have anyone at work to ask. We are a small company, and i am by myself. I'm glad i found this site. Thanks again.

 

[gmmastros]

While you're at it.... I would encourage you to stop bringing back all the data from a table. With SQL 2000 (and up) there are ways to get smaller bits of data. That way you are only returning the information you want the user to see.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ArtieChoke]

The ConnectionTimeout property is ONLY for the connection to the database. It will not affect a query.

The CommandTimeout property is within ADO only.

If you are running into a server timeout, neither of these will have any affect.

So unless you need all the data in the recordset, limit what your query returns.

Also, I've found that some queries will work fine in QA, but they flounder in ADO. Is this a single table query or are there more tables involved?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[TysonLPrice]

I don't know as much as the gurus here but if it is possible create a stored procedure, limit what your bringing back as mentioned, and add SET NOCOUNT ON to the top of the SP.

I may be all wet but I think SQL won't have to compile it that way when you run. Also check and see if you have indexes on what's driving the select.