One Uppercase letter+one misconfiguration=4 hours quest

Just a normal a day with a SfB on-premises (yes, there are still some installed in the world) after migrating the RGS from another domain to this one and you decide to look around on the Monitoring services if everything is OK…

ISSUE

Just go to the Monitoring Reports and pick ‘Response Group Usage Report’:
sqlcollation
And… come on !! really?
sqlcollation2

Let summarize my last 4 hours:
(1) Go to the the SSRS, <program files>\Microsoft SQL Server\LogFiles and you will find this ‘self explanatory’ error message (whaaaat?):
processing!ReportServer_0-41!f30!10/14/2019-14:54:52:: w WARN: Data source ‘CDRDB’: Report processing has been aborted.
processing!ReportServer_0-41!f30!10/14/2019-14:54:52:: e ERROR: Throwing … —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘MainDS’. —> System.Data.SqlClient.SqlException: Invalid column name ‘TCTIme’.

(2) Start a SQL Server profiler session for the LcsCDR database, repeat the ‘query’ and you will get the call to a storage procedure:
sqlcollation3

(3) Open the LcsCDR database and manually execute that stored procedure dbo.CdrRGSUsageTrend. Gotcha!
sqlcollation4

(4) edit (Modify) the store procedure and you will find one line where the temporary column ‘TCTime’ with the ‘I’ in uppercase (the only one on the entire SQL statement)
sqlcollation5

CAUSE

(1) The uppercase ‘I’ is a long-term MS bug.
If you go to the <program files>\Common Files\Skype for Business Server 2015\DBSetup and open the ‘CdrDb.sql’ you will find it defined like that.

(2) Check the LcsCDR properties, and you might find that the collation is not Case Insensitive (CI) which means that ‘i’ <> ‘I’
sqlcollation6

WORKAROUND

Modify the ‘I’ to lower case and save the store procedure.
This will solve the problem… until you update SfB databases, because the CdrDB.sql will replace the store procedures with the uppercase ‘I’… unless MS fix this on the next CU

THE SOLUTION

Change the DB SQL collation to Case Insensitive (CI), like the default ‘Latin1_General_CP1_CI_AS’

You might now say that you really need an ‘I’ (or two) to troubleshoot this one đŸ˜‰

2 thoughts on “One Uppercase letter+one misconfiguration=4 hours quest

  1. Branko Sabadi 22/10/2019 / 10:18

    Strange. Never ran into this issue. I did had problem with index of same query.

    • LuisR 29/02/2020 / 07:55

      might not happen in some scenarios.
      But i managed easily to reproduce on a Lab, just by changing the SQL collation to case sensitive.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.