We are using Win NT and SQL server 2000 When I run a stored procedure, I get Server: Msg 137, Level 15, State 2, Procedure pr_delopp, Line 29 Must declare the variable '@oppid'.
This is because variable @oppid is declared as @OppID (different case). The SQL server 2000 installation which is on my own machine was done with Case sensitive. But we created a database as case insensitive.. CREATE DATABASE A4V42 ON (NAME = A4V42Data, FILENAME='F:\mssqldata\MSSQL$S1171A\data\A4V42Data.dat', SIZE=50MB) LOG ON (NAME = A4V42Log, FILENAME='F:\mssqldata\MSSQL$S1171A\data\A4V42Log.dat', SIZE=10MB) /* COLLATE Latin1_General_CI_AI */ COLLATE Latin1_General_CI_AS GO
I tried both COLLATE Latin1_General_CI_AI and COLLATE Latin1_General_CI_AS GO
Why is this variable case sensitive inside a procedure even though the database is set up case insensitive ? Does it take the default set on the server rather than database ? I am using this variable @oppid to hold intermediate values and not for temporary table.
How do I fix it, if I want to do this only for this database and not for the whole server ( all other databases) ? Is this a bug in SQL server 2000 ?