3

When I execute this format of SQL command: SP_HELPTEXT Sproc1 . The result set will display Could not find stored procedure 'SP_HELPTEXT'. But if i will replace the SQL command to lower case like sp_helptext Sproc1 , it definitely displays the content of Sproc1.

Im using the Sproc1 in my program and when the program executes Sproc1 it will return a message:

Must declare the variable '@Variable1'.

Although I've already declared that specific variable.

I have a hint that the issue is related to collation, case-sensitive or insensitive settings. Does anybody know how to resolve ?

Another situation where case sensitive variable names appear:

CREATE PROCEDURE Foo @customerID int AS
PRINT @customerId
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
RedHat
  • 205
  • 2
  • 12
  • 24
  • Well, *is* the variable case different? I mean, it could be simply that it isn't declared (in scope) .. create/post a minimal test-case showing the issue. –  Jan 11 '13 at 03:09
  • whats the collation on the instance? Use below tsql to find collation ? SELECT DATABASEPROPERTYEX('DatabaseName', 'Collation') SQLCollation; – Hiten004 Jan 11 '13 at 03:10
  • @Hiten004, i have provided the collation above. – RedHat Jan 11 '13 at 03:18
  • Change Databasename to your database! – Hiten004 Jan 11 '13 at 03:20
  • @Hiten004, see above changes. – RedHat Jan 11 '13 at 03:24
  • here is the more detail on [sql_latin1_general_cp1_ci_as](http://stackoverflow.com/questions/5039211/what-does-collate-sql-latin1-general-cp1-ci-as-do)...... [Transfer a database from one collation to another collation](http://support.microsoft.com/kb/325335) – Hiten004 Jan 11 '13 at 03:28

1 Answers1

4

You have a case sensitive server collation.

Your database has a (as you have shown) a case insensitive collation but when you have a case issue with variables it is the server collation that matters.

The same goes for sp_helptext which is a stored procedure defined in database master with lowercase. So when you call SP_HELPTEXT it is not found.

To fix your stored procedure to work in a case sensitive server collation you have to make sure that every reference to the variable @Variable1 is exactly that. Not @variable1 or @VARIABLE1.

Use this to check what server collation you have.

SELECT SERVERPROPERTY('collation');

From the SQL Server Books Online:

COLLATE (Transact-SQL)

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.

    For example, two tables with names different only in case may be created in a database with case-sensitive collation, but may not be created in a database with case-insensitive collation. For more information, see Database Identifiers.

  • The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the server instance.

    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.

See also

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • The server collation is Latin1_General_BIN. I have a multiple database in my local machine and i think it's very tedious to modify every trigger and stored procedure. I have not encountered such issue when I'm using Win XP 32 bit but now i migrated to Win 7 64 bit OS , i just hope that its not connected on the problem. I would like to ask if there's a way on how to modify the server collation , please help. Thanks. – RedHat Jan 11 '13 at 09:13
  • It i possible to change the server collation but it is not easy. [Set or Change the Server Collation](http://msdn.microsoft.com/sv-SE/library/ms179254.aspx) – Mikael Eriksson Jan 11 '13 at 09:22
  • Eventhough my question has been closed as off topic i would like to share how did i resolved the issue and i would like to thank the comment from Mikael because it pinpoint the root cause. I've followed the steps from this link, http://www.sp-configure.com/change-sql-server-default-collation/ – RedHat Jan 11 '13 at 14:05
  • But the process took very long then I've forced to stop the ongoing process. I've decided to uninstall the SQL Server 2000 , restart the machine then install it again and during the installation setup I've chosed the proper server collation. The system stored procedures(Transact-SQL) is now working fine either it was typed and execute in lower or upper case. – RedHat Jan 11 '13 at 14:05
  • @Alex, good to hear that you have solved the issue. My "vote to close" was a vote to move the question to [DBA](http://dba.stackexchange.com/) where your question would have been seen by more expertise regarding this than I can provide and perhaps you would have received different/better advice there. – Mikael Eriksson Jan 11 '13 at 14:30