SQL and C#: Timeout expired exception---How to fix it

[EDN Admin]

Hello All,
Ive found no useful assistance on other forum posts about this problem, and I think that Ive tried most of what I can. I keep getting the following SQL exception during execution:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.<br/>
at Sql.... etc... etc... at line#<br/>
...<br/>
...<br/>
...<br/>
etc...
Heres my code (the exception is thrown at the line in bold):
SqlConnection con = new SqlConnection(conString)<br/>
con = new SqlConnection(conString);<br/>
SqlCommand com = new SqlCommand();<br/>
com.CommandTimeout = 21600;<br/>
com = con.CreateCommand();<br/>
com.CommandType = CommandType.Text;<br/>
int row = 22000000;<br/>
com.CommandText = "SELECT v.[Latitude] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [ID] ASC) AS rowNum, [Latitude] FROM dbo.Latitudes) as v WHERE v.rowNum = @row";<br/>
com.Parameters.Add("@row", SqlDbType.VarChar).Value = row;
<br/>
con.Open();<br/>
object lat_value = com.ExecuteScalar(); <br/>
string lat_lines = Convert.ToString(lat_value);<br/>
con.Close();
Can someone help fix this problem? Each query runs through 19,000,000+ values in the database table, and this process runs overnight per attempted execution.
To try to remedy this issue, Ive tried setting the ConnectionTimeout to inordinately large sizes (right now, that is at 21600s; thats the equivalent of six hours). Ive also set the CommandTimeout to the same value (as shown above).
What could possibly be the issue here? The operational connection string helps connect to the database just fine.
Any help would be appreciated. Thank you in advance for your answers.
-AD-
<
AndrewDen

View the full article