QuerySingleOrDefaultAsync: Procedure or function has too many arguments specified

[daviden83]

We have developed an API project in .Net Core 2.2 and Dapper 2.0.30 with Microsoft SQL Server 2017 database. Sometimes it happens after a new build in release, that we receive the "Procedure or function has too many arguments specified" error on the production server. We cannot reproduce this error in debug, even using the same database. Often, recompiling and republishing the project in production, even without making any changes, the problem disappears. The problem is that it then reappears randomly in a subsequent build.

I found this discussion where there was already talk of a similar case: https://stackoverflow.com/questions/25069578/dapper-procedure-or-function-has-too-many-arguments-specified In fact, the first time we had a Stored Procedure that could return multiple datasets and that we ran with the QuerySingleOrDefaultAsync command. But now it is also happening with a stored procedure that always returns a single dataset.

By the way, this method is generic and we use it to perform all the data reading of a single record for all entities. But the error, at least at the moment, occurs only with a certain stored procedure.

Looking at what happens through SQL Profiler, this is the correct call I see in debug: exec dbo.s_ExampleModelFind @emId=74658 This is the wrong one I see in production: exec dbo.s_ExampleModelFind @ParameterNames1=N'emId', @RemoveUnused=1

This is the Stored Procedure:

CREATE PROCEDURE [dbo].[s_ExampleModelFind]
	@emId int
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT	emId,
			emName
			emOrder,
			_usrIdC,
			_grpIdC,
			_tsC,
			_usrIdM,
			_grpIdM,
			_tsM,
			_deleted
	FROM	dbo.ExampleModel
	WHERE	emId = @emId;
END

This is the model class:

public partial class ExampleModel
{
	[Key]
	[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
	public int EmId { get; set; }
	public string EmName { get; set; }
	public int EmOrder { get; set; }
	public int UsrIdC { get; set; }
	public int GrpIdC { get; set; }
	public DateTimeOffset TsC { get; set; }
	public int? UsrIdM { get; set; }
	public int? GrpIdM { get; set; }
	public DateTimeOffset? TsM { get; set; }
	public bool Deleted { get; set; }
}

This is the generic method:

async Task<T> ICRUDRepository<T>.GetByID(params object[] ids)
{
	var keyProperties = typeof(T).GetKeysProperties();	// returns only the properties with Key attribute
	var p = new DynamicParameters();
	for (int i = 0; i < keyProperties.Count; i++)
	{
		p.Add(keyProperties[i].Name, ids[i], keyProperties[i].PropertyType.ToDbType());
	}

	string spName = $"{_storedProcedurePrefix}{_modelName}Find";
	using (IDbConnection conn = Connection) // Connection is a SqlConnection variable
	{
		conn.Open();
		var record = await conn.QuerySingleOrDefaultAsync<T>(spName, p, commandType: CommandType.StoredProcedure);

		return record;
	}
}

Are there any problems building a project in a certain way that uses dapper? I can't explain why this issue never occurs in debugging.

[mgravell]

Well that's very odd - it is like it is using the dynamic parameters type incorrectly (rather than iterating the parameters, it is embedding them directly); does this happen all the time? or only occasionally? (I'm trying to determine how to investigate it)

On Wed, 1 Apr 2020 at 13:23, daviden83 [email protected] wrote:

We have developed an API project in .Net Core 2.2 and Dapper 2.0.30 with Microsoft SQL Server 2017 database. Sometimes it happens after a new build in release, that we receive the "Procedure or function has too many arguments specified" error on the production server. We cannot reproduce this error in debug, even using the same database. Often, recompiling and republishing the project in production, even without making any changes, the problem disappears. The problem is that it then reappears randomly in a subsequent build.

I found this discussion where there was already talk of a similar case: https://stackoverflow.com/questions/25069578/dapper-procedure-or-function-has-too-many-arguments-specified In fact, the first time we had a Stored Procedure that could return multiple datasets and that we ran with the QuerySingleOrDefaultAsync command. But now it is also happening with a stored procedure that always returns a single dataset.

