Is there any way to configure select Retry by default? (OA with SqlAzure)

[Jacek]

Hi,

From time to time I encounter error 40143 in my Windows Azure/SqlAzure web application, like those:

(simple RadListBox_object.DataBind() operation)

System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException'
was thrown.
---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
---> Telerik.OpenAccess.Exceptions.DataStoreException: Error executing query:
Telerik.OpenAccess.RT.sql.SQLException:
The service has encountered an error processing your request. Please try again. Error code 40143. A severe error occurred on the current command. The results, if any, should be discarded. at
Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery() at
OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()
SQL: SELECT a.[RoleId] AS COL1, a.[RoleName] AS COL2 FROM [aspnet_Roles] a ORDER BY COL2
Telerik.OpenAccess.RT.sql.SQLException:
The service has encountered an error processing your request. Please try again. Error code 40143. A severe
error occurred on the current command. The results, if any, should be discarded.
at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery() at
OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()
---> Telerik.OpenAccess.RT.sql.SQLException:
The service has encountered an error processing your request. Please try again.
Error code 40143. A severe error occurred on the current command. The results, if any, should be discarded. at
Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery() at
OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()
--- End of inner exception stack trace ---
at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e) at OpenAccessRuntime.ExceptionWrapper.Throw()
at OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.fetchNextQueryResult(ApplicationContext
context, RunningQuery runningQuery, Int32 skipAmount)
at OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.getNextQueryResult(QueryResultWrapper aQrs, Int32
skipAmount) at OpenAccessRuntime.DataObjects.ForwardQueryResult.Initialize(Int32 indexParam) at
OpenAccessRuntime.DataObjects.ForwardQueryResult.get_Item(Int32 indexParam)
at OpenAccessRuntime.ListEnumerator.setCurrent(Int32 _pos) at OpenAccessRuntime.ListEnumerator.Move(Int32
relative) at OpenAccessRuntime.ListEnumerator.MoveNext() at
Telerik.OpenAccess.Query.TypedEnumerator`1.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1
collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
--- End of inner exception stack trace --- at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[]
arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[]
arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder,
Object[] parameters, CultureInfo culture) at System.Reflection.MethodBase.Invoke(Object obj, Object[]
parameters)
at Telerik.OpenAccess.Web.QueryableDataSourceHelper.ToList(IQueryable query, Type dataObjectType) at
Telerik.OpenAccess.Web.OpenAccessLinqDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) at
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback
callback)
at Telerik.Web.UI.RadComboBox.OnDataBinding(EventArgs e) at Telerik.Web.UI.RadComboBox.PerformSelect() at
System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
at Telerik.Web.UI.RadComboBox.DataBind() at XXX.Page_Load(Object sender, EventArgs e)
in c:\XXX\xxx.cs:line 129 at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs
e) at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Adapters.ControlAdapter.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean
includeStagesAfterAsyncPoint)
at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean
includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest
(HttpContext context) at ASP.XXX_aspx.ProcessRequest(HttpContext context)
in XXX.cs:line 0 at
System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

or
(calling domain method/stored procedure)

System.Data.SqlClient.SqlException (0x80131904): The service has encountered an error processing your request.
Please try again. Error code 40143. A severe error occurred on the current command. The results, if any,
should be discarded. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError
(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean
callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior
runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj, Boolean& dataReady) 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, Int32 timeout, Task& task, Boolean asyncWrite) at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior,
Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean
asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion,
String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at OpenAccessRuntime.CommandWrapper.ExecuteNonQuery() at
Telerik.OpenAccess.Runtime.Logging.LoggingDbCommand.ExecuteNonQuery() at
Telerik.OpenAccess.Data.Common.OACommand.ExecuteNonQuery() at
Telerik.OpenAccess.OpenAccessContextBase.ExecuteNonQuery(String commandText, CommandType commandType,
DbParameter[] parameters) at OpenAccessModel.mystoredprocedure() in c:
\xxx\OAModel.cs:line 658 at XXXX.SomeMethod() in c:\XXX\xx.aspx.cs:line 247

Is there any way to configure in OA model, by default, the retry of a simple select operation. Or maybe with every DataBind()/Rebind() one has to implement his own mechanism? Any suggestions
From what I see so far, there is only connection retry in OA model backend configuration.

Kind Regards,
Jacek

[Thomas]

Hi Jacek,

unfortunately, there is not much that we can offer at this time to get around this issue. On Azure, you can receive such an error on any point in time, including the time after some of the results have already been processed, thus requiring a logical cleanup in our internal state which we dont have yet. Also, we would need to implement a strategy how often / in which time we can retry. How important is such a feature for you? How often do you receive this error, are the call stacks the same?

All the best,
Thomas
the Telerik team

[Jacek]

Hi,

Is Telerik OpenAccess ORM compatible with Transient Fault Handling Application Block (which could handle retry)?

Will the following work as intended when SqlAzure errors (40143, timeouts) occur?

private EntitiesModel OAContext = new EntitiesModel();

protected void OpenAccessLinqDataSource1_Selecting(object sender, Telerik.OpenAccess.Web.OpenAccessLinqDataSourceSelectEventArgs e)
{
    var retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(5);

    var result = retryPolicy.ExecuteAction(() =>
    {
        return (from a in OAContext.Aspnet_Users select a);
    });

    e.Result = result;
}

Kind Regards,
Jacek

[Thomas]

Hello Jacek,

I do not see an issue there with this approach. We just not have this retry strategy around in our lowest levels, but if a high level retry is fine from an application point of view, we should be fine.

Regards,
Thomas
the Telerik team

[Jacek]

Hi,

Unfortunately Enterprise Library Transient Fault Handling Application Block is not "compatible" with Telerik OpenAccess ORM (2012.2.816.1).
Following code just throws exception, without retry, meaning it doesn't recognize exception:

var retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(5, new TimeSpan(0, 0, 2));
int i = -1;
string SessionId = "someid";

var qnew = retryPolicy.ExecuteAction(() =>
{
    i++;
    return (from a in dBContext.Sessions
            where a.event_session_id == SessionId
            select a.id
                );
});

Exception:

Telerik.OpenAccess.Exceptions.DataStoreException: Error executing query: Telerik.OpenAccess.RT.sql.SQLException:
The service has encountered an error processing your request. Please try again. Error code 40143. A severe error occurred on the current command. The results, if any, should be discarded. at
Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery() at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute() SQL: SELECT a.[id] AS COL1 FROM [sessions] a WHERE a.[event_session_id] = ?
Telerik.OpenAccess.RT.sql.SQLException: The service has encountered an error processing your request. Please try again. Error code 40143. A severe error occurred on the current command. The results, if any, should be discarded.
at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute() ---> Telerik.OpenAccess.RT.sql.SQLException: The service has encountered an error processing your request. Please try again. Error code 40143. A severe error occurred on the current command. The results, if any, should be discarded. at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery() at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery() at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()
--- End of inner exception stack trace ---
at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e) at OpenAccessRuntime.ExceptionWrapper.Throw() at OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.fetchNextQueryResult(ApplicationContext context, RunningQuery runningQuery, Int32 skipAmount) at OpenAccessRuntime.DataObjects.ForwardQueryResult.Initialize(Int32 indexParam) at OpenAccessRuntime.DataObjects.ForwardQueryResult.get_Item(Int32 indexParam) at OpenAccessRuntime.ListEnumerator.setCurrent(Int32 _pos) at OpenAccessRuntime.ListEnumerator.Move(Int32 relative) at System.Linq.Enumerable.Any[TSource](IEnumerable`1 source) at

