Stored Proc errors from Web - SQL Server Forums

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)

Stored Proc errors from Web

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-04-21 : 10:14:00

Hello all,

Problem:

When I am calling the proc from web interface I am gettting errors like
Object could not be opened.
When I hard code the date values, it works just fine.

Some of the things I tried
1)
convert function for each date variable when I am passing it to the query like

between convert( smalldatetime,@ThreeMonthStart) and convert( smalldatetime,@ThreeMonthEnd)

2)
hard coded date in one place with 'set' and calculate the rest.

Nothing seems to work. May be all my ideas are wrong and I do not want to hard code. Pls. help.

stored proc as follows:

CREATE PROC spTest @ThreeMonthStart smalldatetime, @Type Char(50)
as

Declare @ThreeMonthStart smalldatetime,
@ThreeMonthEnd SmallDatetime
,@MonthlyStart SmallDatetime
,@MonthlyEnd SmallDatetime

SET @ThreeMonthStart =convert (smalldatetime,'11/01/2003')
SET @ThreeMonthEnd=DATEADD(d,-1,DATEADD(m,3,@ThreeMonthStart))
SET @MonthlyStart=DATEADD(d,1,@ThreeMonthEnd)
SET @MonthlyEnd=DATEADD(d,-1,DATEADD(m,1,@MonthlyStart))

SELECT m.Monthly,tm.ThreeMonAvg, tm.name
FROM
(SELECT count(SCount)/3 as threeMonAvg, name,description
from I
where SStartDate between @ThreeMonthStart and @ThreeMonthEnd -- '11/01/2003' and '01/31/2004'
group by i.name,i.description
) as tm
JOIN
(select count(SCount) as Monthly, name,description
from I
where SStartDate between @MonthlyStart and @MonthlyEnd -- '02/01/2004' and '02/29/2004' --@MonthlyEnd
group by i.name,i.description
) as m
ON tm.Name=m.Name
and tm.Description=m.Description
and tm.Description=@MediaType
GO

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-04-21 : 13:34:10

Never mind guys, I found the problem.
I had one my debugging print statements in the procedure.

Thanks to all who read.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources