Sql Exception thrown trying to exec a stored proc claiming missing param that isnt missing, works for a period of time then stops
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 18.4k
- Forks
- 3.7k
- Avg merge
- 5h 8m
- Merged PRs (30d)
- 1
Description
net 7 web app
Dapper 2.0.123
My application recently started throwing sql exceptions when executing a stored procedure with dapper that has parameters. The procedure is only called in one place in my code and I added some logging to make sure params are being set. The weirdest part is the code runs fine for a few hours and then the error starts happening. A reset of the appPool fixes the issue until it happens again. I would appreciate any guidance on the issue. The original code was only using dynamic params, I refactored to use a dictionary to see if anything would change. It did not.
public async Task<List<GroupEntity>> GroupEntityList(
string user,
string searchGroupEntityName,
string searchGNPI,
int? searchOrganization,
string searchSortColumn,
string searchSortDirection)
{
string storedProcedure = "[DATA].[sp_UI_GroupEntity]";
Dictionary<string, object> dictionary = new Dictionary<string, object>
{
{ "@Action", 1 },
{ "@User", user },
{ "@searchGroupEntityName", searchGroupEntityName },
{ "@searchGNPI", searchGNPI },
{ "@searchOrganization", searchOrganization },
{ "@searchSortColumn", searchSortColumn },
{ "@searchSortDirection", searchSortDirection }
};
try
{
_logger.LogInformation("Attempting to use stored proc:{storedProcedure} with parameters:{dictionary}", storedProcedure, JsonConvert.SerializeObject(dictionary));
using SqlConnection connection = new SqlConnection(_configuration.GetConnectionString("MPRI"));
connection.Open();
IEnumerable<GroupEntity> result = await connection.QueryAsync<GroupEntity>(storedProcedure, new DynamicParameters(dictionary),
commandType: CommandType.StoredProcedure);
{
return result.ToList();
}
}
catch (Exception e)
{
_logger.LogError(e, e.Message, dictionary, user, searchGroupEntityName, searchGNPI);
throw;
}
}
Information from the logger statement in the try block:
Attempting to use stored proc:"[DATA].[sp_UI_GroupEntity]" with parameters:"{"@Action":1,"@User":"someusername","@searchGroupEntityName":null,"@searchGNPI":null,"@searchOrganization":null,"@searchSortColumn":null,"@searchSortDirection":null}"
Error Message:
System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'sp_UI_GroupEntity' expects parameter '@Action', which was not supplied.
at System.Data.SqlClient.SqlCommand.<>c.b__126_0(Task1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 418
at MPRI.Repository.GroupEntityRepository.GroupEntityList(String user, String searchGroupEntityName, String searchGNPI, Nullable1 searchOrganization, String searchSortColumn, String searchSortDirection) in C:\Source\repos\ASPNET_MPRI\MPRI\Repository\R_GroupEntity.cs:line 55
at MPRI.Controllers.GroupEntityController.Index(String searchGroupEntityName, String searchGNPI, String searchSortColumn, String searchSortDirection, Nullable1 searchPageNumber, Nullable1 resetFilter) in C:\Source\repos\ASPNET_MPRI\MPRI\Controllers\C_GroupEntity.cs:line 150
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.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.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.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.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Server.IIS.Core.IISHttpContextOfT`1.ProcessRequestAsync()
ClientConnectionId:8545e7f4-3f46-4bee-a856-9f7e0a5b2a83
Error Number:201,State:4,Class:16
Providing the create part of the stored proc jic:
CREATE PROCEDURE [DATA].[sp_UI_GroupEntity]
(
@Action TINYINT
,@User VARCHAR(50)
,@GroupEntityID INT = NULL
,@GroupEntityName VARCHAR(100) = NULL
,@GroupEntityTypeID INT = NULL
,@GNPI BIGINT = NULL
,@StartDate DATE = NULL
,@EndDate DATE = NULL
,@GroupEntityPhone VARCHAR(15) = NULL
,@GroupEntityFax VARCHAR(15) = NULL
,@GroupEntityBillingName VARCHAR(200) = NULL
,@Comments VARCHAR(4000) = NULL
,@Annotation VARCHAR(4000) = NULL
,@searchGNPI VARCHAR(15) = NULL
,@searchGroupEntityName VARCHAR(100) = NULL
,@searchOrganization INT = NULL
,@searchActiveFlag BIT = NULL
,@searchSortColumn VARCHAR(100) = NULL
,@searchSortDirection VARCHAR(50) = NULL
)
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the supplied call in MPRI.Repository.GroupEntityRepository.GroupEntityList and the stored procedure definition, then inspect Dapper's SqlMapper.Async.cs around line 418. Reproduce the intermittent failure with the logged parameters and determine whether the missing @Action originates in Dapper, SQL Server, or the application. Done requires a confirmed cause and a narrowly scoped fix or reproducible upstream report.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100