collation error

  • 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 ?


    Sonali Kelkar

  • If you declare it as @oppid, does it work?

    Haven't really worked with collation, but I will try to alter a server here and see.

    Steve Jones

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply