Must declare the scalar variable "@UserId".

We are doing server migration testing. I've restored a DB from production to staging server. I setup up a ASP.NET application and point to production server, everything work fine but when point to staging server, error below appear. By the way, we are using MS SQL 2005 standard edition in both server.

Server Error in '/' Application.

Must declare the scalar variable "@UserId".
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@UserId".

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): Must declare the scalar variable "@UserId".]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62
Dal.GetRecentRater(Int32 PostId, DataTable dt) +190
Post.BindBlogPost() +1295
Post.Page_Load(Object sender, EventArgs e) +49
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061










At this point, i suspect something wrong with the staging server DB. So i access to the staging server and execute the stored procedure in Query Panel and it give me some error message.
Must declare the scalar variable "@UserId".






I open and check the stored procedure and the stored procedure is simple

ALTER PROCEDURE [dbo].[GetUserDetails]
@UserId int
as
select * from Users where UserId = @UserID

After check for a while, i change the @UserID to @UserId and magic happened. Problem solved. It's case sensitive.

It's strange because this stored procedure running in production server without any problem. Collation for both MS SQL 2005 DB are SQL_Latin1_General_CP1_CI_AS.