When attempting to utilize Lync 2013 Monitoring Reports, we were presented with an error which began with:
An error has occurred because the time zone of SQL Server Reporting Services (SSRS) is inconsistent with the time zone of the…
We run our SQL servers in GMT because of our International presence, but our Lync servers are running in EST. We recently rebuild our Lync environment and hadn’t verified the monitoring reports on the new installation because they were so rarely used.
The error message provides the proper steps for resolution. These steps needed to be run on both the LCSCDR and QoEMetrics databases, and the above error message will reference either database as they both need to be fixed.
1. Run the dbo.RTCClearTimeZoneInfo stored procedure on the [LcsCDR/QoeMetrics] database
2. Open the reports home page in your web browser using the SSRS that has the time zone that you want to use
3. Run the dbo.RtcGenerateSummaryTables stored procdure on [LcsCDR/QoeMetrics] database to improve your reporting experience.
However, after running the procedure in step 1, we were presented with the following error:
Msg 547, Level 16, State 0, Procedure RtcClearTimeZoneInfo, Line 6
The DELETE statement conflicted with the REFERENCE constraint “FK_DaylightSavingYears_1”. The conflict occurred in database “QoEMetrics”, table “dbo.DaylightSavingYears”, column ‘TimeZone’.
The statement has been terminated.
(1 row(s) affected)
I found this thread on technet which properly identified the problem with Lync 2013 that the end user had resolved with a Microsoft support call.
There are two issues which prevent the successful execution of the stored procedure in Lync 2013.
- There is now a foreign key constraint between the DaylightSavingYears table and the TimeZones table which prevents the TimeZones table from being cleared first.
- There is now a trigger associated with the TimeZoneConfiguration table which requires there always be exactly one row in the table.
Resolution
To work around this issue the following SQL query can be run to perform the same action as the stored procedure. It will execute the operation on both the LcsCDR and QoEMetrics tables.
USE [LcsCDR]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @Status int
set @Status = 0
BEGIN TRANSACTION
DELETE from DaylightSavingYears WITH (TABLOCKX)
if (@@error <> 0) begin
ROLLBACK TRANSACTION
end
DELETE from TimeZones WITH (TABLOCKX)
if (@@error <> 0) begin
ROLLBACK TRANSACTION
end
ALTER TABLE TimeZoneConfiguration DISABLE TRIGGER "TimeZoneConfigurationTrigger";
DELETE from TimeZoneConfiguration WITH (TABLOCKX)
if (@@error <> 0) begin
ROLLBACK TRANSACTION
end
ALTER TABLE TimeZoneConfiguration ENABLE TRIGGER "TimeZoneConfigurationTrigger";
COMMIT TRANSACTION
exec @Status = RtcTruncateSummaryTables
SELECT @Status
USE [QoEMetrics]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @Status int
set @Status = 0
BEGIN TRANSACTION
DELETE from DaylightSavingYears WITH (TABLOCKX)
if (@@error <> 0) begin
ROLLBACK TRANSACTION
end
DELETE from TimeZones WITH (TABLOCKX)
if (@@error <> 0) begin
ROLLBACK TRANSACTION
end
ALTER TABLE TimeZoneConfiguration DISABLE TRIGGER "TimeZoneConfigurationTrigger";
DELETE from TimeZoneConfiguration WITH (TABLOCKX)
if (@@error <> 0) begin
ROLLBACK TRANSACTION
end
ALTER TABLE TimeZoneConfiguration ENABLE TRIGGER "TimeZoneConfigurationTrigger";
COMMIT TRANSACTION
exec @Status = RtcTruncateSummaryTables
SELECT @Status
The nice thing about the script is that it combines the procs from both the LcsCDR and QoEMetrics in a single procedure. After running the script, I still needed to run generate the summary tables with dbo.RtcGenerateSummaryTables in each of the databases.
