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 , 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

David
When you connect to SQL Server what is the value you specify for timeout? I
think it is time to start looking at your query fo optimizing
Run the query with SET STATISTICS IO ON and SET STATISTICS PROFILE ON and
put in here ( if you can)

> and isnull(TK_Timesheet.SupervisorApproved, 0) > 0
> and isnull(TK_Timesheet.BMApproved, 0) > 0

Cooud be above to re-write as?

and TK_Timesheet.SupervisorApproved IS NOT NULL
and TK_Timesheet.BMApproved IS NOT NULL


> (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.


Can you show an example?



"David" <david.colliver.NEWS@revilloc.REMOVETHIS.com> wrote in message
news:%23at6$9BEJHA.4304@TK2MSFTNGP02.phx.gbl...
> 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 , 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
>

When you run a web application against a SQL Server database process, there
are 3 types of timeout:

1. connection timeout: the timeout for application to try to establish
connection the target database, if the connection cannot be created in given
timespan, you get connection timeout;
2. command timeout: in .NET app, the actual data manipulation in database is
called through SqlCommand, which has a default timeout, meaning, when
execution starts, it wait for given timespan for the data manipulation in
database to be completed. If not, then time out
3. website response timeout: a request sends to website, and waits for the
website to process the request do be done within given timespan (default to
120 sec)

It sounds you get command timeout. Firstly, you can extend commad timeout
(default is 30sec, I believe) by set SqlCommand.CommandTimeout property to
longer time. But more importantly, you need to optimize your SQL process.
You only extend Command Timeout when you believe you have done everything to
optimize the SQL process.

"David" <david.colliver.NEWS@revilloc.REMOVETHIS.com> wrote in message
news:%23at6$9BEJHA.4304@TK2MSFTNGP02.phx.gbl...
> 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 , 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
>