Power By 专否社区

SQL Server 2005 Case Sensitive when queried via C#

Go To StackoverFlow.com

4

I've run into an issue installing a product that has worked at several customer sites just fine, I believe the issue has to do with the Collation setting on their database server. I have code that looks like this (I changed table and variables names around since the code is proprietary):

using (SqlCommand insertCommand = dbConnection.CreateCommand())
{
    insertCommand.CommandText = "INSERT INTO [myTable] ([valueOne] ,[valueTwo] ,[CreationDate]) VALUES (@valueTwo ,@valueTwo ,@creationDate);select IDENT_CURRENT('myTable');";
    insertCommand.Parameters.AddWithValue("@valueOne", "Value One");
    insertCommand.Parameters.AddWithValue("@valueTwo", "Value Two");
    insertCommand.Parameters.AddWithValue("@CreationDate", CreationDate);                    
    dbConnection.Open();
    object result = insertCommand.ExecuteScalar();
    dbConnection.Close();
}

This works at most sites and on our development and QA machines, but at this one site we receive an error that says "Must declare scalar variable "@creationDate". The main discrepancy I see is that the site has the collation value set to SQL_Latin1_General_CP1_CS_AS and our settings are all SQL_Latin1_General_CP1_CI_AS. I changed this for our database but the server is still set to the Case Sensitive variation. This did resolve another issue we were having with a different table (that one was the table name, not a parameter name), but for some reason it's still a problem for this. Does anyone have any ideas on how I can resolve this a little more quickly than going through and fixing all of the case differences in our code base?

The site is using SQL Server 2005, and our code is written in C# and .NET 3.5.

Thanks -Hollis

2012-04-03 21:33
by Hollis
Do you have the ability to change it at the server level as well as the database level? Or is the case sentivity requied at the server level for some reason - Dan P 2012-04-03 21:41
The instance (server) collation cannot be easily changed, and changing it on the database level doesn't fix the issue since variables (including arguments) are always instance-relative (see my answer) - Lucero 2012-04-03 21:54
The command text includes @creationDate (lowercase) and you try to pass the param in upper case. I don't remember if this is case sensitive and can't check right now - JotaBe 2012-04-03 22:29

4

The collation of the server controls those, this is by design.

From BOL (emphasis mine):

The collation of an identifier depends on the level at which it is defined. Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance. Identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of the database. Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database and then referenced when the context has been switched to another database. Therefore, the identifiers for variables, GOTO labels, and temporary tables are in the default collation of the instance.

2012-04-03 21:52
by Lucero
Lucero, thanks so much for your answer. I kind of suspected I was out of luck on this, but at least now I understand the reasons behind it which is always a plus - Hollis 2012-04-04 00:11

2

The case sensitivity of your variables is controlled by the server level collation, not database level. See this question on the same topic.

2012-04-03 21:42
by Blake Mitchell
From his question it wasn't clear if he could modify the server level collation. It sounded to me like he was only able to modify it the database level otherwise he would not have stated.

"I changed this for our database but the server is still set to the Case Sensitive variation. This did resolve another issue we were having with a different table (that one was the table name, not a parameter name), but for some reason it's still a problem for this. - Dan P 2012-04-03 21:46

Thanks Blake, I probably can't change the server here so I think I'll have to fix it in the code...not the worst thing, and probably the best solution longterm anyway - Hollis 2012-04-04 00:15
Ads