Timeout Expired in Stored Procedure Call

Posts   
 
    
larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 28-Feb-2012 23:02:43   

I'm using V2.6, self servicing, .NET 2.0. I make a call to a stored procedure that usually executes in 0-2 seconds within SQL Server Management Studio, and receive a Timeout Expired exception. My call looks like this:

            edu.umn.cffm.agplan.HelperClasses.DbUtils.CommandTimeOut = 120;
            DataTable myInactivePlans2 = RetrievalProcedures.MyCall(dtTwoYearsAgo, false);

The Timeout Expired occurs in the adapter.Fill(tableToFill) statement in CallRetrievalStoredProcedure, line 226 of DbUtils.cs. I make a call to this procedure earlier in my code, passing true instead of false and it runs successfully, returning 0 records. This timed out call should return 1025 records. Any idea why it's timing out?

One detail: this stored procedure had been taking quite a while to run, but I tuned it and it takes 0-2 seconds at this time. It doesn't depend on any views.

Thanks.

Laurie

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 29-Feb-2012 06:16:22   
  • What is your LLBLGen runtime library version? (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7725)

  • Does this happen always? Does it used to work before?

  • Also check that the SP definition didn't change, if so you should regenerate code. Also check that your connection string is pointing to the appropriate DB (the one with the tune up).

You could try to increase the value of DBUtils.CommandTimeOut, however I see that you expects it to be faster.

David Elizondo | LLBLGen Support Team
larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 29-Feb-2012 17:18:44   

Runtime library version = 2.6.10.421.

This does happen always. It has never worked.

I have regenerated the code just in case. The SP definition hadn't changed, but the underlying stored procedure had. Regenerating the code didn't make a difference. I also went in and removed the stored procedure, ran a refresh of all catalogs, and re-added it, then regenerated the code again.

I ran it with CommandTimeOut set to 1200. It is still getting the timeout expired exception. Plus the fact that the actual sp runs in under 2 seconds.

I make two calls to this particular SP in my code: one with a parameter set to true and one to false. The first one returns just fine. It's the call with the parameter set to false that takes forever to run. Changing the order of these procedure calls does not make a difference.

I'm pretty much stumped.

larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 29-Feb-2012 17:21:26   

Oh, and here's the stacktrace in case that's helpful:

The thread '<No Name>' (0x1c24) has exited with code 0 (0x0).
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
        System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>AgPlanAccountCleanup.vshost.exe</AppDomain><Exception><ExceptionType>System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.</Message><StackTrace>   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.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(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at edu.umn.cffm.agplan.HelperClasses.DbUtils.CallRetrievalStoredProcedure(String storedProcedureToCall, SqlParameter[] parameters, DataTable tableToFill, ITransaction transactionToUse) in C:\websites\cffm\BizPlan\LLBLGen\HelperClasses\DbUtils.cs:line 226
   at edu.umn.cffm.agplan.StoredProcedureCallerClasses.RetrievalProcedures.AgPlanBusinessPlansGetBpidsForInactiveBusinessPlans(DateTime inactivePlanDate, Boolean isMarkedForDeletion) in C:\websites\cffm\BizPlan\LLBLGen\StoredProcedureCallerClasses\RetrievalProcedures.cs:line 49
   at AgPlanAccountCleanup.Program.Main(String[] args) in C:\websites\cffm\BizPlan\Updates for TAAF\AgPlanAccountCleanup\AgPlanAccountCleanup\Program.cs:line 62
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>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.SqlInternalConnection.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(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at edu.umn.cffm.agplan.HelperClasses.DbUtils.CallRetrievalStoredProcedure(String storedProcedureToCall, SqlParameter[] parameters, DataTable tableToFill, ITransaction transactionToUse) in C:\websites\cffm\BizPlan\LLBLGen\HelperClasses\DbUtils.cs:line 226
   at edu.umn.cffm.agplan.StoredProcedureCallerClasses.RetrievalProcedures.AgPlanBusinessPlansGetBpidsForInactiveBusinessPlans(DateTime inactivePlanDate, Boolean isMarkedForDeletion) in C:\websites\cffm\BizPlan\LLBLGen\StoredProcedureCallerClasses\RetrievalProcedures.cs:line 49
   at AgPlanAccountCleanup.Program.Main(String[] args) in C:\websites\cffm\BizPlan\Updates for TAAF\AgPlanAccountCleanup\AgPlanAccountCleanup\Program.cs:line 62
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</ExceptionString><DataItems><Data><Key>HelpLink.ProdName</Key><Value>Microsoft SQL Server</Value></Data><Data><Key>HelpLink.ProdVer</Key><Value>10.50.1617</Value></Data><Data><Key>HelpLink.EvtSrc</Key><Value>MSSQLServer</Value></Data><Data><Key>HelpLink.EvtID</Key><Value>-2</Value></Data><Data><Key>HelpLink.BaseHelpUrl</Key><Value>http://go.microsoft.com/fwlink</Value></Data><Data><Key>HelpLink.LinkId</Key><Value>20476</Value></Data></DataItems></Exception></TraceRecord>
The program '[9660] AgPlanAccountCleanup.vshost.exe: Managed (v2.0.50727)' has exited with code -1073741510 (0xc000013a).

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 29-Feb-2012 17:30:22   

Since you are using the Adapter template set, you should set DataAccessAdapter.CommandTimeOut.

The DbUtils version is for the SelfServicing template set.

larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 29-Feb-2012 17:33:09   

I'm using selfservicing.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 29-Feb-2012 17:50:11   

Oh I see. Could you please try the latest release of v.2.6

larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 29-Feb-2012 18:24:59   

Done. Recompiled. New runtime version 2.6.11.1105.

Same results. :-<

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 29-Feb-2012 19:17:49   

Please check this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=20639

And check the docs for Wrapping the SP

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 01-Mar-2012 12:43:44   

Please check the generate code / templates you're using. The issue is / was in the templates but we can't reproduce it with the latest templates for sql server.

Frans Bouma | Lead developer LLBLGen Pro
larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 02-Mar-2012 17:28:56   

SD.TemplateBindings.SqlServerSpecific.NET20 is at the top.

I'm working on redoing this as a typed view. Will let you know how it goes.

larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 02-Mar-2012 17:43:55   

Changing to typed view worked just dandy. Took about 26 seconds.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 02-Mar-2012 20:09:44   

larkydoo wrote:

Changing to typed view worked just dandy. Took about 26 seconds.

Good wink Let us know if you ever has a way to reproduce SP situation.

David Elizondo | LLBLGen Support Team