Sleeper: Timeout

[TedMosby]

I have a problem where I dont understand how it times out. When I have run the same SP in SQL Server it works fine.

Run-time error -2147217871 [Microsoft][SQL Native Client] Query timeout expired

This is the code I have.

Sub BuildUpdateData2()
Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
Dim stProcName As String
Dim strSQLServer As String        
strSQLServer = "Driver={SQL Native Client};" & _       
"Server=CISSQL1;" & _       
"Database=CORPINFO;" & _       
"Trusted_Connection=Yes"    
Set cnnDW = New ADODB.Connection    
Application.ScreenUpdating = False    
cnnDW.Open strSQLServer    
Set rsDW = New ADODB.Recordset    
stProcName = "EXEC dbo.sp_PARA_UpdateLkUp "    
rsDW.CursorLocation = adUseClient    
Debug.Print stProcName    
rsDW.Open stProcName, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText    
Application.ScreenUpdating = False    
cnnDW.Close    
Set cnnDW = Nothing    
Exit Sub
End Sub

Where have I gone wrong?

[Bob Phillips]

Ted,

Have you ever called an SP in that way. I ask as I tend to use a command and object when calling an SP.

My ADO code is all wrapped in a data access class, so I would need to revisit to check whether I do that because of preference or need.

[TedMosby]

Yes usually call an SP this way before, but usually had parameters with it too, but this doesnt need any parameters.

How would I do this with the Command & Object?

[Bob Phillips]

This is an example from my notes using a command object, albeit with a parameter

Public Function RunAccessSP
Dim mpParam As Object 'ADODB.Parameter
Dim mpCmd As Object
Dim mpRS As Object
Dim mpConn  As Object
Dim mpParamValue As String
mpParamValue = "some value"
Set mpConn = CreateObject("ADODB.Connection")
    mpConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & "C:\bob.mdb"
Set mpCmd = CreateObject("ADODB.Command")
    Set mpCmd.ActiveConnection = mpConn
mpCmd.CommandText = "spName"
    mpCmd.CommandType = adcmdStoredProc
Set mpParam = mpCmd.CreateParameter("prmName", adVarChar, _
              adParamInput, Len(mpParamvalue))
    mpParam.Value = mpParamvalue
    mpCmd.Parameters.Append mpParam
Set mpRS = CreateObject("ADODB.Recordset")
    mpRS.Open mpCmd
Do Until mpRS.EOF
        Debug.Print mpRS(0), mpRS(1), mpRS(3)
        mpRS.MoveNext
    Loop
Set mpRS = Nothing
    Set mpCmd = Nothing
    Set mpConn = Nothing
End Function