ASP error caused by sql Server stored proc exec

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)

ASP error caused by sql Server stored proc exec

Author

Topic

ekellysql
Starting Member

16 Posts

Posted - 2006-03-28 : 12:05:30

I wrote a stored proc for a report. The sp runs fine in query analyzer but when I try and execute the stored procedure from an asp web page I'm getting an asp ADO error. The porblem is the asp code works fine for executing other stored procedures so I'm thinking the issue is with the stored proc somehow. I'm not very familiar with capturing stored procedure errors. Is there a way I can write the stored proc error to my asp page? I can post the stored proc if anyone thinks that may help.

any help would be great.

thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-28 : 12:50:55

U may need to Post the SP & the code portion in ASP where u access the SP

Srinika

ekellysql
Starting Member

16 Posts

Posted - 2006-03-28 : 12:55:45

Here's my sp and asp code that calls it. The last line of my asp code is where I get the error.
"Operation not allowed when the object is closed."

It works fine for calling other sp's though. Thanks for any help.

CREATE Procedure CE_Customer_Purchase_Info
(

@totalrevenue as int = 0,
@numdayssincelstpurchase as int = 0

)

As

declare @tab table ( capID int,
memID int,
tt int
)
insert @tab
select f.* from (

select top 100 PERCENT cM2.memID as capID,TD.memid,count(CA.whomade)as TT
from transactiondetails TD
inner join transactioninfo TI ON TI.TID = TD.TID
inner join catalog CA ON CA.CatalogID = TI.catalogID
inner join coversMems cM2 ON cM2.memId = CA.whomade

WHERE ((TD.cc_status='B' and TD.captureStatus = 'successfullyCaptured' and TD.tranStat = 'success')
OR (TD.cc_status='V' and TD.captureStatus = '7 day drop' and TD.tranStat = 'success'))
group by TD.memid, cM2.memID
order by TD.memid,count(CA.whomade)desc
) f

IF @@ERROR <>0
BEGIN
PRINT 'Error Occured'
END

SELECT --top 100
cM.MemId
, cM.FirstName
, cM.LastName
, cM.Email
, convert(integer,MPI.LPDate - convert(datetime,convert(char(10),cast(madeon As DateTime),101))) As LifeSpanDay
, MPI.CntOP
, MPI.TotPur As TotalRevenue
, MPI.LPDate
, convert(integer, getdate() - MPI.LPDate) As #DaysSinceLastPurchase
, convert(numeric,MPI.LPDate - convert(datetime,convert(char(10),cast(madeon As DateTime),101))) / MPI.CntOP As AvePurchaseCycle

, dbo.GetCustomerMaxIdleDays(cM.memId)
,
(SELECT Count(memId) As CntGoodPicks FROM transactiondetails TD
WHERE (cc_status='B' and captureStatus = 'successfullyCaptured' and tranStat = 'success'
and TD.memID = cM.MemId)) As CountofGoodPicks
, (SELECT Count(memId) As CntBadPicks FROM transactiondetails TD
WHERE (cc_status='V' and captureStatus = '7 day drop' and tranStat = 'success'
and TD.memID = cM.MemId)) As CountofBadPicks
, 'FS' As FavouriteSport
, capM.FirstName + ' ' + capM.LastName as FavouriteCapper
FROM coversMems cM
INNER JOIN (SELECT MAX(trandate) As LPDate, memID, Count(memId) As CntOP, SUM(playCost) As TotPur
FROM transactiondetails
WHERE (cc_status='B' and captureStatus = 'successfullyCaptured' and tranStat = 'success')
OR (cc_status='V' and captureStatus = '7 day drop' and tranStat = 'success')
GROUP BY memID
) As MPI ON MPI.memID = cM.MemId --MPI = Member Purchase Info
inner join
(

select /*top 1000*/ cM2.memID as capID, TD.memid,count(CA.whomade)as TT
from transactiondetails TD
inner join transactioninfo TI ON TI.TID = TD.TID
inner join catalog CA ON CA.CatalogID = TI.catalogID
inner join coversMems cM2 ON cM2.memId = CA.whomade
WHERE ((TD.cc_status='B' and TD.captureStatus = 'successfullyCaptured' and TD.tranStat = 'success')
OR (TD.cc_status='V' and TD.captureStatus = '7 day drop' and TD.tranStat = 'success'))
and convert(varchar(10),cM2.memID)+'_'+convert(varchar(10),TD.memid) = (
select top 1 convert(varchar(10),capID)+'_'+convert(varchar(10),memid)
from @tab
WHERE memid=TD.memID
order by tt desc)
group by cM2.memID,TD.memid
) FC on FC.memID=cm.memId
inner join (select memID,FirstName, LastName from coversMems) capM on capM.memID=FC.capID

WHERE MPI.TotPur >= @totalrevenue and convert(integer, getdate() - MPI.LPDate) >= @numdayssincelstpurchase

IF @@ERROR <>0
BEGIN
PRINT 'Error Occured Again'
END
GO

****************************ASP code that calls the sp*******

set cmdTempPur = server.CreateObject("ADODB.Command")
set ors = server.CreateObject("ADODB.Recordset")

Set conSumRep = Server.CreateObject("ADODB.Connection")
conSumRep.ConnectionString="Provider=SQLOLEDB;User ID=user;Password=pwd;Initial Catalog=dbname;Data Source=server"
conSumRep.ConnectionTimeout = 300
conSumRep.Open

cmdTempPur.ActiveConnection = conSumRep
cmdTempPur.CommandType = adCmdStoredProc
cmdTempPur.CommandText = "CE_Customer_Purchase_Info"
cmdTempPur.Prepared = True
if Request.Form("totalrevenue") = "" then totalrevenue = 0 else totalrevenue = Request.Form("totalrevenue")
cmdTempPur.Parameters("@totalrevenue") = totalrevenue
if Request.Form("numdays") = "" then reqnumdays = 0 else reqnumdays = Request.Form("numdays")
cmdTempPur.Parameters("@numdayssincelstpurchase") = reqnumdays
cmdTempPur.CommandTimeout = 300
set ors = cmdTempPur.Execute
if not ors.eof then NewCustAry = ors.getrows() else NewCustAry = ""

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-28 : 14:06:55

I think ur situation is similar to

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63652

Quick hint :
-- set nocount on

Srinika

ekellysql
Starting Member

16 Posts

Posted - 2006-03-31 : 10:53:08

thanks Srinika, that worked but now I can't get past timeout errors on the web page. I have connection timeouts set but it doesn't help. The stored proc deals with a lot of data and takes about 45 secs to run in query analyzer. Any ideas?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-31 : 11:03:52

I think u may need to deal that with IIS (or any such web server)

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources