no resultset from SP (using NOCOUNT ON)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

no resultset from SP (using NOCOUNT ON)

Author

Topic

sashir
Starting Member

6 Posts

Posted - 2004-11-26 : 04:12:01

I encounterd a strange problem with Stored Procedure, hoping the SQLTeam will help me.

I've included the options
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

the results are stored in a #Temp table

The SP executes fine on Query Analyzer, and shows the contents of the temp table. But when I run this SP using ASP, I got the following error
-----------
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/test_site/test.asp, line 59
------------

I've tried using Recordset.MoveNextRecordset, if at all there are more than one recordset, obviously I got the same error.

Pls help...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-26 : 05:34:56

this error indicates that you want to perform recordset operations on a closed recordset.
show us the asp code. this is asp bug.

Go with the flow & have fun! Else fight the flow

sashir
Starting Member

6 Posts

Posted - 2004-11-26 : 05:55:36

I used the foll code. This coding style works well with other simple SPs.

objRS.Open "atul_med.[sp_rep_sale] '0401'",Con, adOpenKeyset, adLockOptimistic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-26 : 06:03:03

so that is you line 59 in test.asp?
because when i got that error i did:
rs.Open ...
--do stuff
rs.Close()
rs.MoveNext -- this is where my error showed up.

have you done?
dim objRS
set objRS = Server.CreateObject("ADODB.Recordset")

Go with the flow & have fun! Else fight the flow

sashir
Starting Member

6 Posts

Posted - 2004-11-26 : 06:27:03

yes, I declared and instantiated the objRS object
dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")

let me show the SP code listing...

CREATE PROCEDURE atul_med.[sp_rep_sale]
(
@rep_in varchar(100)
)
AS

SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

DECLARE

@REP int,
@temp_pack varchar(20),
@temp_ntp int,
@temp_party_code varchar(20),
@temp_party_name varchar(20),
@temp_prod_code varchar(20),
@temp_prod_desc varchar(20),
@tot_qty int,
@tot_amount int,
@iCnt int,
@tot_sale_qty int,
@net_tot_sale_qty int,
@TableName varchar(10)

select @REP= @rep_in
set @tot_sale_qty=0
set @net_tot_sale_qty=0
set @TableName='#Test2'

Declare @SQL VarChar(1000),
@cnt int,
@iCurLine int

set @iCurLine=1

select @Cnt = (SELECT count(*) FROM VW_PART_REP WHERE (REP = @REP))
print "Nos Of Party :"+ convert(char(1),@iCnt)

Create Table #Test2 (ID int identity,prod_desc nvarchar(100),pack nvarchar(100),ntp int)-- ,tot_qty int ,tot_amt int )

SELECT @SQL = "Create Table "+@TableName+" ("
SELECT @SQL = @SQL + "ID int identity,prod_desc nvarchar(100),pack nvarchar(100),ntp int,"

while @iCurLine <= @cnt
begin
SELECT @SQL= "ALTER TABLE #Test2 ADD "
SELECT @SQL = @SQL + "party_qty"+convert(char(2),@iCurLine)+" int "
set @iCurLine=@iCurLine+1
Exec (@SQL)
print @sql
end
SELECT @SQL= "ALTER TABLE #Test2 ADD "
SELECT @SQL = @SQL +"tot_qty int , tot_amt int "
SELECT @SQL = @SQL
exec (@SQL)
print @SQL

set @iCurLine=1

--=============================================

--print '=================Product Name========================'
DECLARE PROD_CUR CURSOR FOR
SELECT distinct(prod_code) as prod_code,prod_desc,pack,ntp FROM VW_sales_sum
WHERE (REP = @REP)
group by prod_code,prod_desc,pack,ntp

Declare @SQL_ins VarChar(1000)

OPEN PROD_CUR
FETCH NEXT FROM PROD_CUR
INTO @temp_prod_code,@temp_prod_desc,@temp_pack,@temp_ntp

WHILE @@FETCH_STATUS = 0
BEGIN
--Print @temp_prod_desc+'-'+convert(varchar(10),@temp_pack)+'-'+convert(varchar(10),@temp_ntp)
--print '=================prod_code :'+convert(varchar(10),@temp_prod_code)+'========================'
print "============Insert Statement============"

SET @SQL_ins = "Insert into "+@TableName+" values("
SET @SQL_ins = @SQL_ins +""""+ @temp_prod_desc+""","""+@temp_pack+""","""+convert(char(10),@temp_ntp)+""","

DECLARE Party_CUR1 CURSOR FOR
SELECT party_code,party_name FROM VW_PART_REP
WHERE (REP = @REP) group by party_code,party_name

