Timeout problem - Urgently need help!

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

SQL Server Development (2000)

Timeout problem - Urgently need help!

Author

Topic

iamgq
Starting Member

2 Posts

Posted - 2005-10-26 : 04:14:38

Hi,
My site has developed a intermediate timeout problem.

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Can someone tell me what could cause this intermediate problem. Is it something wrong with the query? It is definitely the problem from the query as I have 2 queries using same design and both of them develop similar problem.

Pleaee help as our site will have major advertising next month so need to fix it by then.

My server is running Win2003 on SQL 2000.

I have shown the cut-down version of the original query:

CREATE PROCEDURE dbo.sp_getmyitems
(
@CurrentPage tinyint,
@PageSize int,
@TotalRecords int OUTPUT
)
As
Set NoCount ON

Declare @FirstRec int
Declare @LastRec int
Set @FirstRec = (@CurrentPage - 1) * @PageSize
Set @LastRec = (@CurrentPage * @PageSize + 1)

Create Table #TempTable
(
LocalItemID int IDENTITY PRIMARY KEY,
ItemID int,
Title varchar(250),
)

Insert Into #TempTable
(ItemID,Title)
SELECT ItemID, Title
FROM tbl_Items

Select ItemID,Title
From #TempTable
Where LocalItemID > @FirstRec AND LocalItemID < @LastRec

Select @TotalRecords = Count(*) FROM #TempTable
GO

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-26 : 04:21:40

See if this helps you
http://vyaskn.tripod.com/watch_your_timeouts.htm

Madhivanan

Failing to plan is Planning to fail

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-26 : 05:41:58

Hi,

If I am not wrong, you using this stored procedure for pagination of records. I think here the table you trying to access is getting locked by some other process.

You can specify NO_LOCK in the statement you using to populate the #table.

So in case the table is locked, you can still read the table.

Insert Into #TempTable
(ItemID,Title)
SELECT ItemID, Title WITH (NOLOCK)
FROM tbl_Items

For more inforation about NOLOCK, please check BOL.

One of the good linl I found is http://www.developerfusion.co.uk/show/1688/4/.

Regards
Sachin Samuel

iamgq
Starting Member

2 Posts

Posted - 2005-10-26 : 06:25:36

Hi Sachin,
I will try to add (NOLOCK) into the query and see if it will fix the problem.
Your info are very much appreciated.

Regards,

Paul

quote:
Originally posted by sachinsamuel

Hi,

If I am not wrong, you using this stored procedure for pagination of records. I think here the table you trying to access is getting locked by some other process.

You can specify NO_LOCK in the statement you using to populate the #table.

So in case the table is locked, you can still read the table.

Insert Into #TempTable
(ItemID,Title)
SELECT ItemID, Title WITH (NOLOCK)
FROM tbl_Items

For more inforation about NOLOCK, please check BOL.

One of the good linl I found is http://www.developerfusion.co.uk/show/1688/4/.

Regards
Sachin Samuel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources