Getting a 404/401 on a long running report

Greetings.
I have a report that I am running through ASP.Net with the rs.render method.
When I have a longer date range, the execution time of the query gets longer
and I start to get funky errors. Namely, at around 3 minutes, I get
prompted for network credentials by the standard IE prompt. It doesn't
accept what they are, and after a few times, it takes me to a 404 saying that
the page can't be found. If I try to cancel the prompt, I get a 401 that I
don't have access. I'm not sure what else to look at here. This report has
worked fine in the past, and there are no problems if I just scale back the
date range.
I have the render make a pdf file in a virtual directory on my web server
and then my postback page has a client script inserted to open a window
showing that pdf file.
Anyone have any ideas?
Dale.I fixed it.
It turns out that the HTTP connection timeout settings needed to be
increased a little bit to allow the report to complete. The default of 120
seconds had worked fine in IIS 5 on Windows 2000 Server, but the same value
in IIS 6 on Windows 2003 Server didn't fare too well.
Also, I wound up re-writing the stored proc for the report. The original
author of it had cursors and correlated subqueries doing things they were
never intended to do. Execution time is now 33s instead of 10m15s.
:-)
"Dale M." wrote:
> Greetings.
> I have a report that I am running through ASP.Net with the rs.render method.
> When I have a longer date range, the execution time of the query gets longer
> and I start to get funky errors. Namely, at around 3 minutes, I get
> prompted for network credentials by the standard IE prompt. It doesn't
> accept what they are, and after a few times, it takes me to a 404 saying that
> the page can't be found. If I try to cancel the prompt, I get a 401 that I
> don't have access. I'm not sure what else to look at here. This report has
> worked fine in the past, and there are no problems if I just scale back the
> date range.
> I have the render make a pdf file in a virtual directory on my web server
> and then my postback page has a client script inserted to open a window
> showing that pdf file.
> Anyone have any ideas?
> Dale.

getdate() not returning a value

I have a strange problem occurring when I am using getdate() in a stored proc. I want to get some data from a table within a date range using getdate.
I have a begin and end dates on a table and want to retrieve a guid and some other information based on the current date. So, wherever today's date falls between the begin date and the end date, I want the information from that row.

For example,

select * from polldates
where (pollbegindate >= getdate() and pollenddate <= getdate())

This works fine Monday through Saturday. I get a value returned from getdate() correctly and am able to retrieve the information that I need. However, on Sunday, getdate returns nothing when I run the stored procedure. Any clues? Am I just crazy or has anyone else seen this type of thing happen?

Any help would be greatly appreciated!I doubt very much that GetDate() isn't returning a value. Your query may not be returning rows, but I'm very sure that GetDate() is returning a value.

-PatP|||If you are sure that get date is returning a correct value but I am not getting anything back from my query can you suggest how to improve the query?

For example, the begin date is 9/5/04 and the end date is 9/11/04.

Thanks!|||Is it safe to assume pollbegindate and pollenddate are datetime datatypes in the table? Please post the enitre proc. There may be another problem.|||I doubt very much that GetDate() isn't returning a value. Your query may not be returning rows, but I'm very sure that GetDate() is returning a value.

-PatP

Well that was CERTAINLY helpful...

Dude

Do SELECT GetDate()...what do you see?

Ahh microseconds...

USE DATEDIFF

But the logic doesn't make sense...

You want all begin dates that are today and greater but all end dates that are less that or equal today...which means...

And day where the start and end are equal and it's TODAY

Johhny...tell him what he's won.....|||Maybe we all need to read. Now I feel like an idiot (well, I almost always feel like an idiot, but that's another matter).SELECT *
FROM polldates
WHERE pollbegindate <= getdate()
AND pollenddate >= getdate()The previous code was looking for rows where the begindate was greater than the enddate!

-PatP|||Even with the screwed up logic why does it return records everyday but Sunday?|||Me no know.

Without seeing the real query and the underlying data, I can offer a gazillion guesses, but no hard facts.

-PatP

get todays date and a certain time

Hi Everyone,

I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Thanks in advance,
AnnaYou can use DATEADD, for example:

SELECT DATEADD(hour,14,CONVERT(CHAR(10),getdate(),102))

Razvan

AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

Hi Everyone,
>
I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?
>
Thanks in advance,
Anna

|||On 27 Sep 2006 11:36:59 -0700, AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

>Hi Everyone,
>
>I am trying to write something to give me back all the data for a
>sertain time range for today.
>So for example: I need to get all records where change_date is <= today
>2pm and today at 8pm.
>I know i can get just the date for today by using
>CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Hi Anna,

Use either

CONVERT(datetime, CONVERT(CHAR(10), getdate(), 126) + 'T14:00:00')

or

DATEADD(day, DATEDIFF(day, 0, getdate()), '14:00:00')

to get current date with a time of 2PM.

--
Hugo Kornelis, SQL Server MVP

get the row counts for each day going back to 6 months (was "query help")

I have a proc to get the rowcounts for the given date range.
I have to get the row counts for each day going back to 6 months on the table.

With this proc i can get one day's row couts.. i need to loop through for all dates.

Please can someone get me the code for this.

create proc p_rowcounts

@.Date1 datetime,
@.Date2 datetime

SELECT
count (*) as 'Number of Rows', @.Date1 as Date
FROM
Table1 (nolock)
WHERE ModifyTime >= @.Date1 and ModifyTime < @.Date2

thanks for the help.I'd do it as:CREATE PROC p_rowcounts
@.Date1 datetime = NULL
, @.Date2 datetime = NULL
AS

IF @.Date1 IS NULL SET @.Date1 = GetDate()
IF @.Date2 IS NULL SET @.Date2 = DateAdd(month, -6, Convert(CHAR(10), @.Date1, 121))

