2

We have a SQL Server 2014 database with collation SQL_Latin1_General_CP1_CI_AS, which is case insensitive. It is linked to another SQL server database with the same collation. The second database was rebooted last week, and since that time, comparisons in the first database to the second are case sensitive.

For instance, code in the first will have something like:

select *
from [LINK-SERVER].schema.dbo.table tbl
where tbl.fldname = 'tagName.TagValue'

That used to work, even though the data on the linked server had the value 'tagName.tagValue'. After the reboot, that uppercase 'TagValue' doesn't match 'tagValue' and no data is found.

Since this type of code is used in several places (and I've fixed some of them), and I'm still searching for where all it is used - a better solution would be to remind the databases that case is supposed to be ignored.

What is likely the problem, and how do we get them to ignore the case again?

(I am not the DBA, so I'll have to ask for something to be done. I asked for the DBA to reset the link, but either the wording I'm using isn't right, or else that's not what needs to be done.)

Update From the queries in the comments, the first query returns is_collation_compatible = 0, and uses_remote_collation = 1.

The query on name, collation name from sys.columns and sys.tables: it returns SQL_Latin1_General_CP1_CI_AS, but I'm not finding the table explicitly, which makes me wonder if the table name is a synonym.

The query on name, collation_name from sys.databases returns SQL_Latin1_General_CP1_CI_AS for the database.

I have limited access in the database I can get to, and in the linked server properties for the LINK-SERVER, Use Remote Collation is set to true.

And I see that the linked server is SQL Server 2008 SP1 (10.1.2531.1 Intel X86) and I do NOT have 2014, but rather 2012 (SP2-GDR) (11.0.5388.0 X64). I will update my tags.

Update EXEC(N'SELECT SERVERPROPERTY(''collation'');') AT [LINK-SERVER] returns SQL_Latin1_General_CP1_CI_AS

Running the second query with COLLATE SQL_Latin1_General_CP1_CI_AS at the end returns data, but only if the capitalization is correct. If it is not, nothing is returned. I'm guessing this query is to force this collation. It refuses to be moved. At this point, I'm not sure what to do but to consider case important and modify my code.

  • 1
    I would check the use remote collation and collation name settings in the linked server options just to be sure they are what is expected – Bob Klimes Oct 31 '17 at 18:26
  • 1
    First run the following on the local server and update the question with the results: SELECT ss.[name], ss.[is_collation_compatible], ss.[uses_remote_collation], ss.[collation_name] FROM sys.servers ss WHERE ss.[name] = N'LINK-SERVER';. Then, post the results of this query: SELECT col.[name], col.[collation_name] FROM [LINK-SERVER].db_name.sys.columns col INNER JOIN [LINK-SERVER].db_name.sys.tables tab ON tab.[object_id] = col.[object_id] WHERE tab.[name] = N'tbl' AND col.[name] = N'fldname';. – Solomon Rutzky Oct 31 '17 at 18:33
  • 1
    Also wouldn't hurt to get the result of this query added to the bottom of the question: SELECT sd.[name], sd.[collation_name] FROM [LINK-SERVER].db_name.sys.databases sd WHERE sd.[name] = N'remote_db_name'; – Solomon Rutzky Oct 31 '17 at 19:02
  • also, see if you can execute this (after putting in the correct names) : exec(' use dbname; select COLUMNPROPERTYEX(OBJECT_ID(''dbo.table''),''fldname'',''collation'') ') at [LINK-SERVER]; – Mister Magoo Nov 1 '17 at 0:35
  • Another test might be : exec(' select * from schema.dbo.table tbl where tbl.fldname = ''tagName.TagValue''') at [LINK-SERVER] – Mister Magoo Nov 1 '17 at 0:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.