×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Procedure time out problems

Procedure time out problems

Procedure time out problems

(OP)
I am attempting to delete large amounts of data (typically 200,000 records) from a large file based on two parameters that identify the record type, using a stored procedure.  If I execute the proc directly in the ADP and type in the parameters, it works great. If I do the exact same thing using an ADO procedure, I get a time out error. I have set the various time out parameters to 0 in the ADO code, and I have set the query -wait in SQL Server 2000 /Servername / Properties page to zero.  Nothing seems to change this. Could some one tell me what I am missing?

Here is the proc:
Alter PROCEDURE deltblMastSch
@OrderType char(2),
@OrderSubType char(1)
 AS
DELETE FROM tblMastSch Where Ordertype = @OrderType and OrderSubtype=@OrderSubtype


Here is the ADO code:


Public Function DelMastSchBlock(ByVal Ordertype As String, ByVal OrderSubtype As String, ByVal modname As String) As Integer

Dim oConn As New ADODB.Connection
Dim oCmd As New ADODB.Command
Dim RetVal As ADODB.Parameter
Dim blkParam As ADODB.Parameter
Dim blkParam2 As ADODB.Parameter
Dim objerror As ADODB.Error
Dim i As Integer

On Error GoTo err_h
Forms!frmMain!txtModule = modname
Forms!frmMain.Repaint

With oCmd

Set RetVal = .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, , Null)

.Parameters.Append RetVal
Set blkParam = .CreateParameter("@OrderType", adWChar, adParamInput, 2, Ordertype)
.Parameters.Append blkParam
Set blkParam2 = .CreateParameter("@OrderSubType", adWChar, adParamInput, 2, OrderSubtype)
.Parameters.Append blkParam2

.CommandText = "deltblMastSch"
.CommandType = adCmdStoredProc

oConn.Provider = "SQEDB"
oConn.ConnectionString = "Data Source=FS4;Initial Catalog=MasterSchedule;Trusted_Connection=Yes"
oConn.ConnectionTimeout = 0

oConn.CommandTimeout = 0
oConn.Open
.ActiveConnection = oConn



    .Execute
    GoTo Exit_Delmast

End With




Exit Function

'************
Exit_Delmast:
'************
DelMastSchBlock = oCmd("RETURN_VALUE")
Set oCmd = Nothing
Set RetVal = Nothing
Set blkParam = Nothing
Set blkParam2 = Nothing
Set objerror = Nothing
oConn.Close
Set oConn = Nothing
Exit Function


'******
err_h:
'*******


For Each objerror In oConn.Errors
MsgBox Str$(objerror.Number) + " " + objerror.Description
msgbox error$
stop


Next




End Function

RE: Procedure time out problems

(OP)
ok, I eventually figured this out myself. To any interested party, this is the solution:

oConn.CommandTimeout = 0

should be oCmd.CommandTimeout = 0

The connection objects' timeout setting has no effect on the command timeout

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close