The connection is dead

[cj]

I left a program running Thursday when I left for the holiday. It was
filling a dataset with just over 3 million records from a table across a
VPN connection. Estimates I made from other large, but not this large,
tables suggested it would take approximately 18 hours to run. I just
checked in on it and it had failed in the fill command. The error
message it gave me was the "connection is dead" Given the length of
time it was to take I don't doubt something happened but nothing should
have happened. Is there anything I can do to make the odbcadapter.fill
less likely to have these problems?

 

[m.posseth]

maybe a glitch in the line ???


i can`t inmagine why you would like to transfer such a hughe amount of data
, i work also with databases that contain millions of records however i
never encountered a situation when i needed to transfer such amounts in a
dataset

regards

Michel Posseth [MCP]

 

[Cor Ligthert [MVP]]

cj,

I think this should be normal, I would set the connection.timeout in this
rare case to zero, which means infinity. (Do not do that normally)

http://msdn.microsoft.com/library/d...tsqlconnectionclassconnectiontimeouttopic.asp

Cor

 

[Ken Tucker [MVP]]

cj,

I dont think I would want to load 3 million records at once. I
would load them in blocks. The dataadapter's fill method that has an
overload which allows you to specify the start record and number of records
to load. Here is a simple example that loads data in blocks from an access
database. It will work with the odbcdataadapter also. I did not include
this in the sample but you also could show a progress bar show your status.
Try something like this.

Dim da As OleDbDataAdapter
Dim conn As OleDbConnection
Dim ds As New DataSet
Dim strConn As String
Dim cmd As OleDbCommand
Dim x, numRec As Long

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
strConn &= "Data Source = c:\Northwind.mdb;"

conn = New OleDbConnection(strConn)
cmd = New OleDbCommand("Select count(ProductName) From Products",
conn)
Try
da = New OleDbDataAdapter("Select * from Products", conn)

conn.Open()

numRec = CLng(cmd.ExecuteScalar)
conn.Close()

For x = 0 To numRec Step 10
da.Fill(ds, x, 10, "Products")
Next
Catch ex As Exception
Trace.WriteLine(ex.ToString)
End Try

http://msdn.microsoft.com/library/d...temdatacommondbdataadapterclassfilltopic6.asp

Ken

 

[cj]

I'm moving all the tables in a remote informix database to a local sql
server database. Fill via the odbcadapter and update with the
sqladapter was the best way I could come up with. Don't even go there.
You were about to tell me to use the databases migration tools weren't
you --can't--don't have time to explain.

Still there's a nice feeling knowing that the computer is spending a lot
of time on this project especially after all the work it's put me
through. Let it work over Christmas. Otherwise it'd be wasted
processing potential anyway.

maybe a glitch in the line ???


i can`t inmagine why you would like to transfer such a hughe amount of data
, i work also with databases that contain millions of records however i
never encountered a situation when i needed to transfer such amounts in a
dataset

regards

Michel Posseth [MCP]

I left a program running Thursday when I left for the holiday. It was
filling a dataset with just over 3 million records from a table across a
VPN connection. Estimates I made from other large, but not this large,
tables suggested it would take approximately 18 hours to run. I just
checked in on it and it had failed in the fill command. The error message
it gave me was the "connection is dead" Given the length of time it was to
take I don't doubt something happened but nothing should have happened. Is
there anything I can do to make the odbcadapter.fill less likely to have
these problems?

 

[cj]

Will give it a try. Thanks!

 

[cj]

You've got a valid point. One I considered but I decided to try all
records at once anyway. Your suggestion is my backup plan. I do wish
that the data table allowed me to specify a start and # of records to
fill like the dataset--but it doesn't. I'm using a datatable at the
moment but it's not hard to change it to using a dataset. That is the
backup plan. Thanks for the input.

 

[m.posseth]

You were about to tell me to use the databases migration tools weren't

you --can't

Nope ...

I have been there ,, however in my case it was a 7,5 gigabyte mysql dump
database that i needed to import in SQL server 2000
tryed everything to automate this process ,,,, but in the end i succeeded
with a processing time of +- 30 minutes

my solution was to dump the file to a MYSQL dump wich is text based then i
wrote a VB.net program that run through this file and converted the Mysql
dialect to MSSQL Dialect
run the DDL SQL and batch inserted a few hundred records at once to MSSQL .

this was much faster as ODBC connections etc etc that could be made to MYSQL
to MSSQL this process took for this program a whole weekend to complete

maybe this gives you some ideas

regards

Michel Posseth [MCP]

I'm moving all the tables in a remote informix database to a local sql
server database. Fill via the odbcadapter and update with the sqladapter
was the best way I could come up with. Don't even go there. You were
about to tell me to use the databases migration tools weren't you
--can't--don't have time to explain.

Still there's a nice feeling knowing that the computer is spending a lot
of time on this project especially after all the work it's put me through.
Let it work over Christmas. Otherwise it'd be wasted processing potential
anyway.

maybe a glitch in the line ???


i can`t inmagine why you would like to transfer such a hughe amount of
data , i work also with databases that contain millions of records
however i never encountered a situation when i needed to transfer such
amounts in a dataset

regards

Michel Posseth [MCP]

I left a program running Thursday when I left for the holiday. It was
filling a dataset with just over 3 million records from a table across a
VPN connection. Estimates I made from other large, but not this large,
tables suggested it would take approximately 18 hours to run. I just
checked in on it and it had failed in the fill command. The error
message it gave me was the "connection is dead" Given the length of time
it was to take I don't doubt something happened but nothing should have
happened. Is there anything I can do to make the odbcadapter.fill less
likely to have these problems?

 

[Guest]

Hi,

The informix ado.net class might give better performance than the odbc
class.

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0510durity/index.html

Ken
-------------------------

I'm moving all the tables in a remote informix database to a local sql
server database. Fill via the odbcadapter and update with the
sqladapter was the best way I could come up with. Don't even go there.
You were about to tell me to use the databases migration tools weren't
you --can't--don't have time to explain.

Still there's a nice feeling knowing that the computer is spending a lot
of time on this project especially after all the work it's put me
through. Let it work over Christmas. Otherwise it'd be wasted
processing potential anyway.

maybe a glitch in the line ???


i can`t inmagine why you would like to transfer such a hughe amount of data
, i work also with databases that contain millions of records however i
never encountered a situation when i needed to transfer such amounts in a
dataset

