Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

ADO - SQL Server ConnectionTimeout

I have successfully connected to my SQL Server db and now can run simple stored procedures and in-line SQL.

However, when I use the code below, which works for other Stored Procedures, to run a Stored Procedure to delete all the rows in a large table I receive a 'Timeout' error.
Checking ADO help I find that I can set a ConnectionTimeout parameter. I have tried this with no success.

Any ideas? ... heres my code...Thanks in anticipation

'##################
Private Sub Command4_Click()
'''''''''''''''''
'My example using ADO to execute a Stored Procedure
'''''''''''''''''

Dim ADOcnn1 As ADODB.Connection
Dim ADOCmd As New ADODB.Command
Dim objErr As ADODB.Error

On Error GoTo Myerr
   
'Open a connection using a DSN and ODBC tags.
Set ADOcnn1 = New ADODB.Connection
ADOcnn1.ConnectionString = "DSN=SQL123;UID=me;PWD=qwert;"
ADOcnn1.ConnectionTimeout = 180
ADOcnn1.Open

'Check the state of the connection to ensure we are connected
If ADOcnn1.State = adStateOpen Then

    'Set the Command object properties
    Set ADOCmd.ActiveConnection = ADOcnn1
    ADOCmd.CommandText = "uSP_Delete_Bang"
    ADOCmd.CommandType = adCmdStoredProc
   
    'Execute the Command object
    ADOCmd.Execute
   
    MsgBox "Complete"
     
    Set ADOCmd = Nothing
    ADOcnn1.Close
    Set ADOcnn1 = Nothing

Else
     
    'Display all errors
    For Each objErr In ADOcnn1.Errors
    Debug.Print objErr.Description
    Next
     
End If


Exit Sub

Myerr:

MsgBox Error + " " + Str(Err)

Exit Sub


End Sub
0
jonesgj
Asked:
jonesgj
1 Solution
 
robbertCommented:
> Checking ADO help I find that I can set a ConnectionTimeout parameter. I have tried this with no success.

Specify ADOCmd.CommandTimeout, too.
0
 
Éric MoreauSenior .Net ConsultantCommented:
You can set the CommandTimeout of the Connection object:
ADOcnn1.CommandTimeout = 300
0
 
jonesgjAuthor Commented:
Thanks ....just what I needed..
0
 
jdunckCommented:
Further, if you are doing a very long execution, and you do not specifically need to wait until the task is done, you can do the following:

objCmd.Execute , adAsyncExecute , adCmdStoredProc+ adExecuteNoRecords

adAsyncExecute indicates that the method should return before finishing the task.

adExecuteNoRecords indicates that the stored proc will not return either a recordset or output params.  Make sure this is the case before you use it... otherwise I'm not sure what will happend.

Both of these should make the call very quick.

Just how large -is- that table, anyway?

If it is very large and heavily indexed, you may need to resort to partitioning...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now