By the way, this method is generic and we use it to perform all the data reading of a single record for all entities. But the error, at least at the moment, occurs only with a certain stored procedure.

Looking at what happens through SQL Profiler, this is the correct call I see in debug: exec dbo.s_ExampleModelFind @emid https://github.com/emid=74658 This is the wrong one I see in production: exec dbo.s_ExampleModelFind @ParameterNames1=N'emId', @RemoveUnused=1

This is the Stored Procedure:

CREATE PROCEDURE [dbo].[s_ExampleModelFind] @emId int AS BEGIN SET NOCOUNT ON;

SELECT emId, emName emOrder, _usrIdC, _grpIdC, _tsC, _usrIdM, _grpIdM, _tsM, _deleted FROM dbo.ExampleModel WHERE emId = @emId; END

This is the model class:

public partial class ExampleModel { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int EmId { get; set; } public string EmName { get; set; } public int EmOrder { get; set; } public int UsrIdC { get; set; } public int GrpIdC { get; set; } public DateTimeOffset TsC { get; set; } public int? UsrIdM { get; set; } public int? GrpIdM { get; set; } public DateTimeOffset? TsM { get; set; } public bool Deleted { get; set; } }

This is my generic method:

async Task<T> ICRUDRepository<T>.GetByID(params object[] ids) { var keyProperties = typeof(T).GetKeysProperties(); // returns only the properties with Key attribute var p = new DynamicParameters(); for (int i = 0; i < keyProperties.Count; i++) { p.Add(keyProperties[i].Name, ids[i], keyProperties[i].PropertyType.ToDbType()); }

string spName = $"{_storedProcedurePrefix}{_modelName}Find"; using (IDbConnection conn = Connection) { conn.Open(); var record = await conn.QuerySingleOrDefaultAsync<T>(spName, p, commandType: CommandType.StoredProcedure);

  return record;

} }

Are there any problems building a project in a certain way that uses dapper? I can't explain why this issue never occurs in debugging.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/StackExchange/Dapper/issues/1430, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAEHMBZIDRVH26TNEGNEODRKMW57ANCNFSM4LY3UH6Q .

-- Regards,

Marc

[daviden83]

Only occasionally. But recompiling, even without changing anything in the source code, the problem generally disappears.

[daviden83]

I would also add that the model that I pass to the GetById method actually has a difference compared to all the other models, i.e. the presence of this method:

        public ExampleModel Clone()
        {
            return MemberwiseClone() as ExampleModel;
        }

In addition, the model also has a custom VersionParentAttribute attribute

[daviden83]

Unfortunately for me, after months that the problem had never reappeared, last week it happened again and with a stored procedure that always returns only a set of results. Obviously I happened on a production environment, never that these problems occur in the development environment :) By recompiling the project, the problem has disappeared.

Dapper 2.0.35

[pblackburn68]

I can reporduce the parameters issue by simply calling the same procedure twice. First using .QueryMultiple followed by .QuerySingleOrDefault.

The second call has parameters ParameterNames1 and RemoveUnused. Eg.

var sql = "dbo.ParamIssue";
using (var conn = new SqlConnection(connStr))
{
    var procParams = new DynamicParameters();
    procParams.Add("text", "hello", DbType.String, null);

    var mres = conn.QueryMultiple(sql, procParams, commandType: CommandType.StoredProcedure);
    var res = mres.Read().First();
                
    var sres = conn.QuerySingleOrDefault(sql, procParams, commandType: CommandType.StoredProcedure);

}

where the proc is simply

CREATE PROCEDURE dbo.ParamIssue(@text VARCHAR(100)) AS SELECT @text AS RetValue; GO

Real world example was using dyamic SQL hence the different possible returns

[daviden83]

