App hangs using ADO to execute a SQL Stored Procedure
- From: "dj7934" <dj7934@xxxxxxxxxxx>
- Date: 19 Apr 2005 07:53:56 -0700
I'm using an Access Database and ADO to call a SQL Stored Procedure to
update about 2 to 4 records in a SQL table. When I call the .execute
method of the ADO Command object, the app hangs until the timeout
period expires. The SQL Server is also unresponsive to ALL subsequent
requests until my code is stopped (there's a blocking process on the
SQL Server until I end my code.) The update is NOT successful. There
are no errors in the SQL Server log.
If I execute the stored procedure in Query Analyzer, it runs fine and
produces the correct result in a fraction of a second. I have double
checked my parameter declaration to ensure the Table/SP & ADO code all
declare the parameter using the same type consistently. I have debugged
the parameter value being sent, deleted and re-created the stored
procedure, tried various connection strings (including a system DSN
that is setup and working otherwise), and varying ways of declaring /
creating the ADO objects and assigning their values. Currently, VBA
references ADO 2.7, but I've tried 2.5 and 2.6 also.
Everything I have tried produces the same result. However, I have
noticed that if I evaluate the value of the .commandtext property in
debug mode, I see that it is not exactly as assigned.
My assignment is: .CommandText = "Upd_BH_DLFlag"
The value when viewed in debug is: "{call Upd_BH_DLFlag(?)}"
That drew my attention and could be the issue or it may be an internal
translation that is OK, I guess.)
If anyone has any ideas to offer, I would appreciate them greatly. All
of the code involved is posted below.
**************************************************************
Here's the SP that gets called:
**************************************************************
CREATE PROCEDURE Upd_BH_DLFlag
( @SystemID int )
AS
UPDATE Batches SET DownloadFlag = 2 WHERE OrderID = (SELECT OrderID
FROM Batches WHERE SystemID = @SystemID)
**************************************************************
**************************************************************
Here's my Current conn string (I've tried many others too)
(Some values have been replaced/masked)
**************************************************************
Provider=sqloledb;Network Library=DBMSSOCN;Data
Source=10.xx.xx.xx,1433;Initial Catalog=dbPA_prod;User
ID=***;Password=***;
**************************************************************
**************************************************************
Here's the code:
**************************************************************
Public Sub UpdateBHDownloadFlag(lSystemID As Long)
Dim oCon As New ADODB.Connection
Dim oCmd As New ADODB.Command
Dim oParam As New ADODB.Parameter
'Open the connection
oCon.Open sSQLConnString
With oCmd
.ActiveConnection = oCon
.CommandTimeout = 10
.CommandType = adCmdStoredProc
.CommandText = "Upd_BH_DLFlag"
' Create the parameter
Set oParam = .CreateParameter("@SystemID", adInteger, adParamInput,
, lSystemID)
' Add it to the collection
.Parameters.Append oParam
' Execute the command
.Execute '(the code hangs right here)
End With
'Close the connection
oCon.Close
'Cleanup
Set oParam = Nothing
Set oCmd = Nothing
Set oCon = Nothing
End Sub
**************************************************************
Thanks in advance for taking the time to study this and submitting any
ideas you may have!
Dan
.
- Follow-Ups:
- Prev by Date: Re: "3021 No Current Record" on Insert?
- Next by Date: how to input database name for connection and another question.
- Previous by thread: How to test for dbNull field on OLEDB update of VFP table
- Next by thread: Re: App hangs using ADO to execute a SQL Stored Procedure
- Index(es):
Relevant Pages
|
|
