Timeout on dataAdapter Update method

I have run out of ways to determine the reason for a timeout error. The timeout is occuring on the
line indicated with ==>. After completing the statement before, the status of the connection
object(cn) is Open. After the timeout occurs and execution is transferred to the Catch block, the
connection is closed. Also, during the time the Update method is executing, the CPU usage in Windows
Task Manager is pegged.

What can I do to determine the cause of this timeout? Thanks for the help,

Lars

Dim cn As New SqlConnection(ConnectionSettings.cnString)
Dim daMaster As New SqlDataAdapter("usp_UnivUseCode_Sell_All", cn)
Dim daDetail As New SqlDataAdapter("usp_UnivUseCodeMap_Sell_All", cn)
Dim tblMaster As DataTable = ds.Tables(0)
Dim tblDetail As DataTable = ds.Tables(1)

daMaster.InsertCommand = Me.CreateInsertCommandUse(cn)
daMaster.UpdateCommand = Me.CreateUpdateCommandUse(cn)
daMaster.DeleteCommand = Me.CreateDeleteCommandUse(cn)
daDetail.InsertCommand = Me.CreateInsertUpdateCommandMap(cn)
daDetail.UpdateCommand = Me.CreateInsertUpdateCommandMap(cn)
daDetail.DeleteCommand = Me.CreateDeleteCommandMap(cn)

cn.Open()
Try
' Submit the only new Master/Detail rows
daMaster.Update(tblMaster.Select(Nothing, Nothing, DataViewRowState.Added))
==> daDetail.Update(tblDetail.Select(Nothing, Nothing, DataViewRowState.Added))

' Submit the only modified Master/Detail rows
...
Catch ex As SqlException
MessageBox.Show(ex)
End Try
cn.Close()


