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

TheWizardofInt

TheWizardofInt

Well-known member
Joined
Dec 31, 1969
Messages
333
Location
Orlando, FL
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!
 
TheWizardofInt said:
SqlClient.SqlConnection("server=1.2.3.4;Database=db;uid=sa;pwd=;")

Not exactly that, but that format
Click to expand...


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.
 
Here is the code - in the event that there is something fresh eyes will see:

Code: 
        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
 
Like Optikal said - it looks like youre 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? Im assuming youre just updating them all in a similar fashion.

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