SQL Server Timeout Expired Error

[frmorris]

I am getting the following error when I try to run a report in my application. The application is in asp with a Sql Server 2000 database.

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/reports/reportDisplay.asp, line 417

This is the query that is causing the timeout error when I try to run the report.

strQuery = SELECT d.District, ISNULL((SELECT Count(distinct clientid) FROM N_Fact a, ClientReferredTo_LT b, mastervals c

WHERE c.ID = b.WasReferralMade_MID AND a.ID = b.ClientID AND a.DistrictID = d.ID AND (((a.closureDate >= '01/01/2006' OR a.ClosureDate is Null) AND ( a.enrollmentdate <= '7/13/2006' )) ) AND a.hsclient_mid = 25 AND b.WasReferralMade_MID = 402 GROUP BY b.WasReferralMade_MID, c.Mastervalue ), 0) as 'Clients with completed referrals', (SELECT Count(distinct clientid) FROM N_Fact a, ClientReferredTo_LT b, mastervals c WHERE c.ID = b.WasReferralMade_MID AND a.ID = b.ClientID AND a.DistrictID = d.ID AND (((a.closureDate >= '01/01/2006' OR a.ClosureDate is Null) AND ( a.enrollmentdate <= '7/13/2006' )) ) AND a.hsclient_mid = 25 AND b.WasReferralMade_MID <> 401 ) as 'Total clients with referrals' FROM Districts d WHERE (SELECT Count(distinct clientid) FROM N_Fact a, ClientReferredTo_LT b, mastervals c WHERE c.ID = b.WasReferralMade_MID AND a.ID = b.ClientID AND a.DistrictID = d.ID AND(((a.closureDate >= '01/01/2006' OR a.ClosureDate is Null) AND ( a.enrollmentdate <= '7/13/2006' )) ) AND a.hsclient_mid = 25 AND b.WasReferralMade_MID <> 401) > 0

It is greatly appreciated if someone could help me rewrite this query. Thanks.

 

[Qik3Coder]

1. How long does this query take to run in QueryAnalyzer/SSMS?
2. You need to look at this FAQ: faq183-4785

3. You can increase the timeout in the command object before you call "get". this will let the script run longer, before it times-out.

4. To make both our lives easier, could you post the ASP code that you are building this query with, because you are going to need some parameters added, because i am assuming you are doing this as a standard string concat.

Thanks,

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[frmorris]

Thanks Qik3Coder for all your help. My problem has been resolved.