Private Function CreateInsertUpdateCommandMap(ByVal cn As SqlConnection) As SqlCommand
Dim cmd As New SqlCommand("usp_UnivUseCodeMap_Save", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim pc As SqlParameterCollection = cmd.Parameters
pc.Add("@CompID", SqlDbType.Int, 0, "CompID")
pc.Add("@UseCodeFK", SqlDbType.Int, 0, "UseCodeFK")
pc.Add("@UnivUseCode", SqlDbType.Char, 2, "UnivUseCode")

Return cmd

v-kevy
Thu May 26 22:33:44 CDT 2005

Hi Lars,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you're getting a Timeout exception when
trying to update the master and detail table. If there is any
misunderstanding, please feel free to let me know.

In you post, you mentioned the timeout occurs on the line indicated with
==>. Do you mean the program stopped, and the ==> line was highlighted with
green? If so, I assume the following line throws the exception.

daMaster.Update(tblMaster.Select(Nothing, Nothing, DataViewRowState.Added))

As far as I know, this might be caused by many reasons. However, I'm a
little confused with your code.

First, we have to set the SelectCommand's command type to StoredProcedure.

Dim daMaster As New
SqlDataAdapter("usp_UnivUseCode_Sell_All", cn)
daMaster.SelectCommand.CommandType =
CommandType.StoredProcedure

Then you're using the same function to create the insert and update command
for daDetail. Could you let me know your stored procedure?

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

larzeb
Fri May 27 11:18:28 CDT 2005

Kevin,

The timeout occurs while executing the daDetail.Update statement. I changed the SqlDataAdapter
constructors to point to new Select commands. This did not correct the timeout problem. I included
the Update command's stored procedure and create table ddl.

Thanks, Lars


Dim cn As New SqlConnection(ConnectionSettings.cnString)
Dim daMaster As New SqlDataAdapter(Me.CreateSelectMaster(cn))
Dim daDetail As New SqlDataAdapter(Me.CreateSelectDetail(cn))
Dim tblMaster As DataTable = ds.Tables(0)
Dim tblDetail As DataTable = ds.Tables(1)

daMaster.InsertCommand = Me.CreateInsertCommandUse(cn)
daMaster.UpdateCommand = Me.CreateUpdateCommandUse(cn)
daMaster.DeleteCommand = Me.CreateDeleteCommandUse(cn)
daDetail.InsertCommand = Me.CreateInsertUpdateCommandMap(cn)
daDetail.UpdateCommand = Me.CreateInsertUpdateCommandMap(cn)
daDetail.DeleteCommand = Me.CreateDeleteCommandMap(cn)

cn.Open()
Try
' Submit the only new Master/Detail rows
daMaster.Update(tblMaster.Select(Nothing, Nothing, DataViewRowState.Added))
==> daDetail.Update(tblDetail.Select(Nothing, Nothing, DataViewRowState.Added))


Private Function CreateSelectMaster(ByVal cn As SqlConnection) As SqlCommand
Dim cmd As New SqlCommand("usp_UnivUseCode_Sell_All", cn)
cmd.CommandType = CommandType.StoredProcedure
Return cmd
End Function
Private Function CreateSelectDetail(ByVal cn As SqlConnection) As SqlCommand
Dim cmd As New SqlCommand("usp_UnivUseCodeMap_Sell_All", cn)
cmd.CommandType = CommandType.StoredProcedure
Return cmd
End Function



CREATE PROCEDURE [dbo].usp_UnivUseCodeMap_Save
@CompID int,
@UseCodeFK int,
@UnivUseCode char(2)
AS

if exists (SELECT * FROM UnivUseCodeMap WHERE CompID = @CompID AND
UseCodeFK = @UseCodeFK)
BEGIN

UPDATE [dbo].[UnivUseCodeMap] SET
[UnivUseCode] = @UnivUseCode
WHERE
[CompID] = @CompID
AND [UseCodeFK] = @UseCodeFK
END
ELSE
BEGIN
INSERT INTO [dbo].[UnivUseCodeMap] (
[CompID],
[UseCodeFK],
[UnivUseCode]
) VALUES (
@CompID,
@UseCodeFK,
@UnivUseCode
)
END

CREATE TABLE [dbo].[UnivUseCodeMap] (
[CompID] [int] NOT NULL,
[UseCodeFK] [int] NOT NULL ,
[UnivUseCode] [char] (2) NOT NULL ,
[DateAdded] [datetime] NOT NULL


On Fri, 27 May 2005 03:33:44 GMT, v-kevy@online.microsoft.com (Kevin Yu [MSFT]) wrote:

>Hi Lars,
>
>First of all, I would like to confirm my understanding of your issue. From
>your description, I understand that you're getting a Timeout exception when
>trying to update the master and detail table. If there is any
>misunderstanding, please feel free to let me know.
>
>In you post, you mentioned the timeout occurs on the line indicated with
>==>. Do you mean the program stopped, and the ==> line was highlighted with
>green? If so, I assume the following line throws the exception.
>
>daMaster.Update(tblMaster.Select(Nothing, Nothing, DataViewRowState.Added))
>
>As far as I know, this might be caused by many reasons. However, I'm a
>little confused with your code.
>
>First, we have to set the SelectCommand's command type to StoredProcedure.
>
> Dim daMaster As New
>SqlDataAdapter("usp_UnivUseCode_Sell_All", cn)
> daMaster.SelectCommand.CommandType =
>CommandType.StoredProcedure
>
>Then you're using the same function to create the insert and update command
>for daDetail. Could you let me know your stored procedure?
>
>Kevin Yu
>=======
>"This posting is provided "AS IS" with no warranties, and confers no
>rights."

v-kevy
Fri May 27 21:31:44 CDT 2005

Hi Lars,

Could you let me know, if there is a lot of data that you're trying to
update? If so I suggest you do the following:

1. Increase the value of cn.ConnectionTimeout property. For example, to 30.
2. Increase the value of each Insert and Update command's CommandTimeout
property, in the CreateInsertCommandUse and CreateInsertUpdateCommandMap
function. For example, to 30.

If that still doesn't work, you can try to use Sql Profiler to start a
trace to see what is actually happening on the server, whether the command
has been submitted to the server, or the connection cannot be established.

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

larzeb
Mon May 30 13:23:46 CDT 2005

Kevin,

There is a single row in the Master update and three rows in the Detail update, the one which hangs
on a timeout (indicated with ==>).

daMaster.Update(tblMaster.Select(Nothing, Nothing, DataViewRowState.Added))
==> daDetail.Update(tblDetail.Select(Nothing, Nothing, DataViewRowState.Added))

I don't think that should require increasing the connection timeout property. Below is the output of
the profiler. The line marked with ==> is the Master update.


EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go
==> exec usp_UnivUseCodeMap_Save @CompID = 3, @UseCodeFK = 2, @UnivUseCode = '01'
go
SELECT N'Testing Connection...'
go
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go
SELECT N'Testing Connection...'
go
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go
SELECT N'Testing Connection...'
go
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go
SELECT N'Testing Connection...'
go
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go

Thanks, Lars

On Sat, 28 May 2005 02:31:44 GMT, v-kevy@online.microsoft.com (Kevin Yu [MSFT]) wrote:

>Hi Lars,
>
>Could you let me know, if there is a lot of data that you're trying to
>update? If so I suggest you do the following:
>
>1. Increase the value of cn.ConnectionTimeout property. For example, to 30.
>2. Increase the value of each Insert and Update command's CommandTimeout
>property, in the CreateInsertCommandUse and CreateInsertUpdateCommandMap
>function. For example, to 30.
>
>If that still doesn't work, you can try to use Sql Profiler to start a
>trace to see what is actually happening on the server, whether the command
>has been submitted to the server, or the connection cannot be established.
>
>Kevin Yu
>=======
>"This posting is provided "AS IS" with no warranties, and confers no
>rights."

larzeb
Tue May 31 11:59:24 CDT 2005

Kevin,

The connection string is stored in the app's config file, accessed by a Shared function. So pooled
connections should be used.

All the dataadapter updates are contained within a try-catch block:

cn.Open()
Try
' Submit the only new Master/Detail rows
daMaster.Update(tblMaster.Select(Nothing, Nothing, DataViewRowState.Added))
==> daDetail.Update(tblDetail.Select(Nothing, Nothing, DataViewRowState.Added)) 'Error

' Submit the only modified Master/Detail rows
...
Catch ex As SqlException
MessageBox.Show(Exceptions.HandleError(ex))
End Try
cn.Close()

The error messages from the exception object:

Message = "Timeout expired. The timeout period elapsed prior to completion of the operation or the
server is not responding."
Number = -2 Integer
Procedure = "ConnectionRead (WrapperRead())."
Source = ".Net SqlClient Data Provider"
State = 0
StackTrace = " at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping
tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows)
at MailHouse.DataAccess.UnivUseCodeMapDB.Save(DataSet ds)
in G:\Pow\MailHouse.DataAccess\Data Classes\UnivUseCodeMapDB.vb:line 136"

The connection string before execution of the timeout line, indicated above by ==>:

?dadetail.insertcommand.connection
{System.Data.SqlClient.SqlConnection}
ConnectionString: "Data Source=(local);Initial Catalog=MailHouse;User ID=lcm;"
ConnectionTimeout: 15
Container: Nothing
Database: "MailHouse"
DataSource: "(local)"
PacketSize: 8192
ServerVersion: "08.00.2039"
Site: Nothing
State: Open
WorkstationId: "SIEBEN"

The connection string after execution of the timeout line, and execution transferred to the catch
block:
?dadetail.insertcommand.connection
{System.Data.SqlClient.SqlConnection}
ConnectionString: "Data Source=(local);Initial Catalog=MailHouse;User ID=lcm;"
ConnectionTimeout: 15
Container: Nothing
Database: "MailHouse"
DataSource: "(local)"
PacketSize: 8192
ServerVersion: <error: an exception of type: {System.InvalidOperationException} occurred>
Site: Nothing
State: Closed
WorkstationId: "SIEBEN"

Thanks, Lars

On Tue, 31 May 2005 04:54:20 GMT, v-kevy@online.microsoft.com (Kevin Yu [MSFT]) wrote:

>Hi Lars,
>
>Thanks for posting the trace log here. Since the daMaster.Update has been
>executed successfully, I assume there might be something wrong with the
>connection.
>
>Here I suggest you put the second update in a try block, and catch the
>TimeoutException.
>
> daMaster.Update(tblMaster.Select(Nothing, Nothing,
>DataViewRowState.Added))
> Try
> daDetail.Update(tblDetail.Select(Nothing,
>Nothing, DataViewRowState.Added))
> Catch(Exception ex)
> End Try
>
>In the catch block, we can check the status of the
>daDetail.InsertCommand.Connection. If its status is closed in the catch
>block, it means that it's a connection timeout. If it is open, it seems to
>be a command timeout. So in this case, I strongly suggest you try to
>increase the Timeout to a larger value, for example 60.
>
>Also this might be caused by connection pooling. If a pool is out of
>available connections, the Open method will wait for one. That might also
>the cause of the problem. So could you also post your connection string
>here? The connection string has to be the one you get in the catch block. I
>will try to see if there is something wrong.
>
>Thanks for your cooperation!
>
>Kevin Yu
>=======
>"This posting is provided "AS IS" with no warranties, and confers no
>rights."

v-kevy
Wed Jun 01 03:17:20 CDT 2005

Hi Lars,

This is really weird that it's hard to judge what is actually going wrong,
since the first update works perfect, while the second one fails.

Looking at the nature of this issue, it would require intensive
troubleshooting which would be done quickly and effectively with direct
assistance from a Microsoft Support Professional through Microsoft Product
Support Services. You can contact Microsoft Product Support directly to
discuss additional support options you may have available, by contacting us
at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top.

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

Rogas69
Wed Jun 01 05:57:09 CDT 2005

hmm, maybe try to assign separate connection to each adapter and see what
happens? also try to see what is connection state after master record
update.

Peter

larzeb
Wed Jun 01 11:10:12 CDT 2005

Peter,

As the code stands, the connection is open after the Master update but before the Detail update.
When using a new connection object for the Detail adapter, the behavior is the same. That is, before
the Detail update the connection is open, and the connection times out as a result of the update
method on the Detail table.

Thanks Lars

On Wed, 1 Jun 2005 11:57:09 +0100, "Rogas69" <rogas69@no_spamers.o2.ie> wrote:

>hmm, maybe try to assign separate connection to each adapter and see what
>happens? also try to see what is connection state after master record
>update.
>
>Peter
>

v-kevy
Thu Jun 02 03:42:48 CDT 2005

Hi Lars,

This is really weird, since using another connection object still doesn't
work. Is there anything wrong with the server? If you have another server,
could you try to put the database to it and try to do the job on that
server to see if it works fine?

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

larzeb
Fri Jun 03 14:26:05 CDT 2005

Kevin,

I finally isolated the problem to SQL update not being tuned properly. So it really has nothing to
do with VS.

Thanks for all your help. You pushed me to try new techniques in resolving the problem.

Lars
On Thu, 02 Jun 2005 08:42:48 GMT, v-kevy@online.microsoft.com (Kevin Yu [MSFT]) wrote:

>Hi Lars,
>
>This is really weird, since using another connection object still doesn't
>work. Is there anything wrong with the server? If you have another server,
>could you try to put the database to it and try to do the job on that
>server to see if it works fine?
>
>Kevin Yu
>=======
>"This posting is provided "AS IS" with no warranties, and confers no
>rights."

v-kevy
Fri Jun 03 21:11:55 CDT 2005

You're welcome, Lars. It was nice to hear that you have had the problem
resolved.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

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