ASP timeout - 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

SQL Server Development (2000)

ASP timeout

Author

Topic

GenerationWithoutName
Starting Member

26 Posts

Posted - 2005-08-02 : 03:37:06

Dear All,

I have situation like this :

SP :
CREATE PROCEDURE dbo.BLA2 AS
BEGIN
BEGIN TRANSACTION
Statement DML#1
IF (@@ERROR=0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
GO

I have ASP Client that Execute SP dbo.BLA2.
Somehow it Error "TimeOut Expired".
My Question is :
When it "TimeOut Expired" what happen to MY SP (dbo.BLA2)???
Is it still running, or what?

thank's all

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-02 : 04:04:17

Refer this
http://vyaskn.tripod.com/watch_your_timeouts.htm

Madhivanan

Failing to plan is Planning to fail

GenerationWithoutName
Starting Member

26 Posts

Posted - 2005-08-02 : 06:26:26

So?The answer is?
it's continue or it's rollback ? or nothing else?

DIJE

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 06:47:20

My guess would be that its still running - until such time as it can detect that the connection has gone away - which might not be until it has finished running, or it attempts to send a recordset to the client, or somesuch.

Kristen

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-02 : 07:41:33

i think ur SP was still running so you need to increase the connection time out property..

GenerationWithoutName
Starting Member

26 Posts

Posted - 2005-08-02 : 07:58:17

so it will rollback my transaction right, according to my SP example above? or not?

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-02 : 08:06:18

yes..
u need to increase the timeout madivanan had mentioned one link..
go through that.i think it will help u out from this problem..

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 08:57:44

"so it will rollback my transaction right, according to my SP example above? or not?"

I think there is a chance that it may not. My thinking is

ASP ask SQL to run the SProc

The SProc runs, but takes a long time

ASP aborts and gives user "Timeout message"

Now, depending on the connection to SQL (synchronous or asycnchronous) SQL may have no means of easily knowing that the connection is dead.

I think it it tries to return a recordset to the client it will discover the connection is gone. Maybe when it sends a RETURN value. But if it doesn't have a need to send a recordset then maybe it won't discover, and will NOT rollback the transaction.

best way would be to try it:


-- DROP TABLE dbo.TEMP_LogRunningQuery

IF EXISTS (SELECT * 
		FROM sysobjects 
		WHERE id = object_id(N'[dbo].[TEMP_SP_LogRunningQuery]') 
			AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.TEMP_SP_LogRunningQuery
END
GO
CREATE PROCEDURE dbo.TEMP_SP_LogRunningQuery
AS
IF NOT EXISTS (SELECT * from dbo.sysobjects 
	WHERE	id = object_id(N'[dbo].[TEMP_LogRunningQuery]') 
		AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.TEMP_LogRunningQuery
	(
		PK_ID		int identity(1,1) NOT NULL,
		StartTime	datetime NOT NULL,
		EndTime		datetime NULL,
		PRIMARY KEY
		(
			PK_ID
		)
	)
END
SET NOCOUNT ON

DECLARE	@PK_ID	int	-- Store IDENTITY from INSERT

INSERT INTO dbo.TEMP_LogRunningQuery
(
	StartTime
)
SELECT	[StartTime] = GetDate()

SELECT	@PK_ID = scope_identity()

WAITFOR DELAY '000:00:05'	-- '001:02:03' is 1h 2m 3s

UPDATE	U
SET	EndTime = GetDate()
FROM	dbo.TEMP_LogRunningQuery U
WHERE	PK_ID = @PK_ID
GO

-- Test rig:
exec TEMP_SP_LogRunningQuery
GO
SELECT	* FROM dbo.TEMP_LogRunningQuery ORDER BY PK_ID

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources