C
CuriousGeorge
I have a very simple .Net 1.1 app that I'm writing to upgrade our
applications database for a new version. This app has a pretty tight loop
where I'm using a SqlDataReader to walk through all records in a fairly
large table, manipulate the data, then write one of the columns back out.
The problem I'm having is that after a certain period of time I'm running
into a System.Data.SqlClient.SqlException specifically:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.
The odd thing to me is that the error if happening part way through, after a
couple of thousand records have been processed, and on the Read() call of
the SqlDataReader. Through the logging capabilites in my app I can see each
iteration through the loop and I'm getting about 25 iterations per second.
Why would a read from the SqlDataReader time out like this mid-way? It just
doesn't make sense to me why it would be looping fine then suddenly it can't
read from the datareader? There is no time limit on how long I can have a
reader open is there? It can't be a physical communication issue as it
happens every time, and usually after about the same number of iterations.
SQLServer is running on my machine, as is the application hitting it. When
I start getting the timeouts there is no disk activity and almost no CPU
usage. I've tried increasing the CommandTimeout on the SqlCommand to no
avail (it just makes it take longer to fail but still fails on the same
basic iteration).
Here is a snippet of my logging. You can see it iterating and then within a
split second it fails on a timeout. Doesn't make sense to me.
2/13/2006 3:08:44 PM 3068 - ExtDocStat:
p-0aaba9ff-cd53-4656-b866-23e8362bcd2e
2/13/2006 3:08:44 PM 3069 - ExtDocStat:
p-37e842e5-c86a-4baf-a529-23eb82999d2c
2/13/2006 3:08:44 PM 3070 - ExtDocStat:
p-2df6cfea-04c5-467f-ae87-23ed60232a18
2/13/2006 3:08:44 PM Unexpected error in ExtractDocumentStatus:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.
Below is the code I'm using, simplified to show the important info (no
comments and reduced error handling). Am I doing something basic wrong
here?
-Brett-
public void ExtractDocumentStatus(string strConn)
{
SqlConnection sqlConnRead = new SqlConnection(strConn);
SqlConnection sqlConnWrite = new SqlConnection(strConn);
try
{
sqlConnRead.Open();
sqlConnWrite.Open();
SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Portfolio",
sqlConnRead);
SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
int ordID = sqlRdr.GetOrdinal("PortID");
while (sqlRdr.Read())
{
SqlGuid id = sqlRdr.GetSqlGuid(ordID);
string sql = string.format("UPDATE DocData SET Stat='OK' WHERE
Pid='{0}'", id);
SqlCommand sqlCmd2 = new SqlCommand(sql, sqlConnWrite);
sqlCmd2.ExecuteNonQuery();
}
sqlRdr.Close();
}
finally
{
sqlConnRead.Close();
sqlConnWrite.Close();
}
}
applications database for a new version. This app has a pretty tight loop
where I'm using a SqlDataReader to walk through all records in a fairly
large table, manipulate the data, then write one of the columns back out.
The problem I'm having is that after a certain period of time I'm running
into a System.Data.SqlClient.SqlException specifically:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.
The odd thing to me is that the error if happening part way through, after a
couple of thousand records have been processed, and on the Read() call of
the SqlDataReader. Through the logging capabilites in my app I can see each
iteration through the loop and I'm getting about 25 iterations per second.
Why would a read from the SqlDataReader time out like this mid-way? It just
doesn't make sense to me why it would be looping fine then suddenly it can't
read from the datareader? There is no time limit on how long I can have a
reader open is there? It can't be a physical communication issue as it
happens every time, and usually after about the same number of iterations.
SQLServer is running on my machine, as is the application hitting it. When
I start getting the timeouts there is no disk activity and almost no CPU
usage. I've tried increasing the CommandTimeout on the SqlCommand to no
avail (it just makes it take longer to fail but still fails on the same
basic iteration).
Here is a snippet of my logging. You can see it iterating and then within a
split second it fails on a timeout. Doesn't make sense to me.
2/13/2006 3:08:44 PM 3068 - ExtDocStat:
p-0aaba9ff-cd53-4656-b866-23e8362bcd2e
2/13/2006 3:08:44 PM 3069 - ExtDocStat:
p-37e842e5-c86a-4baf-a529-23eb82999d2c
2/13/2006 3:08:44 PM 3070 - ExtDocStat:
p-2df6cfea-04c5-467f-ae87-23ed60232a18
2/13/2006 3:08:44 PM Unexpected error in ExtractDocumentStatus:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.
Below is the code I'm using, simplified to show the important info (no
comments and reduced error handling). Am I doing something basic wrong
here?
-Brett-
public void ExtractDocumentStatus(string strConn)
{
SqlConnection sqlConnRead = new SqlConnection(strConn);
SqlConnection sqlConnWrite = new SqlConnection(strConn);
try
{
sqlConnRead.Open();
sqlConnWrite.Open();
SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Portfolio",
sqlConnRead);
SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
int ordID = sqlRdr.GetOrdinal("PortID");
while (sqlRdr.Read())
{
SqlGuid id = sqlRdr.GetSqlGuid(ordID);
string sql = string.format("UPDATE DocData SET Stat='OK' WHERE
Pid='{0}'", id);
SqlCommand sqlCmd2 = new SqlCommand(sql, sqlConnWrite);
sqlCmd2.ExecuteNonQuery();
}
sqlRdr.Close();
}
finally
{
sqlConnRead.Close();
sqlConnWrite.Close();
}
}