The problem has never occurred to me anymore, without making substantial changes to the way I make database calls.

[daviden83]

Sometimes the problem recurs and often on a different stored procedure from the previous one. A recycle of the application pool in IIS is sufficient to solve the problem.

But what causes this Dapper issue?

This is the last failed call, intercepted via Profiler:

exec api.sg_AdminHomePageContentUpdate @ParameterNames1=N'HpcId',@ParameterNames2=N'CustId',@ParameterNames3=N'CntId',@ParameterNames4=N'HpId',@ParameterNames5=N'HpccId',@ParameterNames6=N'HpcOrder',@ParameterNames7=N'MmId',@ParameterNames8=N'HpcTsPublishedBegin',@ParameterNames9=N'HpcTsPublishedEnd',@ParameterNames10=N'HpcDescr',@ParameterNames11=N'HpcUrl1',@ParameterNames12=N'HpcUrl2',@ParameterNames13=N'HpcUrl3',@ParameterNames14=N'MmDescr',@ParameterNames15=N'MmHTMLTitle',@ParameterNames16=N'MmHTMLAlt',@ParameterNames17=N'HpctId',@ParameterNames18=N'HpcLabel',@ParameterNames19=N'HpcUrl4',@ParameterNames20=N'HpcPreview',@ParameterNames21=N'MmCaption',@ParameterNames22=N'MmHTMLLongDesc',@ParameterNames23=N'UsrIdC',@ParameterNames24=N'GrpIdC',@ParameterNames25=N'TsC',@ParameterNames26=N'UsrIdM',@ParameterNames27=N'GrpIdM',@ParameterNames28=N'TsM',@ParameterNames29=N'Deleted',@ParameterNames30=N'usrIdM',@RemoveUnused=1

The Stored Procedure returns only one dataset and I use the QuerySingleOrDefaultAsync method with a Dapper.DynamicParameters. But it's as if dapper interprets the DynamicParameters object as an anonymous type or as if it's a model class.

[daviden83]

I use this method to log any SQL exception:

private void LogSQLError(Exception ex, string commandText, object pars, CommandType commandType)
{
   string parsString;
   if (pars is DynamicParameters dynPars)
   {
       var parsList = new List<string>();
       foreach (string parName in dynPars.ParameterNames)
       {
           var parValue = dynPars.Get<dynamic>(parName);

           string parString = $"{parName}: ";
           if (parValue != null)
           {
               if (parValue.GetType() == typeof(string))
               {
                   parValue = "\"" + parValue + "\"";
               }
               string parType = parValue.GetType().FullName;
               parString += $"{parValue} ({parType})";
           }
           else
           {
               parString += "null";
           }

           parsList.Add(parString);
       }
       parsString = string.Join("\n", parsList);
   }
   else
   {
       parsString = JsonConvert.SerializeObject(pars);
   }

   _logger.LogCritical(ex, "SQL Exception with commandType '{0}', commandText '{1}' and parameters {2}", commandType, commandText, parsString);
}

And this is the log message:

