Hey, I'm having an issue that appears to be related to collation, but I'm not sure.
I'm developing against a backup of my production database. The only difference in the SQL Server 2005 environments (that I'm aware of right now) is that my localhost has "SQL_Latin1_General_CP1_CS_AS" (Case sensitive) as the collation, and the production environment has "SQL_Latin1_General_CP1_CI_AS" (case insensitive).
When I execute the stored procedures locally, it is throwing "Must Declare Scalar Variable "@firstName"" errors.
The variable "@FirstName" has been declared.
Now, the database itself IS "SQL_Latin1_General_CP1_CI_AS" as the collation, so why are the stored procedures not running in a case insensitive context?
I've looked into this online, and they say basically that I have to rebuild my SQLserver.
Shouldn't the database context trump the server context for collation?
BTW, the master and tempdb are both "SQL_Latin1_General_CP1_CS_AS" Case Sensitive.
Element RECORDCOUNT is undefined in “yyReslt”
Cannot connect to the sql server
prevent an SQL Server Admin from changing a user in the database?
Anything where you don't control the collation (e.g.
Is performance hit hard when you reinitialize a subscription in sql server 2005
SELECT INTO a temporary table) will break when you try to do anything involving varchars.
SQL Server script: ALTER PROCEDURE - Executing multiple ALTER PROCEDURE into one script without having to select each of the ALTER one after another
The server default collation will trump anything where you don't explicitly coerce the collation, which will be most if not all of your existing stored procedure code.
created persisted computed columns when the user defined scalar function appears to be non-deterministic
How to temporarily disable the log in SQL2000/2005?
The normal solution to this problem is to reinstall the SQL Server instance with the correct collation.
SqlServer 2005: deadlock problem with no shared records
Yes, you really do need to do that.
My knowledge of this is secondhand, but I understand (from someone who got MS technical support involved in this issue) is that it is technically possible to change the default collation on a SQL Server instance but the process is technical and error prone, and making a mistake can leave the server in an unusable state.
Apparently this procedure is not officially supported by Microsoft and the recommended procedure is to reinstall the SQL Server instance..