PDA

View Full Version : Sleeper: Timeout



TedMosby
02-26-2009, 01:38 AM
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
02-26-2009, 01:54 AM
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
02-26-2009, 02:11 AM
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
02-26-2009, 02:21 AM
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