Asked By David
06-Sep-08 08:52 AM
Hi all,
I am having problems with SQL Server on a clients website...
I know this email is quite long. I really need to sort out the failure
rather than anything else here. I add all other info in case it is relevant.
The site is built with C#. When I open a connection, I always close it. (I
specifically develop this way, and I have since double checked and triple
checked this is happening) I am connecting to a SQL 2K5 database.
Occassionally, I get a timeout whilst querying the DB. It only happens in
certain scenarios and does NOT happen all the time. The query is doing a lot
of summing of fields.
I am watching the task manager at the same time.
When it fails, the task manager for SQL goes to 99%. The mem usage is
something like 169MB. When the page returns with the fail, the cpu usage
drops.
However, when I run the same query inside SQL Management studio, it looks
like the same thing happens, i.e. the memory usage increases. (I have not
yet tried it when the page fails.)
Any ideas or things I can try would be appreciated.
On another note, in .NET, you can close connections, but not datasets,
datatables, DataAdapters or anything. All my SqlDataReaders are also closed
off.
If it is of any help, my query is...
set DateFormat dmy;
select
TK_Timesheet.CostCenter, TK_Timesheet.UserID as Employee,
Surname + ', ' + FirstName as Name,
sum(cast(ExpensesAmount as Money)) as TotalExpenses,
sum(cast(RadiusTimeMiles as int)) as RadiusMiles,
sum(cast(NightRatePayment as Money)) as NightRate
,SUM(DatePart(HOUR, (cast(HoursWorked as DateTime)))*3600+DatePart(MINUTE,
(cast(HoursWorked as DateTime)))*60) / 3600.00 as Hours
, StandingDataChange
from TK_JobDetail
left join TK_Timesheet on TK_JobDetail.TimesheetID =
TK_Timesheet.TimesheetID and TSID in(select max(TSID) from TK_Timesheet
group by TimesheetID)
left join TK_UserDetail on TK_Timesheet.UserID =
TK_UserDetail.EmployeeNumber
where TK_Timesheet.UserID is not null
and isnull(TK_Timesheet.SupervisorApproved, 0) > 0
and isnull(TK_Timesheet.BMApproved, 0) > 0
and (RowDeleted is null or RowDeleted = '0' or RowDeleted = '')
and ChildRow = 0
and SubmitWeek = @SubmitWeek and SubmitYearPeriod = @SubmitYear
group by
TK_Timesheet.UserID, Surname + ', ' + FirstName
, StandingDataChange
, TK_Timesheet.CostCenter, EmployeeNumber
order by TK_Timesheet.CostCenter, EmployeeNumber
(On another note, I was having problems with casting the HoursWorked as
DateTime. However, when checking the database, I could not find any invalid
hours. (In this case, they are stored as varchar). However, when I put " AND
isDate(HoursWorked) = 1 " in the where clause, the SQL worked. What is
really odd is that this error only happened when I filtered by having a
costcenter (in the where clause), but across ALL cost centers (no cost
center filter), it worked quite happily (this may be related to the other
problem above). (This is without " or RowDeleted = '' " in the above
statement)
--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available