Stored procedure from javascript ASP?

[NeoTeq]

I'm having a hard time getting results from a stored procedure (from SQL Server 2000) and using them in a javascript ASP page (in IIS 5) by opening them somehow using ADO. The stored procedure is quite long, but it basically does something like:

Code:

CREATE PROCEDURE GetWeekBuckets(@CustomerID int, @EndDate datetime, @NumberOfWeeks int)
AS

CREATE TABLE #WeekBuckets (
ReportYear int NULL,
ReportWeek int NULL,
ReportBucket int NULL,
ReportAmount float NULL,
ReportCount float NULL
)

WHILE @NumberOfWeeks >= 0
BEGIN
INSERT INTO #WeekBuckets (ReportYear, ReportWeek, ReportBucket, ReportAmount, ReportCount)
SELECT DatePart(YEAR, @EndDate) AS ReportYear, DatePart(WEEK, @EndDate) AS ReportWeek,
dbo.GetBucket(DateDiff(DAY, InvoiceDate, @EndDate)) AS ReportBucket,
SUM(AmountImported) AS ReportAmount, COUNT(*) AS ReportCount
FROM MyData
WHERE (CloseDate IS NULL OR CloseDate > @EndDate) AND
InvoiceDate <= @EndDate
GROUP BY dbo.GetBucket(DateDiff(DAY, InvoiceDate, @EndDate))

SELECT @EndDate = DateAdd(WEEK, -1, @EndDate)
SELECT @NumberOfWeeks = @NumberOfWeeks - 1
END

SELECT * FROM #WeekBuckets ORDER BY ReportYear ASC, ReportWeek ASC

DROP TABLE #WeekBuckets
GO

It returns a beautiful recordset when asked to do so from the Query Analyzer:

Code:

EXEC GetWeekBuckets 1, '2006-01-01', 52

Now, I've gone through all sorts of possibilities in javascript ASP, by simply opening a recordset with the same text as what I executed in Query Analyzer, by using a Command object and them trying to assign it as a source to a recordset in a few different ways, but all to no avail. The error I'm getting is ADODB.Recordset (0x800A0E78), which says the operation is not allowed when the object is closed (when trying to loop through the recordset).

Any ideas?

[Kevin3NF]

What happens if you remove the Drop Table statement at the end?  It will drop on its own when the connection/spid is closed...

[NeoTeq]

I figured out the problem - Sorry for not checking back sooner. The DROP TABLE statement is fine, it is needed for when I execute the procedure more than once on a page. The problem was that the INSERT statement gave back a count which was interpreted as an empty recordset. So now, it looks like this:

Code:

CREATE PROCEDURE GetWeekBuckets(@CustomerID int, @EndDate datetime, @NumberOfWeeks int)
AS

SET NOCOUNT ON

CREATE TABLE #WeekBuckets (
ReportYear int NULL,
ReportWeek int NULL,
ReportBucket int NULL,
ReportAmount float NULL,
ReportCount float NULL
)

WHILE @NumberOfWeeks >= 0
BEGIN
INSERT INTO #WeekBuckets (ReportYear, ReportWeek, ReportBucket, ReportAmount, ReportCount)
SELECT DatePart(YEAR, @EndDate) AS ReportYear, DatePart(WEEK, @EndDate) AS ReportWeek,
dbo.GetBucket(DateDiff(DAY, InvoiceDate, @EndDate)) AS ReportBucket,
SUM(AmountImported) AS ReportAmount, COUNT(*) AS ReportCount
FROM MyData
WHERE (CloseDate IS NULL OR CloseDate > @EndDate) AND
InvoiceDate <= @EndDate
GROUP BY dbo.GetBucket(DateDiff(DAY, InvoiceDate, @EndDate))

SELECT @EndDate = DateAdd(WEEK, -1, @EndDate)
SELECT @NumberOfWeeks = @NumberOfWeeks - 1
END

SELECT * FROM #WeekBuckets ORDER BY ReportYear ASC, ReportWeek ASC

DROP TABLE #WeekBuckets
SET NOCOUNT OFF

GO

And the ASP:

Code:

var rs = new AtciveXObject("adodb.recordset");

rs = con.Execute("EXEC GetWeekBuckets 1, '2006-01-01', 52");

[Kevin3NF]

Yep...seen that before as well.  Specifically on an Access ADP front-end to a SQL 2000 backend...