DECLARE @SQL1 varchar(10),@SQL2 varchar(100)
set @SQL2=''
OPEN Party_CUR1
FETCH NEXT FROM Party_CUR1
INTO @temp_party_code,@temp_party_name

WHILE @@FETCH_STATUS = 0
BEGIN
print "==Party_name :"+ @temp_party_name +"===Party_Code :"+convert(char(3),@temp_party_Code)
set @tot_sale_qty=0
set @tot_sale_qty= (select sum(issuedqty)as tot_qty from vw_sales_sum
where party_code =@temp_party_code
and (REP = @REP)
and (prod_code=@temp_prod_code))
if @tot_sale_qty IS NULL
begin
set @tot_sale_qty=0
print "===Tot Qty :"+convert(varchar(10),@tot_sale_qty)
end
else
begin
print "===Tot Qty :"+convert(varchar(10),@tot_sale_qty)
end
SELECT @SQL1 = @tot_sale_qty
select @net_tot_sale_qty=@net_tot_sale_qty+@tot_sale_qty
--print @SQL1
Select @SQL2 = @SQL2 + @SQL1 +","

FETCH NEXT FROM Party_CUR1
INTO @temp_party_code,@temp_party_name

END

CLOSE Party_CUR1
DEALLOCATE Party_CUR1
print '==============================='
print @SQL2+convert(char(4),@net_tot_sale_qty)

--set @net_tot_sale_qty=@tot_sale_qty+@net_tot_sale_qty
--SET @SQL = @SQL+convert(char(10),@tot_sale_qty)+','
--set @tot_sale_qty=0

Select @SQL_ins = @SQL_ins+@SQL2+convert(char(8),@net_tot_sale_qty)
Select @SQL_ins = @SQL_ins+","
Select @SQL_ins = @SQL_ins+convert(char(12),(@net_tot_sale_qty*@temp_ntp))
Select @SQL_ins = @SQL_ins+")"
print @SQL_ins
Exec (@SQL_ins)

select @net_tot_sale_qty=0
FETCH NEXT FROM PROD_CUR
INTO @temp_prod_code,@temp_prod_desc,@temp_pack,@temp_ntp

END

CLOSE PROD_CUR

DEALLOCATE PROD_CUR

select * from #Test2

SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER ON
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-26 : 06:35:53

comment the print statements. they might be messing with your output...

Go with the flow & have fun! Else fight the flow

sashir
Starting Member

6 Posts

Posted - 2004-11-26 : 07:47:15

Well I removed the print statements and tried it again. Let me give a brief picture of the purpose of the SP.
Depending on the rep_in value (the repsentative code) passed to the SP I'm trying to create a temp table having variable number of columns for party_qty (i.e total quantitty of each party this representative deals with). So, the no of party_qty varies (party_qty1,party_qty2,party_qty 3 ) for each representative at run time.
The query analyzer shows 2 resulset.
1) an empty table (the #temp table just created at run time)
2) the same table with all rows.

Now, in the ASP it is giving the following error :-
Microsoft OLE DB Provider for SQL Server (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/test_site/test.asp, line 47

sashir
Starting Member

6 Posts

Posted - 2004-11-26 : 07:56:43

I had written a select statement soon after the line of creating the #Temp table in the SP that's why it was showing an extra empty resultset, sorry about that .
I removed that line QA shows noly 1 resultset.

But the error on the ASP page is :-
Microsoft OLE DB Provider for SQL Server (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/test_site/test.asp, line 47

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-26 : 08:14:16

why do you change table structure. that is not really efficient. and it's also bad design.
it would be easier to use an intermediate table to specify the relationships.

Go with the flow & have fun! Else fight the flow

sashir
Starting Member

6 Posts

Posted - 2004-11-26 : 08:31:27

i can't use an intermediate table since i can't predict the no of columns it should have.
because each representative deals with varying no of partys(customers), also more partys(the customers) may be added(or removed) with time. (and may be the site will become too slow if multiple user access it.)

mswaine
Starting Member

1 Post

Posted - 2006-01-24 : 11:47:22

quote:
Originally posted by sashir

I had written a select statement soon after the line of creating the #Temp table in the SP that's why it was showing an extra empty resultset, sorry about that .
I removed that line QA shows noly 1 resultset.

But the error on the ASP page is :-
Microsoft OLE DB Provider for SQL Server (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/test_site/test.asp, line 47

I've encountered this confusing error message before when the account that was trying to execute the stored procedure didn't have the necessary execute permissions on the object. Definately worth checking!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources