| Author |
Topic |
|
hrishi_des
Yak Posting Veteran
76 Posts |
 Posted - 2007-08-27 : 07:10:25
|
| Hi,I have a SQL server which has case sensitive collation.My database has case insensitive collation.following code fails as varaiable is declared in upper case and and used as small case.declare @SQL varchar(100)set @sql = 'ME'print @sqlDo you know any way to solve this problem without changing script (there loads of script) and server collation to case insensitive (cannot be done at client place).Thanks in advance.HrishikeshWhen solution is simple, God is answering…. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-27 : 07:13:38
|
You want this done without1) Changing the script2) Changing the server default collationYou maybe are allowed to change the column collation for the specific column in the table?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-08-27 : 07:29:35
|
| i have proper database_default collate clause specified on all temporary table and table variable, my issues is now only with variable declaration and use. And i think if i make variable names consistant, my code should work, correct me if i am wrong, may be i am missing some other thing.Making all variable names across all script is cumbursome process. Was thinking if i open each script file, select and press Ctrl + Shift + i would change it to lower case adn then execute it.When solution is simple, God is answering…. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-27 : 21:23:05
|
| But that can't control system created temp objects during sorting or so. |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-08-28 : 01:17:35
|
| Hi rmiao,how system created temp objects would create problems? can u pls elobarate.Many Thanks,HrishikeshWhen solution is simple, God is answering…. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-28 : 20:55:58
|
| It'll use server collation that maybe different from your collation. |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-08-29 : 01:22:19
|
| I think system created temp objects for sorting won't create problem as long as i am not refering it directly.When solution is simple, God is answering…. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 07:04:04
|
| We use an explicit COLLATE statement on ALL Table Create (including CREATE TABLE #TEMP and DEFINE @TableVar TABLE). I can;t see that "database_default" will help on TEMPDB as it will be using Server Collation, rather than the collation of your database (which is what you actually need to enforce)We want our database, and application, to be running in an "anticipated and tested" collation, rather than whatever the client's server may be using.We force collation on all inter-database comparison, so for example:...WHERE DB1.dbo.MyTable1.MyCol1 = DB2.dbo.MyTable2.MyCol2 COLLATE SQL_Latin1_General_CP1_CI_AShowever note that his approach is likely to force a recompile.Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-29 : 07:22:34
|
Kristen, I think OP originally wrotequote:
Originally posted by hrishi_des
Do you know any way to solve this problem without changing script (there loads of script) and server collation to case insensitive (cannot be done at client place).
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 08:58:35
|
| Missed that, thanks Peso. Only effects CREATE TABLE variations though, and a Text Search of scripts may reveal that there are not too many of those.If DATABASE_DEFAULT is used to Collate all new tables within the database then the text find is only needed for CREATE TABLE #Temp and DEFINE @TableVar TABLE statements, which are hopefully not in too many scripts!Kristen |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-08-29 : 09:20:46
|
| Hi Kirstan,When we create Database we specify collation we require, and when we create Temp Table or table variable we use database_default option. Our findings so far has seems to be working fine, our only problem is variable declaration as i mentioned in my problem above.Thanks,HrishikeshWhen solution is simple, God is answering…. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 09:34:15
|
| "when we create Temp Table or table variable we use database_default option"hadn't thought to try that, but I would have assumed you would have got the collation of TEMPDB, and NOT the database you were working on (which could easily be different, of course)Anyway, I have now read your question more carefully!I have read of developers who develop on case-sensitive databases in order that there are no issues like case-sensitive-code/keywords. We just use a different collation during QA because I don;t think we would be prepared to tolerate installing on a client's server if it was configured "case sensitive", Period!!Install another instance on that server with a more appropriate collation?Kristen |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-09-03 : 02:01:00
|
| Hi Kirsten,Thanks for the reply, but here i am talking about a product which may be installed on a shared SQL server managed by third party for a client, and due to cost factors creating new instance may not be feasible for the client (In most cases client will have a dedicated server (95%)). we may have to supprort cases where server collation can be case sensitive. so that is why i was trying for a solution to above problem. Hope this explains my problem.Regards,HrishikeshWhen solution is simple, God is answering…. |
|
|
|
|
Case sensitive collation