Hi Freddy,

Is the service_task_view a View? How does the View code look and what does the instead-of delete trigger/Delete handler look like?

Yes it's a view. 

And it just states. When I do the exact statement from the log in SMSS I don't get any error. It's an odd error because there is no recursion. 

delete st
  from service_task st
  join deleted d
    on d.tenant_id        = st.tenant_id
   and d.service_id       = st.service_id
   and d.service_task_id  = st.service_task_id

That indeed is pretty odd. Could you add the complete stack trace of this error? That will help us determine the exact problem.

2023-02-24T16:37:29.2384265-03:00 8000001c-0001-f600-b63f-84710c7967bb [err] An unhandled exception occurred while processing the request. (ffba027a)
Indicium.Shared.TSF.FatalDbException (0x80004005): The following query failed with an exception: 'DELETE  t1
FROM  [service_task_view] t1

WHERE t1.[tenant_id] = @p0 AND t1.[service_id] = @p1 AND t1.[service_flow_id] = @p2'
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at Indicium.Data.Shared.Connection.DbCommandExtensions.WithLogging[t](DbCommand command, String userID, String effectiveUserID, String sqlStatement, Func`1 action) in C:\azp\agent\_work\1\s\src\Data\Indicium.Data.Shared\Connection\DbCommandExtensions.cs:line 150
   at Indicium.Data.Shared.Connection.DbCommandExtensions.ExecuteNonQueryWithMessages(DbCommand command, String userID, String effectiveUserID, String sqlStatement, TSFMessageHandler messageHandler, Func`4 processError) in C:\azp\agent\_work\1\s\src\Data\Indicium.Data.Shared\Connection\DbCommandExtensions.cs:line 71
   at Indicium.Data.SQLServer.SQLDataProvider.ExecuteNonQuery(DbCommand command, TSFMessageHandler messageHandler) in C:\azp\agent\_work\1\s\src\Data\Indicium.Data.SQLServer\SQLDataProvider.cs:line 225
