Re: Timeout on dataAdapter Update method

[larzeb]

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

 

[Kevin Yu [MSFT]]

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]

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

 

[Kevin Yu [MSFT]]

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."

 

[larzeb]

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

 

[Kevin Yu [MSFT]]

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]

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]

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

 

[Kevin Yu [MSFT]]

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]

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

 

[Kevin Yu [MSFT]]

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."