Sunday, March 25, 2012

Case sensitive variables SQL Express

Hi,
I suddenly ran into a serious problem. We installed a new SQL Express 2005
SP1 on a different server and now it seems to be case sensitive with the
variables in stored procedures:
----
ALTER PROCEDURE [dbo].[GetUserID] (@.UserName varchar(50))
AS
BEGIN
DECLARE @.ret bigint;
SET NOCOUNT ON;
SELECT @.ret = UserID FROM Users WHERE UserName Like @.Username;
RETURN ISNULL(@.ret,-1);
END
----
It fails on the @.Username which is declared as @.UserName
This did not happen on our other SQL Express server. It does not have SP1
either.
Please help!!
DavidHi David,
Most likely your problem is related to different instance collation on the
different servers. All identifiers for variables, GOTO labels and temp
tables are in the default collation of the instance. Probably your new
instance of SQL Server Express is installed with a case sensitive collation.
Here is how to change it if needed:
http://msdn2.microsoft.com/en-us/library/ms179254.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com

No comments:

Post a Comment