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.