Stored procedures that are case sensitive...

[MarkButler]

I have a number of stored procedures that work in my development environment and at many clients without any problem. I am trying to install the same procedures on a new server and it is requiring that I capitalize the table names in the SELECT/UPDATE/INSERT statements.

All servers are running SQL 2000 with various service packs. This is probably a parameter or setup issue I have not come across yet.

Can someone point me at the parameter or what I need to set to allow my procedures to run as they are now without modifying?

Thanx
Mark

 

[SonOfEmidec1100]

Maybe the sqlserver or the database were created with a different collation code, ie case sensitive collation instead of case insensitive. (or the other way round)

Check database properties/general tab/collation name are they the same ? If the name conatains the string 'CI' then its case insensitive.

 

[MarkButler]

That was the the issue. I did an ALTER DATABASE ana all was fixed. Thanx for the reply.

 

[SQLSister]

You realize that by 'fixing' the collation, you might have broken something else? There may be things on this server that are dependent on the fact that it was set up for case sensitivity. Case sensitivity has to be set up deliberately, it is not the default. I suspect that the person who set it up had a specific reason for doing so. If you didn't take the trouble to find out why it was set up that way, you may have just caused a mess for someone else who in turn will set it up for case sensitivity again, thus breaking your code again.

 

[MarkButler]

Thanks for your concern and caution. The database in question is my own and in my test/development environment it was setup as "SQL_LATIN1_GENERAL_CP1_CI_AS". When I defined the database it defaulted to "LATIN1_GENERAL_BIN" which was the root of my problem. Somewhere during the install of SQL 2000 the installer must pick a default which I did not know about (live and learn).

Mark