Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2005-12-13 : 18:32:20
|
| Hi,A client recently installed a database we had created at their company office. It appears their database is set to be case sensitive. One of the first sprocs that is called is failing.Something like this:Create Procedure MyTest(@State varchar(2))ASSelect * from Table where State = @stateGoThe Error says "Must declare the variable '@state'". Notice the state in the error msg is lower case. The code that calls this sproc is "@State". Permissions seem to be fine, so it appears to be a case sensitivity issue. I have read you can change the Collate properites of a database to be case sensitive/insensitive etc. The articles I have read relate to columns being case sensitive etc. I haven't seen anything about sprocs. Our application has hundreds of sprocs. To convert them would be a MAJOR undertaking.How would you switch a database back to being case insensitive?Nic |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-13 : 19:09:16
|
| Note that if you run a database on a server with a different collation then TEMPDB will use the server's collation - so any #TempTable you use will need to a) be pre-CREATED [rather than doing a SELECT * INTO #TempTable FROM MyTable] and b) have COLLATE statements on every column with a char/varchar/text datatype.Also any column logic operator (e.g. Col_A = Col_B) where the two columns in question are from databases with different collation (e.g. one is using a table from the MASTER database, and the other is in the local database) will need to have an explicit COLLATE.Another approach you could consider is to convert all "column name" and "variable" names to a consistent case - e.g. upper case. You could use a tool like SED ["Sequential Editor"] to globally replace them using a regular expression.If you are planning to sell an application to a wide audience then having your QA server use a different collation to your DEV server is a Good Thing!Sorry, not much help with your original question, but I don't reliably know that answer to that.Kristen |
 |
|
|
|
|
|
case sensitive sprocs