I keep getting a "Timeout expired" error when trying to run an update on 350,000 records using ADO.Net.
Below is the code I'm using....
Dim ConnectionString As String
ConnectionString = "Data Source=""sac-sql-001"";Initial Catalog=""Main"";Persist Security Info=""False"";Workstation ID=""SHARDIE"";Integrated Security=""SSPI"";"
Dim conn As New SqlClient.SqlConnection(ConnectionString)
conn.Open()
Dim Table As String = "header"
Dim File As String = "C:\temp\test123.txt"
Dim cmd As New SqlClient.SqlCommand("", conn)
cmd.CommandText = "Update " & Table.Trim & " Set filename = @filename, id = @id + REPLICATE('0', 10 - LEN([linenum])) + RTRIM(LTRIM([linenum]))"
cmd.Parameters.Add("@filename", File)
cmd.Parameters.Add("@id", IO.Path.GetFileName(File) & "_")
cmd.CommandTimeout = 600 '10 minutes
cmd.ExecuteNonQuery() 'Fails Here
conn.Close()
Exception Details...
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at KP.SAC.Debugging.frmDebug.Button12_Click(Object sender, EventArgs e) in C:\Source\Test\Debugging\frmDebug.vb:line 515
And here's the specs of what I'm running...
All of this is on the same LAN using WinForms.
.Net Framework v1.1 (VS .Net 2003)
SQL Server 2000 SP3a
2Gig Pentium sql server machine, 512 MB of RAM, 40 gig hard drive.
Table Info:
350,000 records
33 Fields
Fields used in the update info...
Field Data Type Length Allow Nulls Identity
id varchar 150 Yes No
filename varchar 150 Yes No
linenum int 4 No Yes, (Seed = 1, Increment = 1)
As a side note, the same update under SQL Query Analyzer works fine and takes 48 seconds to run.
Any help on this would be greatly appreciated...
Thanks in advance,
Stephen
Below is the code I'm using....
Dim ConnectionString As String
ConnectionString = "Data Source=""sac-sql-001"";Initial Catalog=""Main"";Persist Security Info=""False"";Workstation ID=""SHARDIE"";Integrated Security=""SSPI"";"
Dim conn As New SqlClient.SqlConnection(ConnectionString)
conn.Open()
Dim Table As String = "header"
Dim File As String = "C:\temp\test123.txt"
Dim cmd As New SqlClient.SqlCommand("", conn)
cmd.CommandText = "Update " & Table.Trim & " Set filename = @filename, id = @id + REPLICATE('0', 10 - LEN([linenum])) + RTRIM(LTRIM([linenum]))"
cmd.Parameters.Add("@filename", File)
cmd.Parameters.Add("@id", IO.Path.GetFileName(File) & "_")
cmd.CommandTimeout = 600 '10 minutes
cmd.ExecuteNonQuery() 'Fails Here
conn.Close()
Exception Details...
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at KP.SAC.Debugging.frmDebug.Button12_Click(Object sender, EventArgs e) in C:\Source\Test\Debugging\frmDebug.vb:line 515
And here's the specs of what I'm running...
All of this is on the same LAN using WinForms.
.Net Framework v1.1 (VS .Net 2003)
SQL Server 2000 SP3a
2Gig Pentium sql server machine, 512 MB of RAM, 40 gig hard drive.
Table Info:
350,000 records
33 Fields
Fields used in the update info...
Field Data Type Length Allow Nulls Identity
id varchar 150 Yes No
filename varchar 150 Yes No
linenum int 4 No Yes, (Seed = 1, Increment = 1)
As a side note, the same update under SQL Query Analyzer works fine and takes 48 seconds to run.
Any help on this would be greatly appreciated...
Thanks in advance,
Stephen