I will try to modify Enterprise Library (probably modification of SqlAzureTransientErrorDetectionStrategy class is only neccesary) to support OA, but any help would be appreciated :)

Jack

[Thomas]

Hello Jacek,

yes, that could be the reason: The Enterprise Library does not know about OpenAccess exceptions, and you need to handle them. Please consider checking the OpenAccessException.CanRetry property too.

All the best,
Thomas
the Telerik team

[TIOS]

We are busy migrating to azure too and am quite concerned about this as we utilise telerik orm quite a bit.  Did you either get a workaround Jacek?

I'm concerned that this hasnt been brought up till now as Telerik ORM has had azure support for quite a while. Im hoping teleirk will provide an answer for us......

Thanks,
Chris

[Thomas]

So far we do not have an implicit retry behavior. It is also not very easy to implement, because a
transaction might already have made some changes to the database (FlushChanges) prior to execution of the query. Likewise the query processing might be interrupted somewhere in the middle, and a retry might simply not be possible at this stage (reading parallel result sets, for example).
Until these issues have been covered I think it is best to handle the Azure exceptions in the application.

All the best,
Thomas
the Telerik team

[Andrew]

Did anyone in this thread ever figure a solution for implementing Transient Fault Handling for SQL Azure with the Telerik OpenAccess ORM? We are starting to leverage SQL Azure as well and think this functionality would help resolve some of the SQL connection issues we are having.  Any thoughts?