SQL Exception with commandType 'StoredProcedure', commandText 'api.sg_AdminHomePageContentUpdate' and parameters 
HpcId: 16497 (System.Int32)
CustId: 297 (System.Int32)
CntId: 16590 (System.Int32)
HpId: null
HpccId: null
HpcOrder: null
MmId: null
HpcTsPublishedBegin: null
HpcTsPublishedEnd: null
HpcDescr: "Test Description" (System.String)
HpcUrl1: null
HpcUrl2: null
HpcUrl3: null
MmDescr: null
MmHTMLTitle: null
MmHTMLAlt: null
HpctId: null
HpcLabel: null
HpcUrl4: null
HpcPreview: "" (System.String)
MmCaption: "" (System.String)
MmHTMLLongDesc: null
UsrIdC: 559 (System.Int32)
GrpIdC: 1 (System.Int32)
TsC: 15/09/2021 14:55:33 +00:00 (System.DateTimeOffset)
UsrIdM: 559 (System.Int32)
GrpIdM: 1 (System.Int32)
TsM: null
Deleted: False (System.Boolean)
usrIdM: 559 (System.Int32)
Microsoft.Data.SqlClient.SqlException (0x80131904): Procedure or function 'sg_AdminHomePageContentUpdate' expects parameter '@hpcId', which was not supplied.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__188_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at Dapper.SqlMapper.QueryRowAsync[T](IDbConnection cnn, Row row, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 473
   at SG.Core.Utils.Database.DBUtil.SG.Core.Utils.Database.IDBUtil.ReadSingleOrDefault[T](String commandText, Object pars, CommandType commandType)
ClientConnectionId:c93f9d06-e7bc-4abc-aff3-15efbff9b7a1
Error Number:201,State:4,Class:16

This is the method that I use for this specific stored procedure call (but I also use other methods to call other stored procedures based on result they need to return, none, scalar, single dataset, multiple dataset): Interface:

Task<T> ReadSingleOrDefault<T>(string commandText, object pars = null, CommandType commandType = CommandType.StoredProcedure);

Class

async Task<T> IDBUtil.ReadSingleOrDefault<T>(string commandText, object pars, CommandType commandType)
{
    try
    {
        using IDbConnection conn = Connection;
        conn.Open();
        var record = await conn.QuerySingleOrDefaultAsync<T>(commandText, pars, commandType: commandType);

        return record;
    }
    catch (Exception ex)
    {
        LogSQLError(ex, commandText, pars, commandType);
        throw;
    }
}

[mgravell]

It seems unlikely, but worth checking: have you tried using hpcId rather than HpcId in your parameter definition? I'm thinking in terms of case sensitivity

On Thu, 16 Sept 2021 at 08:36, daviden83 @.***> wrote:

I use this method to log any SQL exception:

private void LogSQLError(Exception ex, string commandText, object pars, CommandType commandType) { string parsString; if (pars is DynamicParameters dynPars) { var parsList = new List(); foreach (string parName in dynPars.ParameterNames) { var parValue = dynPars.Get(parName);

       string parString = $"{parName}: ";
       if (parValue != null)
       {
           if (parValue.GetType() == typeof(string))
           {
               parValue = "\"" + parValue + "\"";
           }
           string parType = parValue.GetType().FullName;
           parString += $"{parValue} ({parType})";
       }
       else
       {
           parString += "null";
       }

       parsList.Add(parString);
   }
   parsString = string.Join("\n", parsList);

} else { parsString = JsonConvert.SerializeObject(pars); }

_logger.LogCritical(ex, "SQL Exception with commandType '{0}', commandText '{1}' and parameters {2}", commandType, commandText, parsString); }

And this is the log message:

SQL Exception with commandType 'StoredProcedure', commandText 'api.sg_AdminHomePageContentUpdate' and parameters HpcId: 16497 (System.Int32) CustId: 297 (System.Int32) CntId: 16590 (System.Int32) HpId: null HpccId: null HpcOrder: null MmId: null HpcTsPublishedBegin: null HpcTsPublishedEnd: null HpcDescr: "Influencer Marketing 2021 " (System.String) HpcUrl1: null HpcUrl2: null HpcUrl3: null MmDescr: null MmHTMLTitle: null MmHTMLAlt: null HpctId: null HpcLabel: null HpcUrl4: null HpcPreview: "" (System.String) MmCaption: "" (System.String) MmHTMLLongDesc: null UsrIdC: 559 (System.Int32) GrpIdC: 1 (System.Int32) TsC: 15/09/2021 14:55:33 +00:00 (System.DateTimeOffset) UsrIdM: 559 (System.Int32) GrpIdM: 1 (System.Int32) TsM: null Deleted: False (System.Boolean) usrIdM: 559 (System.Int32) Microsoft.Data.SqlClient.SqlException (0x80131904): Procedure or function 'sg_AdminHomePageContentUpdate' expects parameter @.***', which was not supplied. at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__188_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 where exception was thrown --- at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot) --- End of stack trace from previous location where exception was thrown --- at Dapper.SqlMapper.QueryRowAsync[T](IDbConnection cnn, Row row, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 473 at SG.Core.Utils.Database.DBUtil.SG.Core.Utils.Database.IDBUtil.ReadSingleOrDefault[T](String commandText, Object pars, CommandType commandType) ClientConnectionId:c93f9d06-e7bc-4abc-aff3-15efbff9b7a1 Error Number:201,State:4,Class:16

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/DapperLib/Dapper/issues/1430#issuecomment-920662847, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAEHMANIQ5WAHAOKGENQODUCGM6TANCNFSM4LY3UH6Q . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

-- Regards,

Marc

[daviden83]

No I have not tried, also because generally the Dapper methods do not have problems of case sensitivity with the parameter's names, in fact this is a problem that occurs rarely (once every 3 months, and it is an application installed on dozens of web sites), but the strangest thing is that it looks random and resolves itself with a simple application pool recycle.

To pass the parameters to the Dapper methods, I generally start from a model with the properties defined in PascalCase, and then through methods that exploit the reflection I create the DynamicParameters object that I pass to the Dapper methods.

[daviden83]

I was able to reproduce the problem. It occurs when calling the same stored procedure first with the QueryMultipleAsync method + ReadResultAsync and then with the QuerySingleOrDefaultAsync method (also with not async methods) and only if the parameters are passed through the DynamicParameters object: if I use an anonymous object I have no problems (but unfortunately for me it is not a solution because in some cases I have output parameters). I state that in some cases I use the QueryMultipleAsync method also to call stored procedures that return a single dataset, this is because when I call the stored procedure I do not know regardless how many datasets it returns: through reflection I obtain the result of the stored procedure by converting it into the type of item I need.

Try this simple test case:

CREATE PROCEDURE [dbo].[sp_DapperTest]
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	@Id AS Id, 'descr' as Descr;
END

public class TestDapper
{
	public int Id { get; set; }
	public string Descr { get; set; }
}

using IDbConnection conn = new SqlConnection(_config.GetConnectionString("mystrinconnection"));;
conn.Open();
string spName = "[dbo].[sp_DapperTest]";
var parameters = new DynamicParameters();
parameters.Add("Id", 2, DbType.Int32);

var reader = await conn.QueryMultipleAsync(spName, parameters, commandType: CommandType.StoredProcedure);
while (!reader.IsConsumed)
{
    _ = await reader.ReadResultAsync<TestDapper>(); // without this line and loop, the next call works
}
_ = await conn.QuerySingleOrDefaultAsync<TestDapper>(spName, parameters, commandType: CommandType.StoredProcedure); // this will fail with "Procedure or function sp_DapperTest has too many arguments specified." error

using IDbConnection conn = new SqlConnection(_config.GetConnectionString("mystrinconnection"));;
conn.Open();
string spName = "[dbo].[sp_DapperTest]";

var parameters = new { Id = 2 };

var reader = await conn.QueryMultipleAsync(spName, parameters, commandType: CommandType.StoredProcedure);
while (!reader.IsConsumed)
{
    _ = await reader.ReadResultAsync<TestDapper>();
}
_ = await conn.QuerySingleOrDefaultAsync<TestDapper>(spName, parameters, commandType: CommandType.StoredProcedure); // this works

[daviden83]

From what I have noticed, the problem occur in exactly this case:

1. A stored procedure is first called by the QueryMultiple method.
2. Parameters are passed using a DynamicParameters object.
3. The results are read using the generic GridReader.Read<T> method.
4. The same stored procedure is called with another generic method whose name begins with "Query" (Query<T>, QuerySingle<T>, etc.) using a DynamicParameters object and the same T type used in Read method.

However, the problem does not occur if one of these conditions occurs:

• The stored procedure is called for the first time with a generic method whose name begins with "Query" other than QueryMultiple.
• The stored procedure is called not using a DynamicParameters object.
• If the QueryMultiple method is used without reading the data with the generic Read method.
• If you call the stored procedure using a non-generic method or methods whose name begins with "Execute".
• If you call the stored procedure using a generic methods whose name begins with "Query" but with T type different from that used in the Read method.

I have attached the source to easily test the various cases, developed in .NET5 (WinForm). When starting the executable, if you use the "QueryMultiple" button, all the "Query .... Typed" buttons will fail until the executable is restarted TestDapper.zip

[daviden83]

@mgravell

Any news about my latest posts? The test posted by pblackburn68 also highlights the problem: https://github.com/DapperLib/Dapper/issues/1430#issuecomment-760516483

[daviden83]

I have debugged into the Dapper source and I think I've found the problem: inside the ReadImpl <T> method called by the Read<T> method (SqlMapper.GridReader.cs file), the GetCacheInfo is called but passing the value for the exampleParameters parameter to null. This then causes the problem in subsequent Query calls.

I found two possible solutions:

• In the SqlMapper.GridReader.cs file, modify the call to the GetCacheInfo method in the ReadImpl<T> method, passing the value false to the addToCache parameter.
• In the SqlMapper.cs file, modify the call to the GridReader contructor in the QueryMultipleImpl method, passing the value false to the addToCache parameter.

Waiting for an official fix that I hope you can make as soon as possible, which of the two solutions do you recommend? Could there be any contraindications to making this type of change?

Thanks.

[daviden83]

I found a better solution, without modifying the Dapper source. Change the call to QueryMultiple method from like this:

var reader = conn.QueryMultiple (spName, pars, commandType: CommandType.StoredProcedure);

to this:

var cmd = new CommandDefinition (spName, pars, commandType: CommandType.StoredProcedure, flags: CommandFlags.NoCache);
var reader = conn.QueryMultiple (cmd);

Passing the CommandFlags.NoCache parameter I get the same effect obtained by modifying the Dapper sources as I indicated in the previous message.

However, I think a fix from the Dapper library is appropriate because by default it shouldn't have this problem.

[daviden83]

Any news about it? Is there a chance that the problem will be fixed in future releases?

[BrandonBoone]

Also facing the same issue using QueryAsync and DynamicParameters. As @daviden83 said earlier, recycling IIS seems to clear up the issue, but it happens sporadically / randomly. This is on Dapper 1.60.6.

[daviden83]

@BrandonBoone But you call the same stored procedure also with QueryMultiple in source code? If yes, you can solve it by doing what I wrote in this comment: https://github.com/DapperLib/Dapper/issues/1430#issuecomment-937784460

[BrandonBoone]

@daviden83, unfortunately no, my code doesn't match that pattern, but thanks for the suggestion!

I'm using Dapper behind an HTTP API (.net Core 3.1) serving individual requests with simple queries to stored procedures. No multiple responses, etc. I am handling a significant volume of requests though so queries could be repeated by individual requestors, but I'm certainly not calling the same stored proc multiple times within the same http request (thread).

So far I've been trying to avoid using DynamicParameters altogether, but this is more difficult in some cases than others. I've also failed to repeat this issue outside of the production environment, which makes it hard to reproduce & test against.

EDIT: Looking at your suggestion again, I will try switching to using a CommandDefinition to see if that helps. Thanks again.

[daviden83]

Yes, I hope the flags = CommandFlags.NoCache parameter can solve your problem too.

[daviden83]

The bug, after 2 years, still persists in the latest version 2.0.123

[derekatcoats]

Bug is still there in release 2.1.28 on .Net 8.0.

Thanks @daviden83 for the NoCache workaround 👍