Timeout?

Question

Particularly when our site is busy I am getting a number of SQL timeout error exceptions from the call PurchaseOrderManager.GetPurchaseOrderAsDataSet. Is there any way to increase the timeout value for this call so I can avoid the exception?

Thank you.


Kevin

Answer 1

Kevin,
I would recommend to reindex all your indexes so that you can avoid such errors. The other option is to increase command or execution timeout at SQL server. check below link for more details (not sure if it works but it may degrade your application performance).
http://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studio

Hope this helps.

Regards,
-Ravi Kanth Koppala

---

http://techblog.ravikanth.net (If this post answers your question - Either Mark this post as the answer or vote as being useful.)

Answer 2

This sets the timeout value for queries executed in the Management Studio. This wouldn't affect the DB queries that CS executes would it? I believe the default timeout for stored proc queries that are executed via ADO.NET is 30 seconds. The default shown is zero which would indicate no timeout.

Kevin

Answer 3

I think I had the same problem. This occurs when the server is under load and it was (in my case) a TCP/IP problem; in fact we were opening sql connection each time we call a sp (I suppose that GetOrderAsDataset opens one each time too) and the default Windows server 2003 TCP connection pool to another server port is 5000 with a 240s timeout, so that at a time there was no connection possible anymore.
To overide this problem, there are 2 solutions:
- In your site, avoid open new connection and try to cache more sql response data,
- Add connections and lower the timout. This can be done using this:http://support.microsoft.com/kb/328476/en-us

hth
Cyrebre

Answer 4

Call it ignorance but I can't see from this article how to adjust the timeout value or "add connections". Would you enlighten me, please?

Thank you.

Kevin

Answer 5

Sorry, this article is a little bit dark.

The values you should change in registry are these ones:
MaxUserPort:
http://technet.microsoft.com/en-us/library/cc938196.aspx
TcpTimedWaitDelay:
http://technet.microsoft.com/en-us/library/cc938217.aspx

I changed mine to 65000 for MaxUserPort and 30s for TcpTimedWaitDelay and my server does not have problems anymore.

Hope this help.

Cyrebre

Answer 6

Thank you.

The port was already set to the max (0xffff). I increased the default timeout from 30 to 60.

Kevin

Answer 7

Hi,

Not sure you should increase this value. This setting is used to timeout TCP connections when in TIME_WAIT state. The default timeout is normally 240s, passing it to 60 is good to free up connection faster, in the case you create new connections. If a majority of connection is often reused, you should increase this value.

In the majority of cases, this value should be set to 30.

Therefore, i read again your post and maybe this article can help you : http://msdn.microsoft.com/en-us/library/ms960520.aspx


hth
Cyrebre

Answer 8

Hello,

Sorry about the contnual questions. In this article I see

sqlCommandTimeoutSeconds
 
and

sqlLongRunningCommandTimeoutSeconds

I prticularly see timeouts in GetOrderAsDataSet. This article mentions that GetOrderAsXml is subject to the second timeout but nothing is said about GetOrderAsDataSet. Any ideas?

Thanks again.

Kevin

Answer 9

Re,

I don't know more about GetOrderAsDataSet but I suppose that one of the 2 parameters works with that. Furthermore, you really should use getorderasXml which is really faster and easier to use as Dataset. The conversion from dataset-style programming to xml-style is relatively easy too.

hth
Cyrebre

Answer 10

Hello,

Sorry this is unavoidably getting off the subject. But if you would be so kind as to giving me the format or example of the XML that is returned from GetOrderAsXml I would greatly appreciate it. It would save me some time in converting my "data set" centric programming to Xml.

Thank you.

Kevin

Answer 11

Hello,

I can't send you an order example because it contains too much sensible data, but you can download yours to a temp xml file using this code:

<pre lang="x-c#">public void GetOrdersXML()
        {
            Microsoft.CommerceServer.Orders.OrderServiceAgent MyAgent = new OrderServiceAgent(SITE_URL);
            MyAgent.Credentials = new NetworkCredential(USER_NAME, PASS_WORD, DOMAIN_NAME);
            MyAgent.AllowBasicOverNonSecure = true;
            MyAgent.UnsafeAuthenticatedConnectionSharing = true;
            OContext = OrderManagementContext.Create(MyAgent);
            PurchaseOrderManager POmgr = OContext.PurchaseOrderManager;
            XmlElement MyOrderDoc = POmgr.GetPurchaseOrdersAsXml(GetOrdersList("NewOrder");
            StreamWriter s = new StreamWriter("c:\\temp\\ordersxml.xml");
            s.Write(MyOrderDoc.InnerXml);
            s.Close();
            s.Dispose();
         }


 public List<Guid> GetOrdersList(string Status)
        {
            List<Guid> MyOrderList = new List<Guid>();
            InitializeContext();
            PurchaseOrderManager POmgr = OContext.PurchaseOrderManager;
            SearchClauseFactory MySCF = POmgr.GetSearchClauseFactory(POmgr.GetSearchableProperties("fr-fr"), "PurchaseOrder");
            SearchClause MySC = MySCF.CreateClause(ExplicitComparisonOperator.Equal, "Status", Status);
            SearchOptions MySO = new Microsoft.CommerceServer.SearchOptions();
            MySO.NumberOfRecordsToReturn = 250;
            MySO.PropertiesToReturn = "OrderGroupId";
            DataSet MyOrders = POmgr.SearchPurchaseOrders(MySC, MySO);
            foreach (DataRow MyRow in MyOrders.Tables[0].Rows)
            {
                MyOrderList.Add(new Guid(MyRow["OrderGroupId"].ToString()));
            }
            return MyOrderList;
        }

HTH
Cyrebre

Answer 12

I understand. I put together a simple test and I think I have the format now. Thank you.

Kevin

Answer 13

Unfortunately I use GetPurchaseOrderAsXml and I still get the timeouts. I have included a sample stack trace below. Any ideas?

Thank you.

Kevin

System.ApplicationException: Current operation failed.  The SQL command could not be executed with in specified timeout.
System.TimeoutException: Current operation failed.  The SQL command could not be executed with in specified timeout. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at Microsoft.CommerceServer.Internal.Orders.MtsHelper.ExecuteSqlCommandDataSet(String connStr, String commandText, CommandType commandType, ListDictionary sqlParameters, Int32 timeout, Boolean dummy)
   --- End of inner exception stack trace ---

Server stack trace: 
   at Microsoft.CommerceServer.Internal.Orders.MtsHelper.ExecuteSqlCommandDataSet(String connStr, String commandText, CommandType commandType, ListDictionary sqlParameters, Int32 timeout, Boolean dummy)
   at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean fExecuteInContext)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.CommerceServer.Internal.Orders.MtsHelper.ExecuteSqlCommandDataSet(String connStr, String commandText, CommandType commandType, ListDictionary sqlParameters, Int32 timeout, Boolean dummy)
   at Microsoft.CommerceServer.Runtime.Orders.POLoader.LoadSearchSet(Guid searchSetId, OrderGroupCollection ogc)
   at Microsoft.CommerceServer.Orders.DataManagement.ServerOrderSystem.LoadPurchaseOrdersFromDB(Guid[] orderGroupIds, OrderGroupCollection ogc)
   at Microsoft.CommerceServer.Orders.DataManagement.ServerOrderSystem.GetPurchaseOrdersAsXml(Guid[] orderGroupIds)
   at Microsoft.CommerceServer.Orders.PurchaseOrderManager.GetPurchaseOrdersAsXml(Guid[] orderGroupIds)