collation error

• sonaliak

  Default port

  Points: 1426

  More actions

  July 19, 2001 at 9:32 am

  #157502

  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 ?

  thanks

  Sonali Kelkar

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738140

  More actions

  July 20, 2001 at 12:43 pm

  #417215

  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

  steve@dkranch.net