none
SSRS time zone is off on lync reports

    Question

  • On one of my east coast servers I changed it to the east coast time zone (it was on pacific since it was put in service) and now I get the red text on the reports page letting me know: An error happened because the time zone of SQL Server Reporting Services (SSRS) is inconsistent with the time zone of the call detail recording (CDR) database (time zone of SSRS : Eastern Standard Time, CDR database : Pacific Standard Time).

     

    I have tried to right-click on and execute the stored procedure that supposedly fixes this for both the cdr and qoe DBs but when I do so it returns this:

     

    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)

     

     The problem is not resolved...

    Saturday, July 6, 2013 9:11 PM

Answers

  • Hi,pesospesos,

    I believe this is SQL reporting service issue,after searching on the internet I found the following link,would you please check if it helps you.

    http://stackoverflow.com/questions/2969689/working-with-time-zones-in-ssrs

    Regards,

    Sharon


    ××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××× Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.

    • Marked as answer by Kent-Huang Friday, August 2, 2013 6:01 AM
    Monday, July 8, 2013 8:54 AM
    Moderator

All replies

  • I tried deleting the Key, was fun recreating so don't recommend. I also tried modifying it to not enforce key constraint and was able to run the stored procedure but still had the error when viewing reports.


    Sunday, July 7, 2013 3:29 PM
  • Hi Michael, I get the same error for both DBs.  What adverse effects could there be from trying the above approach?

    thanks,

    Wes

    Sunday, July 7, 2013 5:48 PM
  • Hi,pesospesos,

    I believe this is SQL reporting service issue,after searching on the internet I found the following link,would you please check if it helps you.

    http://stackoverflow.com/questions/2969689/working-with-time-zones-in-ssrs

    Regards,

    Sharon


    ××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××× Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.

    • Marked as answer by Kent-Huang Friday, August 2, 2013 6:01 AM
    Monday, July 8, 2013 8:54 AM
    Moderator
  • got error about DaylightSavingYears , when used LYNC

    resolved with MICROSOFT TICKET with this workround :

    There are two issues which prevent the successful execution of the stored procedure in Lync 2013.

    1. There is now a foreign key constraint between the DaylightSavingYears table and the TimeZones table which prevents the TimeZones table from being cleared first.
    2. 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


    בני תמותה - מאה ממאה מספר שעד כה לא השתנה

    • Proposed as answer by DanInAus Wednesday, May 28, 2014 1:25 AM
    Sunday, September 15, 2013 7:58 AM
  • Thanks , I tried problem fixed
    Wednesday, June 18, 2014 8:44 AM