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…
Just go to the Monitoring Reports and pick ‘Response Group Usage Report’:
And… come on !! really?
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:
(3) Open the LcsCDR database and manually execute that stored procedure dbo.CdrRGSUsageTrend. Gotcha!
(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)
(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’
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
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 😉