Timeout expired on SQL Report - 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

General SQL Server Forums

New to SQL Server Programming

Timeout expired on SQL Report

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-08-25 : 11:40:09

I have a webpage running a SQL Report. It's using the ReportViewer control on the page and calling a stored procedure to get the data.
But it gives me "Timeout expired....server is not responding" msg. I searched and tried increasing Query timeout, connection timeout, session timeout all those but still nothing. The reason I did that is the tables have lot of data in them (parent 6+ million and the child 54+ million). I tested the a similar query in Query analyzer and it's taking time like 30 sec - 1 and 1/2 m.
Am I screwed? Here's the query:
SELECT Hour = 'Hour' + STR(CASE TSAMPLE_START_TIME
WHEN '0' THEN 1
WHEN '100' THEN 2
WHEN '200' THEN 3
WHEN '300' THEN 4
WHEN '400' THEN 5
WHEN '500' THEN 6
WHEN '600' THEN 7
WHEN '700' THEN 8
WHEN '800' THEN 9
WHEN '900' THEN 10
WHEN '1000' THEN 11
WHEN '1100' THEN 12
WHEN '1200' THEN 13
WHEN '1300' THEN 14
WHEN '1400' THEN 15
WHEN '1500' THEN 16
WHEN '1600' THEN 17
WHEN '1700' THEN 18
WHEN '1800' THEN 19
WHEN '1900' THEN 20
WHEN '2000' THEN 21
WHEN '2100' THEN 22
WHEN '2200' THEN 23
WHEN '2300' THEN 24
END),
TDATA_UNIT, TDATA_VALUE FROM STATION_DATA_TEMP, SAMPLE_DATA_TEMP
WHERE TSAMPLE_NUM = TDATA_SAMPLE_NUM AND TDATA_PARAMETER = '42101'
AND TSAMPLE_STATION = 1029 AND TSAMPLE_START_DATE = '1-Jan-1996'

Thanks for any help.

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-08-25 : 13:07:55

I used SQL Server tuning tool and found I had one index and statistics missing, created them and now the query improved significantly...like taking only few seconds. But I still have the timeout problem.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 13:56:40

Like google tells there are many people having similar problems.
No one has this ONE SOLUTION to the problem.
There are lot of things that can happen / be done / tested:
-- set nocount on
-- looking for parameter sniffing
-- looking for concurrent running processes
-- try another port for report server
-- look difference in formatted date values in tables and parms
-- ...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources