Recordset not getting data? 2

[riarc]

I have an ASP page that is executing a stored procedure on a SQL server. There is a temp table in the proc. The procedure is executing but the recordset is not opening. must I do something special when I use a temp table in my proc. If I remove the temp table I get a recordset

 

[cbokowski]

Can you run it in Query Analyser? Can you post the Sp?

 

[riarc]

Here is the proc..
I was able to see that the procedure fires in profiler.
but when I try to get a recordcount I get an error msg saying that you can't use recordcount when the resordset is closed. But I take out the temp table it works fine.
I thought maybe it was a permissions thing with tempdb but I have rights...I'm very stumped on this one.

CREATE PROCEDURE rtv_ref_for_dt_range
@start_dt datetime,
@end_dt datetime
AS
BEGIN
DECLARE @total int

CREATE TABLE #tmp(ref_id int not null,
ref_dt datetime not null,
end_dev datetime null,
st_coach datetime null,
elaps_dev int null,
elaps_jc int null)

INSERT INTO #tmp(ref_id, ref_dt, end_dev, st_coach )
SELECT rm.ref_id, rm.ref_dt, jd.end_jd_dt, jc.jc_start_dt
FROM referral_mst rm, job_coaching jc, job_dev jd
WHERE ref_dt >= @start_dt
AND ref_dt <= @end_dt
AND rm.ref_id *= jd.ref_id
AND rm.ref_id *= jc.ref_id

UPDATE #tmp
SET elaps_dev = datediff(day, ref_dt, end_dev),
elaps_jc = datediff(day, end_dev, st_coach)

SELECT @total = COUNT(ref_id) FROM #tmp

SELECT ref_id, CONVERT(CHAR(10),ref_dt,101) as ref_dt,
CONVERT(CHAR(10),end_dev,101) as end_dev,
CONVERT(CHAR(10),st_coach,101) as start_coach,
elaps_dev, elaps_jc, @total as Total
FROM #tmp
END

 

[cbokowski]

I would try adding what is in red below.

CREATE PROCEDURE rtv_ref_for_dt_range
@start_dt datetime,
@end_dt datetime
AS
BEGIN
DECLARE @total int
SET NOCOUNT ON;
CREATE TABLE #tmp(ref_id int not null,
ref_dt datetime not null,
end_dev datetime null,
st_coach datetime null,
elaps_dev int null,
elaps_jc int null)

INSERT INTO #tmp(ref_id, ref_dt, end_dev, st_coach )
SELECT rm.ref_id, rm.ref_dt, jd.end_jd_dt, jc.jc_start_dt
FROM referral_mst rm, job_coaching jc, job_dev jd
WHERE ref_dt >= @start_dt
AND ref_dt <= @end_dt
AND rm.ref_id *= jd.ref_id
AND rm.ref_id *= jc.ref_id

UPDATE #tmp
SET elaps_dev = datediff(day, ref_dt, end_dev),
elaps_jc = datediff(day, end_dev, st_coach)

SELECT @total = COUNT(ref_id) FROM #tmp
SET NOCOUNT OFF;
SELECT ref_id, CONVERT(CHAR(10),ref_dt,101) as ref_dt,
CONVERT(CHAR(10),end_dev,101) as end_dev,
CONVERT(CHAR(10),st_coach,101) as start_coach,
elaps_dev, elaps_jc, @total as Total
FROM #tmp
END

 

[riarc]

That worked... How did you know that. And why does that work?

 

[cbokowski]

Cant recall how I found out about NO COUNT.

How/why it works from SQL BOL.
&quot;NO COUNT :Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.&quot;

so without the nocount you were getting multiple recordsets.

 

[riarc]

I thought that was the case but I was confused as to why the recordset was not open. I thought it was an ASP problem. Thanks for your help.