none
timeout when my xml doc has more than 20000 transactions RRS feed

  • Question

  • my .net forms app is serializing a collection of up to 40000 transactions into an xml doc and sending the doc to an sp for parsing and insertion into the db.

     

    I just noticed that when my test harness builds in excess of approx 20000 transactions, the app times out.  20000 trans complete in just under 30 seconds.

     

    I increased the timeout param on my connection string and set the query threshhold timeout on the server to 0.  The app still times out well before the limit in my connection string.

     

    Interestingly, I temporarily placed an insert and commit statement in the very beginning of the sp to capture the xml doc in a random column in our error log.  When the app times out, the insert doesnt occur.  This makes me think that the size of my message is a problem or there is another timeout param lurking somewhere and that it is not necessarily the number of trans in the message causing the problem.  I increased the server's packet size for grins but that didnt help.

     

    Maybe it's that old TransactionOptions thing that also issues the "enlisted..."  error described at http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1892531&SiteID=1.  

     

    Does anyone have a clue?

     

         

    Tuesday, July 1, 2008 11:33 PM

Answers

  • my bad, sorry for wasting anybody's time, I just found it.  The default CommandTimeout property of the sql command (30 seconds) also needed to be overidden.  So far it doesnt look like one setting somewhere would take care of all the possible overrides that are necessary but since I'm in .net maybe I can take care of two of them based on one passed parameter. 

     

    Wednesday, July 2, 2008 2:30 PM

All replies

  • I increased the default Timespan property of TransactionOptions used in the TransactionScope, it still times out. 

     

    I ran a 25000 tran xml doc thru the stored procedure in mgt studio, it worked. 

     

    I'll ignore the machine timeout for now cuz that default (according to the link) is ten minutes.

    Wednesday, July 2, 2008 1:15 PM
  • my bad, sorry for wasting anybody's time, I just found it.  The default CommandTimeout property of the sql command (30 seconds) also needed to be overidden.  So far it doesnt look like one setting somewhere would take care of all the possible overrides that are necessary but since I'm in .net maybe I can take care of two of them based on one passed parameter. 

     

    Wednesday, July 2, 2008 2:30 PM