DAAB - The number of parameters does not match number of values for stored procedure

Topics: Data Access Application Block
Sep 30, 2008 at 12:42 AM

I'm using user defined types (UDT) with SQL 2005, VS 2008, and EntLib 3.1. My tables contain columns defined with UDTs, so the stored procedures, that I use to access and manipulate the data, have parameters defined with UDTs. Everything works fine, when I call such a procedure through DAAB, if the user has db_owner permissions, but I get the error "The number of parameters does not match number of values for stored procedure", if the user is just a member of a role, that has execute permission on the stored procedures. In Management Studio, the user can call the procedures without the error. When I change stored procedure parameters to "normal" (not UDT) types, everything works fine, the user can call them through DAAB, without the error.
Does anybody have any idea on this? How can I use UDTs, without giving the db_owner permission to users?

Sep 30, 2008 at 2:45 PM
Hi Peter,

How are you invoking the stored procedures? Are you relying on paramter discovery (ie supplying the parameter values instead of setting the parameters manually)?


Sep 30, 2008 at 3:24 PM
Hi Fernando,

Thanks for your answer.
I use parameter discovery with ExecuteDataSet, ExecuteScalar and ExecuteNonQuery methods.

Sep 30, 2008 at 3:52 PM
Test it using explicit parameters as described in http://msdn.microsoft.com/en-us/library/ms131080.aspx to try and isolate the issue.

Sep 4, 2009 at 2:32 PM


Finaly I found a solution for the problem. It's a permission related problem. The user can't reference the UDT without explicit permissions. The following SQL solves the problem:

GRANT REFERENCES ON TYPE::[typename] TO [username]

I'm not sure, what's the difference between calling the stored procedure through Management Studio (which works without the permission) and Enterprise Library, but somehow the EntLib's parameter discovery causes it.


Apr 9, 2010 at 1:10 PM

I was having the same error message reported. The issue was that there was an error in the stored procedure itself. After I ran the stored procedure without issues, this error went away.