Dataadapter.Update fails intermittendly when updating large database

[Urs Eichmann]

My Program needs to insert about 6 Mio records into a large table on SQL
Server 2000 which already has 2 or 3 Mio records. It uses a DataAdapter
to insert 100 records at a time.

About every 2 or 3 hours, the DataAdapter.Update fails with a timeout
exeption (SqlException "Timeout expried. the timeout period elapsed
prior to completion of the operation or the server is not responding".

I used SQL profiler to fetch the latest INSERT INTO statement of th
dataadapter. If I run that INSERT INTO statement in SQL Query analyzer,
I notice that the command actually succeeds, but it takes 2 or 3 minutes
to complete.

I guess that SQL Server is growing the db size during this time. The
problem is - how can I make the dataadapter wait for the command to
complete?

I used DataAdapter.UpdateCommand.CommandTimeout = 600 right before the
DataAdapter.Update command, but it still returns after about 30 seconds
with a timeout exception.

I also tried to catch the exception, and reissue the update command if
the exception occurs. This doesn't help either - the update will fail
again and again.

Thanks for your help
Urs

 

[Miha Markic [MVP C#]]

Hi Urs,

My Program needs to insert about 6 Mio records into a large table on SQL
Server 2000 which already has 2 or 3 Mio records. It uses a DataAdapter
to insert 100 records at a time.

About every 2 or 3 hours, the DataAdapter.Update fails with a timeout
exeption (SqlException "Timeout expried. the timeout period elapsed
prior to completion of the operation or the server is not responding".

I used SQL profiler to fetch the latest INSERT INTO statement of th
dataadapter. If I run that INSERT INTO statement in SQL Query analyzer,
I notice that the command actually succeeds, but it takes 2 or 3 minutes
to complete.

I guess that SQL Server is growing the db size during this time. The
problem is - how can I make the dataadapter wait for the command to
complete?

I used DataAdapter.UpdateCommand.CommandTimeout = 600 right before the
DataAdapter.Update command, but it still returns after about 30 seconds
with a timeout exception.

UpdateCommand handles just UPDATEs. For handling inserts there is INSERT
command thus try setting InsertCommand.CommandTimeout.
Also, you might want to check the indexes on your database tables, database
size - you might initialy create it larger, etc to optimize it.

 

[Kevin Yu [MSFT]]

Hi Urs,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to increase the timeout time
for inserting to database. If there is any misunderstanding, please feel
free to let me know.

Based on my research, you have set the wrong timeout value for the
DataAdapter. Since you are doing an INSERT operation, you have to set
timeout value for DataAdapter.InsertCommand. So I think changing
DataAdapter.UpdateCommand.CommandTimeout = 600 to
DataAdapter.InsertCommand.CommandTimeout = 600 will fix the problem.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Urs Eichmann]

Hi Kevin

Based on my research, you have set the wrong timeout value for the
DataAdapter. Since you are doing an INSERT operation, you have to set
timeout value for DataAdapter.InsertCommand. So I think changing
DataAdapter.UpdateCommand.CommandTimeout = 600 to
DataAdapter.InsertCommand.CommandTimeout = 600 will fix the problem.

Ouch!!! Of course you're right! I was mislead by the fact the command is
called "dataadapter.update" - I assumed it always uses the
UpdateCommand... thanks!

Urs