Re: SQL server application porting headache

Curt Sampson wrote:
> On Mon, 24 Jun 2002, Curt Sampson wrote:
>
>
>>On Sun, 23 Jun 2002, Oskar Berggren wrote:
>>
>>
>>>>>        SELECT "OperatorID" FROM "IntParams" WHERE OperatorID=0;
>>>>>                                                   ^^^^^^^^^^
>>>>>                                                  notice no quotes here
>>>>
>
> Well, it's worse than I thought. Using SQL Server 7.0, I created a table
> test1 with a column name "ColOne". In the Query Analyzer:
>
>     SELECT "ColOne" FROM test1 WHERE "ColOne" = 7
>
> All right so far. But removing either set of quotes also works. Until
> you change case, where it breaks even without quotes. (I guess I should
> have tested with fully uppercase and fully lowercase column names, too,
> but subsequent events addled me so much I lost the thought.)
>
> So after this I move to isql, where doing it without quotes also works,
> so long as I get the case right. (Wrong case without quotes still
> fails.) But better yet, you can quote "ColOne" in the SELECT part of
> the statement, but if you try to quote "ColOne" in the WHERE portion of
> the statement, it fails with some error message about being unable to
> convert a varchar to a column type or something. So you can't quote in
> the WHERE clause at all, except with [] instead of "".


Thanks for your research! I appreciate it. Now at least I know why it
is like it is. Perhaps this is sufficient argument to get them to
correct their queries.


> At this point I started to realise why the application designers may
> have done their queries in the way shown above. I then poured myself a
> very large Suntory whisky, booted back into NetBSD, and left it at that.

Tempting, although I'm not that fond of whisky myself. But I was out
drinking beer all night long so perhaps I've had my fun for this
weekend. :)


regards,
Oskar

--
/----------------------------------------------------------------------\
| Oskar Berggren        beo@sgs.o.se                                   |
| Network and Software Engineer                     SGS Datanätgrupp   |
|                                                   Gothenburg, Sweden |
\----------------------------------------------------------------------/