Timeout Expired with ADO connection 1

[vedicman]

I am getting a "Timeout Expired" error while running the following connection in an XL code module:

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=MSDASQL.1;User ID=sa; " & _
"Data Source=NetSupport"
cn.ConnectionTimeout = 0
cn.Open

......code to create the SQL string goes here

Set myRs = New ADODB.Recordset
With myRs
.ActiveConnection = cn
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open (strSQL)
End With

The error is thrown on the .Open (strSQL) line.
The Server is obviously ignoring the ConnectionTimeout setting. Does anyone have a solution.

FYI: the query runs OK in the SQL Query Analyzer

Thanks.....Franco

 

[Glasgow]

I think perhaps you should be using a non-zero ConnectionTimeOut and then use CommandTimeout for the SQL Command.
Though I'm not sure I understand when you say:

>The Server is obviously ignoring the ConnectionTimeout setting.

 

[Glasgow]

Oh sorry - zero means indefinite wait. So maybe it is connecting ok but timing out on the SQL - hence perhaps use a Command object to execute the SQL and set CommandTimeout property to zero.

 

[Glasgow]

Something along these lines perhaps:

Dim SQLCommand As New ADODB.Command
Dim rsDat As ADODB.Recordset
Dim SqlCmdTxt as string
SqlCmdTxt="SELECT...."
With SQLCommand
.CommandType = adCmdText
.CommandText = SqlCmdTxt
.ActiveConnection = cn
.CommandTimeout = 0
Set rsDat = .Execute(RecsFound)
End With

 

[vedicman]

Thanks Glasgow

Yeah, it's connecting OK but the query is not finishing. (It takes about 3 minutes to run because the tables on the SQL server are not properly indexed and normalized..I have no control of server tables). Later in my code, I need to paste the results of the query into the spreadsheet, which is why I am using a recordset object. If you know how to do that with a command object please show me a code snippet.

Thanks......Franco

 

[Glasgow]

We may have cross-posted but my previous post shows the general approach and uses a recordset. I think it should work!

 

[Glasgow]

I really must be half asleep. I pasted code and modified to reflect your scenario.

.Execute should be enough - you don't need the RecsFound parameter.

 

[vedicman]

Glasgow

You are more awake than you think you are, but you must be a bit psychic. You posted your code example WHILE I was requesting you to post it. With the minor modification of using my already declared variables instead of yours, the code runs fine...no timeout error. I then tried my old code and got the timeout error again, so it appears that your code really did solve the problem. You earned a star!

Thanks......Franco

 

[Glasgow]

Thanks. The crystal ball and tarot cards come in handy sometimes - every programmer should have them.

 

[Alkemac]

Just along similar lines, I've got a stored proc sitting in SQL svr 2000 and when I run it from my VB6 code, it pulls 365 records from the tables and then hits the timeout expired error. Anyway I can get around this as I am not using a recordset where I can set the timeout ?

Thanks

 

[mattKnight]

are you using a command object?

You can use a command object without returning a recordset...

You may get more help if you post some code!

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[Alkemac]

Will this help ?

Option Explicit

'Connections
Public PCis As New ADODB.Connection

Private Command1.click
dim SQL as string

SQL = " Exec PCis.dbo.portcis_getbdastatement_statementsdb '200312'"
'StatementHistory.Execute SQL
PCis.Execute SQL
end sub


Private Sub Form_Load()

Set Form1.PCis = Nothing
PCis.Open "Provider=SQLOLEDB.1;Server=hqcsql02\hqcsql02;" & "Database=Portcis;Trusted_Connection=Yes"

End Sub

 

[mattKnight]

Look at Glasgows code above

  Dim SQLCommand As New ADODB.Command
  Dim SqlCmdTxt as string

  SqlCmdTxt="Exec PCis.dbo.portcis_getbdastatement_statementsdb '200312'"
  With SQLCommand
    .CommandType = adCmdText
    .CommandText = SqlCmdTxt
    .ActiveConnection =  PCis
    .CommandTimeout = 0
    .Execute
  End With
'tidy up 
set sqlCommand = nothing

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[Alkemac]

Thanks Dr....

 

[cjac]

It may also be worth noting that many ODBC drivers hold a enable/disable connection timeout property. I'm not sure if the code would overide this if your ODBC driver WAS set to enable timeout.