Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ADO - SQL Server ConnectionTimeout

Posted on 2001-06-03
4
190 Views
Last Modified: 2013-11-23
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
Comment
Question by:jonesgj
4 Comments
 
LVL 15

Accepted Solution

by:
robbert earned 150 total points
ID: 6150126
> Checking ADO help I find that I can set a ConnectionTimeout parameter. I have tried this with no success.

Specify ADOCmd.CommandTimeout, too.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6150272
You can set the CommandTimeout of the Connection object:
ADOcnn1.CommandTimeout = 300
0
 

Author Comment

by:jonesgj
ID: 6150726
Thanks ....just what I needed..
0
 
LVL 1

Expert Comment

by:jdunck
ID: 6150739
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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question