"The wrong number of parameters does not match number of values for stored procdure" System.Data.Common.DbCommand nuance.

Question

This could be an sql server database setup issue, but I am not to sure where to start looking.

I have a stored procedure :

CREATE PROCEDURE aStoredProcedure
@dteSince DATETIME = null
AS
...

The c# code to call the stored procedure is :-

using (IDataReader dr = database.ExecuteReader("aStoredProcedure"))
{
...

The c# code works fine on the Production environment. Here, dteSince will be set to null as it is not supplied as a input parameter.

Problem is when I run this in the UAT environment, it returns the message shown in the title - wrong number of parameters...

I can fix this by changing the code to :-

using (IDataReader dr = database.ExecuteReader (CommandType.StoredProcedure, "aStoredProcedure"))
{
...

The fix just masks the real problem as to how can the code fails on one environment and works fine on another.

I have dropped and recreated the stored procedure, with no luck.

Technologies used C# 2.0 & Sql Server 2005.

Answer 1

IMHO the CommandType should always be specified anyway - explicitness is always preferable to implicitness. It is obvious that something is happening under the hood that is different in both environments but when you are explicit about the command type the code works as expected.

The only thing I can think of - are the database settings exactly the same -particularly in the treatment of NULL's?

EDIT

May be worth checking SET ANSI_NULLS ON/OFF and SET CONCAT_NULL_YIELDS_NULL ON/OFF - you can look at the properties of both databases and see if the setting match.

Answer 2

Here are some of the troubleshooting tips I usually try.

1. Check the assembly version of DAL assembly that "database" object type resides in.
2. Check library reference to your DAL - Whether you are referencing the DLL from GAC or not - I had trouble before with DLL referencing GAC, which contained old assembly
3. Check your default database when your code tries to connect to database in UAT environment - It might be pointing to other databases depending on how you are connected to database.

And all's still not well, clean and Rebuild solution

side comment: Beh, I don't think "System.Data.IDataReader" is to be included in technology used ;)

Answer 3

You can use SQL Profiler to see what is happening on the SQL Server perspective.

Ideally getting someone to capture a trace from production as well so they can be compared.