ClientConnectionId:99b44650-d291-4f8f-a880-14ba490ee3f8
Error Number:530,State:1,Class:16
   at Indicium.Data.SQLServer.SQLDataProvider.ExecuteNonQuery(DbCommand command, TSFMessageHandler messageHandler) in C:\azp\agent\_work\1\s\src\Data\Indicium.Data.SQLServer\SQLDataProvider.cs:line 225
   at Indicium.Data.Shared.TSFDbDataProvider.DeleteData(FullTable table, TSFFilterExpressionNode whereClause, ITSFDataRow row) in C:\azp\agent\_work\1\s\src\Data\Indicium.Data.Shared\TSFDbDataProvider.cs:line 394
   at Indicium.Shared.Telemetry.ServerTimings.ServerTimingsBuilder.Capture[t](String key, Func`1 action) in C:\azp\agent\_work\1\s\src\Indicium.Shared\Telemetry\ServerTimings\ServerTimingsBuilder.cs:line 41
   at Indicium.Controllers.TableController.deleteRecord(TSFTableEntityType entityType, ODataUri odataUri, ITSFDataRow row) in C:\azp\agent\_work\1\s\src\Indicium\Controllers\TableController.cs:line 901
   at Indicium.Controllers.StagingControllerBase.CommitActionTriggerProcessFlow(IApiObjVariant`1 variant, String taskReportContextTableID, ProcessActionType processActionType, IProcessFlowResource processFlowResource, Func`1 commitAction) in C:\azp\agent\_work\1\s\src\Indicium\Controllers\StagingControllerBase.cs:line 801
   at Indicium.Controllers.TableController.Delete() in C:\azp\agent\_work\1\s\src\Indicium\Controllers\TableController.cs:line 890
   at Indicium.Controllers.TableController.Delete() in C:\azp\agent\_work\1\s\src\Indicium\Controllers\TableController.cs:line 890
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Indicium.Extensions.ApplicationBuilderExtensions.<>c.<<SameSiteOpenIDConnectInterceptor>b__0_0>d.MoveNext() in C:\azp\agent\_work\1\s\src\Indicium\Extensions\ApplicationBuilderExtensions.cs:line 57
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.ResponseCompression.ResponseCompressionMiddleware.InvokeCore(HttpContext context)
   at Indicium.Middleware.Security.AuthenticationHeadersMiddleware.InvokeAsync(HttpContext context) in C:\azp\agent\_work\1\s\src\Indicium\Middleware\Authentication\AuthenticationHeadersMiddleware.cs:line 48
   at Indicium.Middleware.Security.SecurityHeadersMiddleware.InvokeAsync(HttpContext context, ContentSecurityPolicyBuilder cspBuilder) in C:\azp\agent\_work\1\s\src\Indicium\Middleware\Security\SecurityHeadersMiddleware.cs:line 55
   at Indicium.Middleware.Messages.TSFMessagesMiddleware.Invoke(HttpContext context, IRootApplicationLoader rootApplicationLoader, TSFRequestContext requestContext) in C:\azp\agent\_work\1\s\src\Indicium\Middleware\Messages\TSFMessageMiddleware.cs:line 45
   at Indicium.Middleware.Telemetry.ServerTimings.ServerTimingsMiddleware.InvokeAsync(HttpContext context, ServerTimingsBuilder serverTimingsBuilder) in C:\azp\agent\_work\1\s\src\Indicium\Middleware\Telemetry\ServerTimings\ServerTimingsMiddleware.cs:line 43
   at Indicium.Middleware.ExceptionHandlingMiddleware.Invoke(HttpContext context, TSFRequestContext requestContext) in C:\azp\agent\_work\1\s\src\Indicium\Middleware\ExceptionHandlingMiddleware.cs:line 84

Hello Freddy,

Given the error message, I can only conclude that there must be some kind of recursion occuring through one of the following causes:

• The view contains a CTE in its definition that uses recursion. This includes selecting from or joining with another view which might contain a CTE, and so on.
• The view has an instead of delete trigger and this trigger contains insert, update and/or delete statements which keep triggering additional insert, update and/or delete triggers. Or select, insert, update or delete statements on other views which contain a recursive CTE.
• The view does not have an instead of delete trigger, but SQL Server is able to resolve the delete statement itself on the base table(s) of the view. These base table(s) have delete triggers which in turn do insert, update and/or delete statements which trigger additional triggers, etc. Or select, insert, update or delete statements on other views which contain a recursive CTE.

Either way it should boil down to a recursive CTE somewhere, long trigger chains or a combination of both. If I'm not mistaken, the max recursion depth for triggers is 32, not 100. So I'm leaning towards the CTE explanation.

You mentioned that when you run the statement manually in SSMS, you don't get the same error message. Since this error message is most definitely coming from SQL Server and not from Indicium, I can only think of three reasons:

• You are not running the statement on the same database and therefore you are getting a different result. Make sure that the ID or alias of the application that is used in the URL of the delete request to Indicium actually resolves to the server/database combination on which you ran the statement manually. If the first segment of the URL is sf, please look at the runtime configuration in the SF. If the first segment is iam, please look at the application and server settings in IAM.
• The statement that Indicium actually runs on the database is the same as the one you used via SSMS, however, the manner in which Indicium runs the statements is different. If you go to Indicium's Database Event Log you will find the actual statement which allows you to do a true one to one comparison. I find it somewhat unlikely that this would make a difference, but it's worth trying.
• A different max recursion depth is used when you're running the statement via SSMS.

I hope this helps.

• The statement that Indicium actually runs on the database is the same as the one you used via SSMS, however, the manner in which Indicium runs the statements is different. If you go to Indicium's Database Event Log you will find the actual statement which allows you to do a true one to one comparison. I find it somewhat unlikely that this would make a difference, but it's worth trying.

Hi @Vincent Doppenberg , 

The actual prepared statement does lead to the same error, but it doesn't make any sense. 

It's the use of the sp_prepexec that makes the error occur, if I declare the parms and prepare the statement with a ‘normal’  exec($) it works fine. 

If you check out the execution plan of both queries you’ll probably notice that they are different.

It may be possible that one execution plan applies a certain filter earlier than the other execution plan, resulting in different nesting levels for a subsequent CTE.

Prepared statements try to reuse query plans whereas ad-hoc queries generate a new query plan. Query plans are indexed by the command text and the command text of prepared statements are the same even though the parameters differ.

Ad-hoc queries always have different command texts when the parameters vary. This causes almost guarantueed query plan compilation CPU time for ad-hoc queries but may result in more efficient queries from time to time, optimized for the specific parameter values.

SQL Server 2022 offers a cool new feature that allows for multiple query plans for a single command based on varying impact of the provided parameters, to reduce the drawback of shared plans for prepared statements. But I digress.

You can try to extract the CTE from the service_task view code and place it into a table-valued function with the parameters determining the filtering to the point that recusion will not be exhausted. This forces the filtering of the CTE to be performed in the correct sequence.

Hi @Anne Buit ,

You can try to extract the CTE from the service_task view code and place it into a table-valued function with the parameters determining the filtering to the point that recusion will not be exhausted. This forces the filtering of the CTE to be performed in the correct sequence.

But it still doesn't make sense.  The view does use a TVF (with @table definition) that uses a CTE to construct a workflow with start- and end-dates. However, it's impossible to have a recursion, because that would need dependencies (and it's protected for looping) and in this case there are no dependencies registered, so it's a bit impossible to get into a loop. 

Also there is not execution plan to be seen, it instantly goes into this error and there is no execution plan visible or shown. 

Found the issue. Apparently these prepared statements take into consideration the whole data set. There was an old flow (completely other ID) that had a recursive loop. After fixing the query, so it stops at a detected loop the delete works.