Hi,

I apologize in advance for such a long post, but I would rather you have more info than you want than less than you need.

I am trying to make an asynchronous call to a stored procedure on a MS SQL Server 2000 database from a VB6 SP5 application referencing ADO 2.6. I am running Windows 2K SP4.
The stored procedure takes a very long time to run, about an hour in Query Analyzer, but it DOES run in QA without error. I move over to my VB application and get the error, "data provider or other service returned an E_FAIL status" every time.
I open both the connection and the recordset for asynchronous operation and then retreive the recordset in the FetchComplete event. I have tested the code with an inline sql statement and an sp that selected the top 50k records from a large table and it worked as expected. It is when I run the code against this stored procedure that I have problems but I get the same error every time. My code is as follows:

Recordset object set up;

Private Sub Class_Initialize()

' instantiate and set up the async recordset object
Set m_rsAsync = New ADODB.Recordset

With m_rsAsync

.CursorLocation = adUseClient
.Properties("Initial Fetch Size") = 0
.Properties("Background Fetch Size") = 4

End With

End Sub

This is the actual OPENING method;

Public Sub GetCurrentMonthAccrualPriceVariance(dblLow As Double, dblHi As Double)
'---------------------------------------------------------------------------------------
' Procedure : GetCurrentMonthAccrualPriceVariance
' DateTime : 10/22/2003 12:34
' Author : EMerkel
' Purpose : ok so this is a sub that kicks off fetching to an asynchronous recordset so that the user can go on
' about their business while they are waiting for the records to be returned. when the records finally are
' returned we will get the actual recordset from the event that is raise so we are making this a sub so that
' the code isn't blocking while this is running
'---------------------------------------------------------------------------------------
'
Dim cnAsync As ADODB.Connection

g_Err.Push "GetCurrentMonthAccrualPriceVariance"
g_Err.CurrentOperation = "modData"

On Error GoTo GetCurrentMonthAccrualPriceVariance_Error

Set cnAsync = New ADODB.Connection
cnAsync.CursorLocation = adUseClient
cnAsync.ConnectionTimeout = 0

cnAsync.Open "Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=UserID;Initial Catalog=database;Data Source=server", , , adAsyncConnect
'g_SQLcn.ConnectionString
Do Until cnAsync.state <> adStateConnecting
Debug.Print "Opening connection..."
Loop

'g_SQLcn.CursorLocation = adUseClient

' try it with an execute
Set m_rsAsync = cnAsync.Execute("CurrentMonthAccrual_PriceVariance Get " & CStr(dblLow) & ", " & CStr(dblHi), , adAsyncFetch)
' test sp
' Set m_rsAsync = cnAsync.Execute("sp_EDDIE_TEST", , adAsyncFetch)
' try it with an rs.open
' m_rsAsync.Open "CurrentMonthAccrual_PriceVarianceGet " & CStr(dblLow) & ", " & CStr(dblHi), cnAsync, , , adAsyncFetch


GetCurrentMonthAccrualPriceVariance_Cleanup:
g_Err.Pop

Exit Sub

GetCurrentMonthAccrualPriceVariance_Error:

g_Err.HandleError
Resume GetCurrentMonthAccrualPriceVariance_Cleanup

End Sub

I get the fetch progress feedback until the thing errors out, here is that code;

Private Sub m_rsAsync_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

RaiseEvent AsyncFetchProgress(Progress, MaxProgress, adStatus, pRecordset)

End Sub

and finally here is the fetch complete code, this apparently runs as it's not till it go to put the data into the grid that I actually get an error;

Private Sub m_rsAsync_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

Dim rs As ADODB.Recordset

Set rs = pRecordset

Do While rs.state = adStateClosed
Set rs = rs.NextRecordset
Loop

RaiseEvent AsyncFetchComplete(pError, adStatus, rs, Me.CurrentReportName)

End Sub

The code for my stored procedure is as follows:

CREATE PROCEDURE CurrentMonthAccrual_PriceVarianceGet

-- these values will be for the low and high difference tests
@LowVal DECIMAL(18,9),
@HighVal DECIMAL(18,9)

AS

SET NOCOUNT ON

-- these values will be for the where clause ultimately
DECLARE @GasVolVar INT
DECLARE @OilVolVar INT
DECLARE @NGLVolVar INT

-- this is so we can get the right records from the history table
DECLARE @ThisMonth INT
DECLARE @LastMonth INT

-- get the value of this month from the tmpPrice table
SET @ThisMonth = (SELECT top 1(productionMonth) AS thisMonth FROM tmpPrice)

-- set the value for last month to one less
SET @lastMonth = @thisMonth - 1

-- if lastmonth is 0 (this month is January/1) then set it to 12
IF @lastMonth = 0
BEGIN
SET @LastMonth = 12
END

-- get the tolerances from that table for this report
SET @GasVolVar = (SELECT AccrualPriceVarianceGasVolumeDiff FROM systemTolerances)
--PRINT 'GasVolVar = ' + CAST(@GasVolVar AS VARCHAR(50))

