Altering 100's of 1000's of rows in MS SQL

[TheWizardofInt]

I am attempting to alter about 400,000 rows in an MS SQL database

 

Normally, I would make my connection, use the dataadapter to move the data to a datatable, use the datatable to change the data, then put the data back with a SQL insert statement

 

I am getting a 'MaxRows' error which I can only read from the stack trace. The actual error message is " ", which helps a whole heck of a lot.

 

I am assuming there is a max row limit for SQL query like this, or for datatables (I know I have moved larger amounts of data with MySQL)

 

Should I be using the reader for this, or is there some setting in my command (I already set the timeout to 0) I should be making

 

Thanks!

[penfold69]

Are you using the ODBC connector, or the MySQL Connector/Net Gamma 1.0.4 ?

 

B.

[TheWizardofInt]

Are you using the ODBC connector, or the MySQL Connector/Net Gamma 1.0.4 ?

 

B.

 

SqlClient.SqlConnection("server=1.2.3.4;Database=db;uid=sa;pwd=;")

 

Not exactly that, but that format

[Optikal]

SqlClient.SqlConnection("server=1.2.3.4;Database=db;uid=sa;pwd=;")

 

Not exactly that, but that format

 

 

You should be able to download that many rows easily. I have code that downloads several hundred thousand records and it works fine. Try creating a simple test project that just connects and downloads the table to see if it works, if it does then it must be something else you are doing.

 

PS - If you just want to update the data, by far the most efficient way would just be to send an UPDATE sql query via ExecuteNonQuery(), rather than having to download and re-upload the data. This may or may not be possible depending on your situation.

[TheWizardofInt]

Here is the code - in the event that there is something fresh eyes will see:

 

       Dim oConn As SqlClient.SqlConnection
       Dim cmd As New SqlClient.SqlCommand
       Dim da As SqlClient.SqlDataAdapter
       Dim dt As New DataTable
       Dim i As Integer

       sSQL = "SELECT * FROM [imports] WHERE OWNER IS NOT NULL"

       Try
           oConn = New SqlClient.SqlConnection("Server=1.2.3.4;" & _
                               ";DataBase=ResDev;uid=sa;" & _
                               ";pwd=res;")
           oConn.Open()
           cmd.CommandText = sSQL
           cmd.Connection = oConn
           cmd.CommandTimeout = 0                  'allow for the timeout problem
           da = New SqlClient.SqlDataAdapter(cmd)
           da.Fill(dt)

 

The error message is " "

The stack trace mentions max datarow

The connection is via the Internet, the router is configured port 1443 in and out, which is the default

 

Thanks for your help and time, guys

[penfold69]

My confusion - I thought it was a MySQL database, not an MSSQL database :D

[mocella]

Like Optikal said - it looks like you're pulling the majority of this table (or have a known subset of data you wish to update).

 

Why not create a stored procedure that can pass arguments in to update the necessary columns with the info? I'm assuming you're just updating them all in a similar fashion.

 

This way, you're saving a ton of overhead by just keeping the work on the database side and not passing it over the network back and forth.