regards

Michel Posseth [MCP]

I left a program running Thursday when I left for the holiday. It was
filling a dataset with just over 3 million records from a table across a
VPN connection. Estimates I made from other large, but not this large,
tables suggested it would take approximately 18 hours to run. I just
checked in on it and it had failed in the fill command. The error message
it gave me was the "connection is dead" Given the length of time it was to
take I don't doubt something happened but nothing should have happened. Is
there anything I can do to make the odbcadapter.fill less likely to have
these problems?

 

[cj]

I added MyOdbcConnection.ConnectionTimeout = 0

4 hours, 53 minutes, 54 seconds and 488,556 records later the fill
aborted with connection dead.

Any ideas before I do as Ken Tucker recommended and rewrite to fill in
increments?

 

[Cor Ligthert [MVP]]

Yea,

At least set your handling in a try block and log when it stops going. At
least there should in my opinion more be showed than only connection dead.

Cor

 

[Cor Ligthert [MVP]]

cj,

Are you sure that the server has not something as a close all connections
build in to do backups or something like that?

Cor

 

[cj]

It might but I don't see a pattern that would suggest that.

Here's what I doing when the error occurs. I don't know how to collect
any more data on the event.

Try
MyOdbcAdapter.Fill(MyDt)
Catch ex As Exception
stopTime = Now
StopTLbl.Text = Format(stopTime, "hh:mm:ss:ffff tt")
ElapsedTLbl.Text = DateDiff(DateInterval.Second, tStartTime,
stopTime) & " seconds"
ReadLbl.Text = MyDt.Rows.Count()
MessageBox.Show("Fill error: " & ex.Message, "Copy Aborted.")
Exit Sub
End Try

I think it's time for me to re-write the program to copy the records
like Ken Tucker suggested. I'll probably have a better chance of
loading maybe 1000 records at a time.

I wonder if my computer can even handle building a dataset of 3 million
records. Is it trying to keep them all in memory? That might be the
problem. Of course if it is I would have hoped it would tell me out of
memory or something like that instead of connection dead.

I don't know if you celebrate Christmas or not. If so Merry Christmas.
If not Happy Holidays. I'll probably wait till I get back to the
office on Tuesday to rewrite the copy. Thanks for you help.

 

[Peter Huang [MSFT]]

Hi

From your description, it seems that you are using a odbc database(e.g.
access). Commonly access is used in small solution environment and it is
not a good practice to return such a large records in one query.

I think you may try to use SQL server or other large database solution and
redesign your application to return your query of such a large records.
Commonly I think the 3 million records will be not used in the same time.
If you need to do further operation on the returned records, I suggest you
put the logic in the SQL server side as a Stored Procedure.

Thanks!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[m.posseth]

I wonder if my computer can even handle building a dataset of 3 million

records. Is it trying to keep them all in memory? That might be the
problem. Of course if it is I would have hoped it would tell me out of
memory or something like that instead of connection dead.

probably not ,,,


a while ago we had a nice thread about the maximum size of the string data
type ,,,

http://groups.google.com/group/micr...=st&q=string+max+size&rnum=7#7d17429665522020

so i do not know how manny chars your rows contain ,,, but i guess that you
are probably hitting the ceiling ,,,, unless you are running this on a X64
or IA64 system with gigabytes of memory

regards

Michel Posseth [MCP]

 

[cj]

I'm moving all the tables in a remote informix database to a local sql
server database. Fill via the odbcadapter and update with the
sqladapter was the best way I could come up with.

 

[cj]

I rewrote the program to copy in blocks of 50 records with a progress
bar to look pretty. My only problem is that copying the 750 record test
table went from 27 seconds to 245 seconds!!!! It might even get worse.
I had opened the connection to the odbc database before checking the
record count and left it open during all all the fill iterations rather
than closing the connection after checking the record count and then
allowing fill to reopen and close it each time. I fear I need to allow
it to close after each fill after all having the connection go "dead"
would seem to be related to how long it stayed open. I'll do some
adjusting. I'll go from 50 records per block to 1000 and see how that
responds. Lost of my tables only have 10 or so records but one has
almost a million, another about 1.5 million and the biggest 3 million.
Any suggestion would be appreciated.

 

[Peter Huang [MSFT]]

Hi

Thanks for your quickly update!
If you can migrate all the data into SQL server, we can use SQL adapter.
Also as I said before, it would better redesign your program, it is not a
good practice to return such a large records in one query.

Also in ADO.NET 2.0, we introduced some new features which may help you.
Asynchronous Commands
ADO.NET 2.0 Feature Matrix
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html
/ado2featurematrix.asp

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Cor Ligthert [MVP]]

CJ,

Why are you so sticked to that ODBC, don't you have a OLEDB provider for
that database.

Cor

 

[cj]

I'm sorry I think your getting confused. THIS PROGRAM IS BEING USED TO
MIGRATE THE DATA from Informix to SQL Server. And before you suggest
it, I can NOT use any informix db tools or any other tool that would
require loading something or running something on the informix server,
and I don't have time to explain why.