SET @oilVolVar = (SELECT AccrualPriceVarianceOilVolumeDiff FROM systemTolerances)
--PRINT 'OilVolVar = ' + CAST(@OilVolVar AS VARCHAR(50))

SET @NGLVolVar = (SELECT AccrualPriceVarianceNGLVolumeDiff FROM systemTolerances)
--PRINT 'NGLVolVar = ' + CAST(@NGLVolVar AS VARCHAR(50))

-- get the oil data into the temp table
SELECT vwCurrentMonthAccrualOil.* INTO #tblReport FROM vwCurrentMonthAccrualOil INNER JOIN Receivable ON
Receivable.productID = vwCurrentMonthAccrualOil.productID AND
Receivable.propertyID = vwCurrentMonthAccrualOil.propertyID AND
vwCurrentMonthAccrualOil.productionMonth - 1 = Receivable.productionMonth AND
(vwCurrentMonthAccrualOil.accrualNetVolume - Receivable.accrualNetVolume > @HighVal OR vwCurrentMonthAccrualOil.accrualNetVolume - Receivable.accrualNetVolume < @LowVal) WHERE vwCurrentMonthAccrualOil.accrualNetVolume > @oilVolVar

--now get the gas data into the temp table'

INSERT INTO #tblReport SELECT vwCurrentMonthAccrualGas.* FROM vwCurrentMonthAccrualGas INNER JOIN Receivable ON
Receivable.productID = vwCurrentMonthAccrualGas.productID AND
Receivable.propertyID = vwCurrentMonthAccrualGas.propertyID AND
vwCurrentMonthAccrualGas.productionMonth - 1 = Receivable.productionMonth AND
(vwCurrentMonthAccrualGas.accrualNetVolume - Receivable.accrualNetVolume > @HighVal OR vwCurrentMonthAccrualGas.accrualNetVolume - Receivable.accrualNetVolume < @LowVal) WHERE vwCurrentMonthAccrualGas.accrualNetVolume > @GasVolVar

--and now the ngl data'

INSERT INTO #tblReport SELECT vwCurrentMonthAccrualNGL.* FROM vwCurrentMonthAccrualNGL INNER JOIN Receivable ON
Receivable.productID = vwCurrentMonthAccrualNGL.productID AND
Receivable.propertyID = vwCurrentMonthAccrualNGL.propertyID AND
vwCurrentMonthAccrualNGL.productionMonth - 1 = Receivable.productionMonth AND
(vwCurrentMonthAccrualNGL.accrualNetVolume - Receivable.accrualNetVolume > @HighVal OR vwCurrentMonthAccrualNGL.accrualNetVolume - Receivable.accrualNetVolume < @LowVal) WHERE vwCurrentMonthAccrualNGL.accrualNetVolume > @NGLVolVar

--now select the data back from the tmp table
-- 10/23/2003 1:19:27 PM
SELECT
Property.artesiaPropertyNumber,
Property.propertyName,
Users.userName AS Analyst,
Products.productName AS Product,
#tblReport.price AS CurrentMonthPrice,
tmpPrice.source,
#tblReport.accrualNetVolume AS CurrentMonthNetVolume,
Receivable.price AS PriorMonthPrice,
PriceHistory.source AS PriorMonthPriceSource,
Receivable.accrualNetVolume AS PriorMonthNetVolume,
#tblReport.price - Receivable.price AS PriceVariance
FROM
#tblReport
INNER JOIN Products ON
#tblReport.productID = Products.productId
INNER JOIN Receivable
INNER JOIN Analysts
INNER JOIN Users ON
Analysts.userID = Users.userID
INNER JOIN Property
INNER JOIN PriceHistory ON
PriceHistory.propertyNumber = Property.artesiaPropertyNumber
INNER JOIN tmpPrice ON
tmpPrice.propertyNumber = Property.artesiaPropertyNumber ON
Analysts.analystID = Property.AssignedTo ON
PriceHistory.productionYear = Receivable.productionYear AND
PriceHistory.productionMonth = Receivable.productionMonth AND
PriceHistory.productID = Receivable.productID ON
tmpPrice.productionYear = #tblReport.productionYear AND
tmpPrice.productionMonth = #tblReport.productionMonth AND
tmpPrice.productID = #tblReport.productID AND
Property.propertyID = #tblReport.propertyID AND
#tblReport.productionMonth - 1 = Receivable.productionMonth AND
(#tblReport.accrualNetVolume - Receivable.accrualNetVolume > @HighVal OR #tblReport.accrualNetVolume - Receivable.accrualNetVolume < @LowVal) AND
#tblReport.productID = Receivable.productID AND
#tblReport.propertyID = Receivable.propertyID

-- now cleanup
DROP TABLE #tblReport

SET NOCOUNT OFF

If anyone has a clue I would like to hear about it as this has been kicking my butt for almost a week now!

Thanks very much

Eddie