Thomas, thanks very much for picking this up.
In SSMS connected directly to the SQLServer2005 server, when I run:
select top 5 RECORDDATE from SecurityMaster;
I get
Msg 207, Level 16, State 1, Line 1
Invalid column name 'RECORDDATE'.
Running
select top 5 RecordDate from SecurityMaster;
runs fine and returns the first five records.
I see the same behaviour when I run the queries in DBVis via the same
SQLServerDriver jdbc driver I am using for the linked table.
What does the linked table feature use to check the case sensitivity of the
SQLServer?
Is there a way I can quote the column names in query to the linked table
that will bypass the autoconversion to uppercase?
Thanks, Peter
On Saturday, April 27, 2013 1:14:22 AM UTC-7, Thomas Mueller wrote:
>
> Hi,
>
> H2 converts all case insensitive identifier names to uppercase internally.
> But I would expect if the identifiers are case sensitive in MS SQL Server,
> then this should not happen. Maybe the implementation of the linked table
> feature is different however; unfortunately I don't have MS SQL Server
> installed right now so I can't easily test it.
>
> Are the column names actually case sensitive, that means if you run
> "select RECORDDATE from SecurityMaster" in the MS SQL Server, does it
> throw an exception?
>
> Regards,
> Thomas
>
>
>
>
> On Sat, Apr 27, 2013 at 4:07 AM, Peter <[email protected] <javascript:>>wrote:
>
>> Bump. Should I provide more information? Is this even possible? Thanks,
>> Peter
>>
>>
>> On Thursday, April 25, 2013 9:34:29 AM UTC-7, Peter wrote:
>>>
>>> I am having trouble with column name case sensitivity with linked tables
>>> in SQL Server 2005.
>>>
>>> Example. The column name in SQL Server is RecordDate in the table
>>> SecurityMaster
>>>
>>> I create a linked table as:
>>>
>>> create linked table if not exists
>>> L_SecurityMaster(
>>> 'com.microsoft.sqlserver.jdbc.**SQLServerDriver',
>>> 'jdbc:sqlserver://feedqa\**SQL2005:1773;DatabaseName=**testdb',
>>> 'user', 'password',
>>> 'dbo.SecurityMaster');
>>>
>>> Executing show columns from L_Securitymaster shows all the columns in
>>> capitals, i.e. RECORDDATE instead of RecordDate.
>>>
>>> Various combinations of [RecordDate], 'RecordDate' and so on all fail
>>> for one reason or another. I tried to search documentation but sorry I
>>> can't see what I am supposed to do. Can anyone help me with the correct way
>>> to link and query MS SQL Server tables with case sensitive column names?
>>>
>>> Many thanks, Peter
>>>
>>>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> To post to this group, send email to [email protected]<javascript:>
>> .
>> Visit this group at http://groups.google.com/group/h2-database?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
