ADO timeout 1

[sedgely]

i have the following code to fire a SQL sp and bring the data into excel.
This was working fine yesterday, but now i am getting a "timeout" error, i know there is a timeout setting that i can set to 0 but not sure of the syntax or where to put it. can anyone point me in the right direction?

Sub ExtractData()
'DECLARE VARIABLE TO HOLD SQL
Dim strSQL As String
Dim strSPNAME As String
Dim strDATE1 As String
Dim strDATE2 As String

strSPNAME = Sheet2.Range("b1").Value
strDATE1 = Sheet2.Range("b2").Value
strDATE2 = Sheet2.Range("b3").Value

'SET strSQL
strSQL = "SET DATEFORMAT DMY EXEC "
strSQL = strSQL & strSPNAME
strSQL = strSQL & " '" & strDATE1 & "'" & "," & "'" & strDATE2 & "'"

' Create a connection object.
Dim cnAnE As ADODB.Connection
Set cnAnE = New ADODB.Connection

' Create a recordset object.
Dim rsAnE As ADODB.Recordset
Set rsAnE = New ADODB.Recordset

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the ane database on SYAS2K08.
strConn = strConn & "Server=svr; Database=db; uid=id; ;pwd=pwd;"


'Now open the connection.
cnAnE.Open strConn


'Return data
With rsAnE
    ' Assign the Connection object.
    .ActiveConnection = cnAnE
    ' Extract the required records.
    [COLOR=red yellow][b][i].Open strSQL[/i][/b]  'timeout here!![/color]
    ' Copy the records into cell A1 on Sheet1.
    Sheet2.Range(Sheet2.Range("b4").Value).CopyFromRecordset rsAnE
    
    ' Tidy up
    .Close
End With

cnAnE.Close
Set rsAnE = Nothing
Set cnAnE = Nothing

End Sub

Cheers, Craig
Si fractum non sit, noli id reficere

 

[PHV]

Have a look at the CommandTimeout property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sedgely]

Thanks PH
Thats got it!

Sub ExtractData()
'DECLARE VARIABLE TO HOLD SQL
Dim strSQL As String
Dim strSPNAME As String
Dim strDATE1 As String
Dim strDATE2 As String

strSPNAME = Sheet2.Range("b1").Value
strDATE1 = Sheet2.Range("b2").Value
strDATE2 = Sheet2.Range("b3").Value

'SET strSQL
strSQL = "SET DATEFORMAT DMY EXEC "
strSQL = strSQL & strSPNAME
strSQL = strSQL & " '" & strDATE1 & "'" & "," & "'" & strDATE2 & "'"

' Create a connection object.
Dim cnAnE As ADODB.Connection
Set cnAnE = New ADODB.Connection

' Create a recordset object.
Dim rsAnE As ADODB.Recordset
Set rsAnE = New ADODB.Recordset
[COLOR=red yellow][b][i]cnAnE.CommandTimeout = 60[/i][/b][/color]

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the ane database on SYAS2K08.
strConn = strConn & "Server=svr; Database=db; uid=id; ;pwd=pwd;"


'Now open the connection.
cnAnE.Open strConn


'Return data
With rsAnE
    ' Assign the Connection object.
    .ActiveConnection = cnAnE
    ' Extract the required records.
    .Open strSQL  'timeout here!!
    ' Copy the records into cell A1 on Sheet1.
    Sheet2.Range(Sheet2.Range("b4").Value).CopyFromRecordset rsAnE
    
    ' Tidy up
    .Close
End With

cnAnE.Close
Set rsAnE = Nothing
Set cnAnE = Nothing

End Sub

Don't know if i've put it in the right place, but it seems to work OK.


Cheers, Craig
Si fractum non sit, noli id reficere