Connection 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

General SQL Server Forums

New to SQL Server Programming

Connection Timeout

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2009-02-27 : 09:19:48

I have created procedures which calculate the unlimited level of Binary tree. When it process connection timeout Exeption Ocurs..

plz helpme .. How to prevent Connectin Timeout. I have approax 8000 Records and procedure is to process 8000 times. and calculates their Unlimited depth.

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-27 : 09:28:56

Increase CommandTimeOut property in your code from default 30 seconds to 300 seconds (5 minutes).

E 12°55'05.63"
N 56°04'39.26"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-02-27 : 09:38:20

actualy I have set CommandTimeOut property 9999999 seconds
Coneection Timeout shows:
there are approx 50-60 ID where Procedure takes 1.3 minutes to run

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-27 : 09:45:23

Oh, an exception?

Have you checked your code for circular reference?
Or are you using nested procedures? What is nestlevel? Greater than 32?

Post the code, please.

E 12°55'05.63"
N 56°04'39.26"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-02-27 : 10:00:19

members table structure:
create table members
{
vaid varchar(8) Primary key, //Associate ID
vspid varchar(8) not null, //Sponsor ID
cplacement char(1) not null // Place as left or Right

}

Data:

vaid vspid cplacement

1 0 L

2 1 L
3 1 R

4 2 L
5 2 R

6 3 L
7 3 R
8 4 L

9 5 R
10 4 L

11 4 L
12 5 R

now i have to check that How many Level having Associate ID "1"

there is 5 level for 1

procedure:

create proc prcLeftPayment @sid varchar(9),@fromdate datetime,@endDate datetime
as
begin
if exists (select * from members where vaid=@sid)
begin

WITH CatTree (vaid, vspID,cplacement, Level)
AS
(

SELECT vaID, vspID,cplacement 1 As [Level]
FROM members m join scratch s on m.vaid=s.vat_id
join packages p on s.vpackid=p.vid
WHERE vspid=@sid and cplacement='L'

UNION ALL

SELECT C.vaid, C.vspID,C.cplacement, Level + 1
FROM members C
INNER JOIN CatTree ON
CatTree.vaid= C.vspid
join scratch s1 on c.vaid=s1.vat_id
join packages p1 on s1.vpackid=p1.vid

)
SELECT * FROM CatTree
where dtdoj between @fromdate and @enddate
and cstatus='V'
Order By vaid

end

else

begin

print 'Please check your ID'

end

end
GO

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-03-03 : 02:24:04

i m facing this problem now a days.
I haven't get any solution for it
plz hel me

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-03-03 : 02:24:30

plz help me...

Ved Prakash Jha

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-03 : 04:12:16

create proc prcLeftPayment @sid varchar(9),@fromdate datetime,@endDate datetime
as
begin
if exists (select * from members where vaid=@sid)
begin

WITH CatTree (vaid, vspID,cplacement, Level)
AS
(

SELECT vaID, vspID,cplacement 1 As [Level]
FROM members m join scratch s on m.vaid=s.vat_id
join packages p on s.vpackid=p.vid
WHERE vspid=@sid and cplacement='L'

UNION ALL

SELECT C.vaid, C.vspID,C.cplacement, Level + 1
FROM members C
INNER JOIN CatTree ON
CatTree.vaid= C.vspid
join scratch s1 on c.vaid=s1.vat_id
join packages p1 on s1.vpackid=p1.vid

)
SELECT * FROM CatTree
where dtdoj between @fromdate and @enddate
and cstatus='V'
Order By vaid
option (maxrecursion 0)

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-03 : 04:14:25

u have left ",'" near cplacement
SELECT vaID, vspID,cplacement 1 As [Level]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources