Error : Timeout expired

I am developing a .NET web application. We are using SQL 2000. I am getting
this Exception message while executing a query.
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
This query fails when I execute it for the first time. After ttat the query
works most of the times, but I still receive this message sometimes. I am
unable to find out why this occurs.
This is query that is causing the timeout exception
private const string SQL_UNASSIGN_TRAINING = "UPDATE tblAssignedTraining
SET IsWaivedOff = 1 WHERE TrainingId = @.TrainingId AND UserId = @.UserId AND
Status <> 4";
The following is the database design for the tblAssignedTraining table.
Status - Integer (can have value 1, 2, 3 or 4)
TrainingId - Integer
UserId - varchar(50)
IsWaivedOff - Bit
Any help would be highly appreciated.
TIA,
GaryWhen posting table structure, please post DDL (CREATE TABLE statements)
including relevant constraints and indexes.
To prevent a table scan, you'll need an index (or primary key or unique
constraint) on TrainingId and/or UserId. This will help both
performance and concurrency.
You can set the CommandTimeout property of your command object to
override the default (which I believe is 30 seconds). Also, there is a
bug in the .NET Framework 1.0 in which the timeout setting was not
honored under certain conditions. This was corrected in .NET Framework
1.0 SP2 and does not occur with .NET Framework 1.1.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Gary" <msnews@.microsoft.com> wrote in message
news:OcOQv6NXDHA.212@.TK2MSFTNGP12.phx.gbl...
> I am developing a .NET web application. We are using SQL 2000. I am
getting
> this Exception message while executing a query.
> "Timeout expired. The timeout period elapsed prior to completion of
the
> operation or the server is not responding."
> This query fails when I execute it for the first time. After ttat the
query
> works most of the times, but I still receive this message sometimes. I
am
> unable to find out why this occurs.
> This is query that is causing the timeout exception
> private const string SQL_UNASSIGN_TRAINING = "UPDATE
tblAssignedTraining
> SET IsWaivedOff = 1 WHERE TrainingId = @.TrainingId AND UserId =@.UserId AND
> Status <> 4";
> The following is the database design for the tblAssignedTraining
table.
> Status - Integer (can have value 1, 2, 3 or 4)
> TrainingId - Integer
> UserId - varchar(50)
> IsWaivedOff - Bit
> Any help would be highly appreciated.
> TIA,
> Gary
>
>|||You should have an index on one of the columns to ensure the query executes
fast...
"Gary" <msnews@.microsoft.com> wrote in message
news:OcOQv6NXDHA.212@.TK2MSFTNGP12.phx.gbl...
> I am developing a .NET web application. We are using SQL 2000. I am
getting
> this Exception message while executing a query.
> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> This query fails when I execute it for the first time. After ttat the
query
> works most of the times, but I still receive this message sometimes. I am
> unable to find out why this occurs.
> This is query that is causing the timeout exception
> private const string SQL_UNASSIGN_TRAINING = "UPDATE tblAssignedTraining
> SET IsWaivedOff = 1 WHERE TrainingId = @.TrainingId AND UserId = @.UserId
AND
> Status <> 4";
> The following is the database design for the tblAssignedTraining table.
> Status - Integer (can have value 1, 2, 3 or 4)
> TrainingId - Integer
> UserId - varchar(50)
> IsWaivedOff - Bit
> Any help would be highly appreciated.
> TIA,
> Gary
>
>