SQL Server Stored Proc to VB ADO Recordset 1

[eusjyfy]

I am trying to run the following stored proc from a VB ADO connection, which should return two record sets. When I run this from the Query Analyzer, it performs as expected, however, when I call this from VB code and try to loop through the recordset, I get an 'Recordset Not Open' error. I figured it was something with the VB code, yet when I called a stored proc that only performs a simple 'SELECT * FROM TABLE', this worked. What's wrong with my stored proc that VB ADO does not like?


CREATE PROCEDURE dbo.jf_BackLogReport AS

DECLARE @intCPDDMaxWeek AS INT
DECLARE @intCPDDMinWeek AS INT
DECLARE @intNowWeek AS INT
DECLARE @intNumberOrders AS INT
DECLARE @vcWeekNumber AS VARCHAR(120)
DECLARE @OEDMinusCPDD AS INT
DECLARE @OEDMinusCRDD AS INT
DECLARE @CPDDMinusCRDD AS INT
DECLARE @CPDDMinusOPDD AS INT

CREATE TABLE #DateMetrics
(
CPDD DATETIME,
OPDD DATETIME,
CRDD DATETIME,
OED DATETIME
)

INSERT INTO #DateMetrics

(CPDD, OPDD, CRDD, OED)

SELECT dbo.DelivOrder.SchedShippingDate AS CPDD,
dbo.DelivOrder.FirstRspShippingDate AS OPDD,
dbo.DelivOrder.ReqShippingDate AS CRDD,
MAX(dbo.DelivOrder.UpdDate) AS OED

FROM dbo.DelivOrder INNER JOIN
dbo.ExtRecipeLine ON dbo.DelivOrder.DelivOrderId =
dbo.ExtRecipeLine.DelivOrderId

WHERE (dbo.DelivOrder.DelivOrderState <> 60 AND
dbo.DelivOrder.DelivOrderState <> 10)
GROUP BY dbo.DelivOrder.SchedShippingDate,
dbo.DelivOrder.FirstRspShippingDate, dbo.DelivOrder.ReqShippingDate

SET @OEDMinusCPDD = (SELECT AVG(CONVERT(INT , CRDD) - CONVERT(INT , OED)) FROM #DateMetrics)
SET @OEDMinusCRDD = (SELECT AVG(CONVERT(INT , CPDD) - CONVERT(INT , OED)) FROM #DateMetrics)
SET @CPDDMinusCRDD = (SELECT AVG(CONVERT(INT , CPDD) - CONVERT(INT , CRDD)) FROM #DateMetrics)
SET @CPDDMinusOPDD = (SELECT AVG(CONVERT(INT , CPDD) - CONVERT(INT , OPDD)) FROM #DateMetrics)

DROP TABLE #DateMetrics

SET @intCPDDMaxWeek = (SELECT MAX(DATEPART(ww, SchedShippingDate)) AS CPDD
FROM dbo.DelivOrder
WHERE (DelivOrderState <> 60 AND DelivOrderState <> 10)
AND (DATEPART(ww, ReqShippingDate) < DATEPART(ww,
SchedShippingDate)))

SET @intCPDDMinWeek = (SELECT MIN(DATEPART(ww, SchedShippingDate)) AS CPDD
FROM dbo.DelivOrder
WHERE (DelivOrderState <> 60 AND DelivOrderState <> 10)
AND (DATEPART(ww, ReqShippingDate) < DATEPART(ww,
SchedShippingDate)))

SET @intNowWeek = DATEPART(ww, GETDATE())

CREATE TABLE #BackLoggedOrders
(
ShipWeek VARCHAR(120),
Orders_Backlogged INT
)

IF @intCPDDMinWeek > @intNowWeek
BEGIN
SET @intCPDDMinWeek = @intNowWeek
END

WHILE (@intCPDDMinWeek <= @intCPDDMaxWeek)
BEGIN
SET @vcWeekNumber = ('End Of Week ' + CONVERT(CHAR(2) , @intCPDDMinWeek))

SET @intNumberOrders = (SELECT COUNT(DelivOrderId) AS OrderId
FROM dbo.DelivOrder
WHERE (DelivOrderState <> 60 AND DelivOrderState <> 10)
AND (DATEPART(ww, ReqShippingDate) < DATEPART(ww, SchedShippingDate))
AND (DATEPART(ww, SchedShippingDate) > @intCPDDMinWeek))

INSERT INTO #BackLoggedOrders

(ShipWeek, Orders_Backlogged)

VALUES (@vcWeekNumber, @intNumberOrders)

IF @intCPDDMinWeek = @intCPDDMaxWeek
BEGIN
BREAK
END
ELSE
BEGIN
SET @intCPDDMinWeek = @intCPDDMinWeek + 1
CONTINUE
END
END

SELECT * FROM #BackLoggedOrders

SELECT @OEDMinusCPDD AS [OEDMinusCPDDAvg], @OEDMinusCRDD AS [OEDMinusCRDDAvg],
@CPDDMinusCRDD AS [CPDDMinusCRDDAvg], @CPDDMinusOPDD AS [CPDDMinusOPDDAvg]

 

[NoCoolHandle]

Yes... your stumbling block is taht you need to put the magic words

&quot;NOCOUNT ON&quot;

at the beginning of you proc (under the AS)
otherwise you will need to rs.nextrecordset before you will see the first record..

Source of frustration for me a few years ago.

E.g.
Create proc x
as
set nocount on
select * from sysobjects
go


HTH

Rob

 

[eusjyfy]

Thanks...Worked like a charm