VB Runtime error 2147467259(80004005) time out 1

[lilboi]

Hey guys,

I've written a program to do queries on SQL (on another server) but randomly freezes when it gets this runtime error 2147467259(80004005) time-out on accessing SQL server.

What is that? What causes that? It's so random when it happens.

Any help will make my wrinkles temporarily disappear!

 

[ArtieChoke]

It's a time-out issue on the server. Try to use key fields in the where clause or restructure your query. I've had queries that completed in seconds in QA but in ADO they would never finish - very weird.

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

 

[dglienna]

Windows Updates or AV Software would be the first thing to check.

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[TysonLPrice]

I don't know how you constructed your connection and call but two parameters requesting the maximum connection time set up by the DBAs are:

.ConnectionTimeout = 0
.CommandTimeout = 0

 

[ArtieChoke]

ConnectionTimeout is ONLY for getting the connection, it has nothing to do with the query timeout. There might be a SQL command to change the timeout, but I don't recall it offhand.

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

 

[lilboi]

Thank you so much every1!

It occurs on the line.

objConn.open connString

So i've added the line

objConn.ConnectionTimeout = 60

Hopefully that'll solve it. If not, I will try to disable the Norton Antivirus on the SQL server.

 

[TysonLPrice]

ArtieChoke,

Isn't that what the ".CommandTimeout = 0" I posted is for?

 

[lilboi]

What i'm stumped about in this one is.... the time out is suppose to occur when the server's experiencing high traffic right?

But how can there be a high traffic when there's only like 4 people or less running queries on the SQL server?

Could it be a coding problem?

 

[ArtieChoke]

Isn't that what the ".CommandTimeout = 0" I posted is for?

That only works IF you use a command object, not on recordsets.

lilboi, please post your query and the code you are using to run it.

thx.

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

 

[ArtieChoke]

And the command timeout is only from the program's viewpoint - the server can still time out...

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

 

[TysonLPrice]

That only works IF you use a command object, not on recordsets.

ArtieChoke,

This uses:

.ConnectionTimeout = 0
.CommandTimeout = 0

Requesting the maximum default timeout set by the DBAS building a recordset. Correct me if I'm wrong (barring typos

Private CMIConnect As New ADODB.Connection
Private Cmd As New ADODB.Command
Private rsTempRecordSet As New ADODB.Recordset
Private strSQL as string

CMIConnect = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=SSPI;OLE DB Services = -1"
cnRegister.ConnectionString = CMIConnect
cnRegister.ConnectionTimeout = 0
cnRegister.CursorLocation = adUseClient
cnRegister.Open

strSQL = “Select top 1 * from MyTable”

With Cmd
.CommandType = adCmdText
.CommandText = strSql
.ActiveConnection = CMIConnect
.CommandTimeout = 0
Set rsTempRecordSet = .Execute
End With


If not rsTempRecordSet.eof
debug.Print rsTemprecordSet(0)
end if

 

[ArtieChoke]

You don't have to use a command object to create a recordset - I only use them for running stored procedures. IF the timeout is due to ADO and not the server, then this setting will wait forever until the server returns. If the server never returns, the program will hang.

Neither of these timeouts do anything at the server.

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

 

[BobRodes]

<Private CMIConnect As New ADODB.Connection, etc.

See faq222-6008 for reasons not to use this syntax.

Bob

 

[TysonLPrice]

I not trying to belabor the subject. I was only responding to:

ConnectionTimeout is ONLY for getting the connection, it has nothing to do with the query timeout. There might be a command to change the timeout, but I don't recall it offhand.

Which I posted originally and:

That only works IF you use a command object, not on recordsets.

Which it does. I’m not saying it is the best way. I do believe it works to extend the default setting.

 

[ArtieChoke]

Mo' belaborin':

I don't know how you constructed your connection and call but two parameters requesting the maximum connection time set up by the DBAs

Those settings are within ADO only, not at the server or my ADO book is lying!

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

 

[TysonLPrice]

Mo' belaborin':

I think you are right...

http://msdn2.microsoft.com/en-us/library/ms678265.aspx