SELECT
Count (*) AS 'Number of Rows'
, Convert(DATETIME, Convert(CHAR(10), ModifyTime, 121)) AS Date
FROM Table1 (nolock)
WHERE ModifyTime BETWEEN @.Date2 AND @.Date1
GROUP BY Convert(CHAR(10), ModifyTime, 121)

RETURN-PatP|||pat, i think sskris wants one count per date in the range|||That query ought to give one count per day in the range. I think you're hinting that you'd like to see rows with zeros for a count for days with no data, which I see as wasteful and poor practice.

If you have code that relies on zeros, you can certainly go to added trouble to make the zeros appear, but in my mind you'd be much better off to fix the code instead of writing SQL to cater to the problems in it.

-PatP

get number of consecutive numbers

SQL SERVER 2000
Hello
I have a table with a field like this
4
5
6
10
11
15
I want to get a result set of consecutive numbers like this (start range -
end range)
4 6
10 11
15 15
Thanks in advance for your help
--
GilI'm not a big fan of converting rows to columns.
Someone might jump in and give you that solution but in the meantime, try
this:
create table #test (num int)
insert into #test values (4)
insert into #test values (5)
insert into #test values (6)
insert into #test values (10)
insert into #test values (11)
insert into #test values (15)
select t.num from #test t where not exists(select * from #test where num =
t.num-1)
Union all
select t.num from #test t where not exists(select * from #test where num =
t.num+1)
order by num
drop table #test
This will return:
4
6
10
11
15
15
Loop through the recordset in your client application to build your start
and end ranges.
Test this solution first though.
"Gil" <Gil@.discussions.microsoft.com> wrote in message
news:95EED79E-9614-44DD-A808-A4F54D186BC6@.microsoft.com...
> SQL SERVER 2000
> Hello
> I have a table with a field like this
> 4
> 5
> 6
> 10
> 11
> 15
> I want to get a result set of consecutive numbers like this (start range -
> end range)
> 4 6
> 10 11
> 15 15
> Thanks in advance for your help
> --
> Gil|||HI
Try this approch ...
SELECT id,
(SELECT TOP 1 id FROM Table1 CT Where CT.id > MT.id)
FROM TAble1 MT
Thanks
___________
"Gil" wrote:

> SQL SERVER 2000
> Hello
> I have a table with a field like this
> 4
> 5
> 6
> 10
> 11
> 15
> I want to get a result set of consecutive numbers like this (start range -
> end range)
> 4 6
> 10 11
> 15 15
> Thanks in advance for your help
> --
> Gil|||create table #test (num int)
insert into #test values (4)
insert into #test values (5)
insert into #test values (6)
insert into #test values (10)
insert into #test values (11)
insert into #test values (15)
select max(n_from), n_to from
(select t.num n_from from #test t where not exists(select * from #test
where num =
t.num-1))starts
join
(select t.num n_to from #test t where not exists(select * from #test
where num =
t.num+1) )ends
on n_from < n_to
group by n_to
order by n_to
n_to
-- --
4 6
10 11
10 15
(3 row(s) affected)
drop table #test|||"Akbar khan is a Senior Database develope"
< AkbarkhanisaSeniorDatabasedevelope@.discu
ssions.microsoft.com> wrote in
message news:2E6ED1EA-673C-46C9-9FBC-4F54D3A86E8E@.microsoft.com...
> HI
> Try this approch ...
> SELECT id,
> (SELECT TOP 1 id FROM Table1 CT Where CT.id > MT.id)
> FROM TAble1 MT
> Thanks
Returns:
4 5
5 6
6 10
10 11
11 15
15 NULL
This is not what the poster wanted.|||"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1127410743.315339.181750@.g43g2000cwa.googlegroups.com...
> create table #test (num int)
> insert into #test values (4)
> insert into #test values (5)
> insert into #test values (6)
> insert into #test values (10)
> insert into #test values (11)
> insert into #test values (15)
> select max(n_from), n_to from
> (select t.num n_from from #test t where not exists(select * from #test
> where num =
> t.num-1))starts
> join
> (select t.num n_to from #test t where not exists(select * from #test
> where num =
> t.num+1) )ends
> on n_from < n_to
> group by n_to
> order by n_to
>
> n_to
> -- --
> 4 6
> 10 11
> 10 15
> (3 row(s) affected)
> drop table #test
>
Not what the poster wanted:
4 6
10 11
15 15|||yeah right, should be
on n_from <= n_to
instead of
on n_from < n_to
here you go
select max(n_from), n_to from
(select t.num n_from from #test t where not exists(select * from #test
where num =
t.num-1))starts
join
(select t.num n_to from #test t where not exists(select * from #test
where num =
t.num+1) )ends
on n_from <= n_to
group by n_to
order by n_to
n_to
-- --
4 6
10 11
15 15
(3 row(s) affected)|||Here's one without a union:
select t1.num as [start],
(select top 1 t3.num from #test t3 where t3.num >= t1.num and not
exists(select t4.num from #test t4 where t4.num = t3.num+1) order by t3.num)
as [end]
from #test t1 where not exists(select t2.num from #test t2 where t2.num =
t1.num-1)
order by [start]
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1127413636.304427.70620@.g44g2000cwa.googlegroups.com...
> yeah right, should be
> on n_from <= n_to
> instead of
> on n_from < n_to
> here you go
> select max(n_from), n_to from
> (select t.num n_from from #test t where not exists(select * from #test
> where num =
> t.num-1))starts
> join
> (select t.num n_to from #test t where not exists(select * from #test
> where num =
> t.num+1) )ends
> on n_from <= n_to
> group by n_to
> order by n_to
> n_to
> -- --
> 4 6
> 10 11
> 15 15
> (3 row(s) affected)
>