SQL Reporting: Display All Recurring Appointments

[Josh]

Does anyone know how to display a list of appointments (recurring specifically) in an SQL Reporting Services report?

Thanks!

[T. Tsonev]

Hi Josh,

The recurrence rule is stored as a string and it has to be expanded into a series by using the RecurrenceRule class. One option is to use a managed stored procedure/function to expand the recurrence series on the SQL Server itself. We can assist you with the code related to the expanding of the recurrence series.

A second option might be to implement a Data Processing Extension for SSRS. I am afraid that we cannot assist you much with the implementation of the extension itself, but this solution seems to be more flexible.

Best wishes,
Tsvetomir Tsonev
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Josh]

Thank you for the response.  I'd be interested in creating a function / procedure on the SQL Server to expand the stored string.  Could you pass along the code to implement this?

Thanks for you help!

[T. Tsonev]

Hi Josh,

I will try to post an example soon. Thank you for your patience.

Sincerely yours,
Tsvetomir Tsonev
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Jothi Ramashandran]

I am looking to accomplish the same thing Josh is. Would be interested in your solution as well.

[T. Tsonev]

Hi,

We've just finished a draft implementation of a managed User Defined Function that can be used to expand recurring events on the SQL server itself.

The UDF uses the RecurrenceRule class to expand the recurring series. But as it turns out, we can't just put the whole Telerik.Web.UI assembly in the SQL server, as it has lots of external dependencies. That is why we've extracted the recurrence engine into a separate assembly - Telerik.Web.UI.RecurrenceEngine.dll (included). This effectively means that you don't have a dependacy on Telerik.Web.UI in the UDF.

In order to deploy the UDF, you need to open the Setup.sql script and fill-in the database name and the path to both UDF and recurrence engine assembly. Note that this will enable CLR integration on the server (global setting).

Sample usage (full code in Sample.sql):

SELECT      a.ID, a.Subject, o.StartDate, o.EndDate
FROM        Appointments a
CROSS APPLY dbo.ExpandRecurrence(a.RecurrenceRule, @@RangeStart, @@RangeEnd) AS o
WHERE       a.ID = 15

The full source code of the function is included in the attached file.

Additional resources:

• Table Valued Function magic in SQL Server 2005 using the new SQL/CLR integration
• SQLCLR Streaming Table Valued Functions (TVFs)
• Returning Complex Data from User-Defined Functions with CROSS APPLY

Feedback is welcome. We will also publish the code as a KB soon.

All the best,
Tsvetomir Tsonev
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Josh]

Tsvetomir,

Thank you very much for your assistance.  The feature is exactly what I was looking for!

Thanks,
Josh

[Josh]

Tsvetomir,

I've been testing the UDF a little more and I think there may be an issue where the script doesn't pull an event if it falls within the specified date range and it isn't a recurring event.

Was that intended?

My thought was that it would pull all events (recurring or not) within a given date range.

Thanks,
Josh

[T. Tsonev]

Hi Josh,

The sample query only lists occurrences in a given range. If you want to select the non-recurring appointments too, then you need to use OUTER APPLY. Quoted from the MSDN documentation:

There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

We also have to add filtering for the range in the SELECT query itself to ensure we are only getting appointments in range:

SELECT  a.ID,
        a.Subject,
        ISNULL(o.StartDate, a.Start) as Start,
        ISNULL(o.EndDate, a.[End]) as [End]
FROM    Appointments a
        OUTER APPLY dbo.ExpandRecurrence(a.RecurrenceRule, @@RangeStart, @@RangeEnd) AS o
WHERE   -- Include non-recurring appointments in the range
        (a.RecurrenceRule IS NULL AND a.Start < @@RangeEnd AND a.[End] > @@RangeStart) OR
        -- And recurring appointments in range.
        (a.RecurrenceRule IS NOT NULL AND o.StartDate < @@RangeEnd AND o.EndDate > @@RangeStart)

I agree that this is a lot more useful as a sample query and I will use it instead when publishing the KB. Thank you for bringing this question.

Greetings,
Tsvetomir Tsonev
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[ATS]

Can the setup script be modified for SQL 2000 databases? If so, how do I go about doing that?

[T. Tsonev]

Hello Justin,

Unfortunately, SQL Server 2000 does not support managed SPs and UDFs. It supports extended stored procedures that can be used in conjunction with COM interop to route the data to the recurrence engine assembly.

Furthermore, SQL 2005 does not support CROSS or OUTER APPLY, so this will result in more complicated queries.

All the best,
Tsvetomir Tsonev
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[GrZeCh]

please delete this post.

[Keith]

Has there been any issue with running this CLR under SQL 2008 R2, 64 bit?

I'm having some app domain unloading issues that did not exist under SQL 2005, and some things are pointing to this assembly.

[Fred]

Is this user defined function officially supported? Does it get patched if and when bugs get found in the RecurrenceEngine?

Thanks,

Fred

[Peter]

Hi Fred,

What errors do you get? Can you send us the stack trace?

Best wishes,
Peter
the Telerik team

[Todd]

I've had great luck with this so far but have run into one issue. Is there a way to specify the maximum recurrences? I think it defaults to 10,000. 

[Peter]

Hi Todd,

Yes, you can specify the MaximumCandidates for the recurrence rule.

Greetings,
Peter
the Telerik team

[Todd]

From within a Stored Procedure? For example:

SELECT  a.ID,         a.Subject,         ISNULL(o.StartDate, a.Start) as Start,         ISNULL(o.EndDate, a.[End]) as [End] FROM    Appointments a         OUTER APPLY dbo.ExpandRecurrence(a.RecurrenceRule, @@RangeStart, @@RangeEnd) AS o WHERE   -- Include non-recurring appointments in the range         (a.RecurrenceRule IS NULL AND a.Start < @@RangeEnd AND a.[End] > @@RangeStart) OR         -- And recurring appointments in range.         (a.RecurrenceRule IS NOT NULL AND o.StartDate < @@RangeEnd AND o.EndDate > @@RangeStart) 

How would you specify to return more than 10000 recurrences using the ExpandRecurrences_udf?  

[Neil]

There seems to be a problem with the recurrence engine in the supplied DLL, it will not return occurrences for some of the more unusual recurrence rules. Our users often have rules that may relate to an event that happens every 18 or 24 months, so the rule may look something like this:
DTSTART:20090901T000000Z
DTEND:20000102T000000Z
RRULE:FREQ=MONTHLY;INTERVAL=24;BYMONTHDAY=1

I created an additional function to return just the next occurrence date after the date specified as follows:

public DateTime GetNextDue(string recurrenceRule, DateTime sDate)
        {
            DateTime returnVal = new DateTime(2000, 1, 1);
            RecurrenceRule rrule;
            if (RecurrenceRule.TryParse(recurrenceRule, out rrule))
            {
                foreach (DateTime occStart in rrule.Occurrences)
                {
                    if (occStart > sDate)
                    {
                        returnVal = occStart;
                        break;
                    }

                }
            }
            return returnVal;
        }

When I run the above rule in my web application with an sDate of 9/1/2011...the returned next occurrence date is 9/1/2013 which is the expected result, however when I run the function in SQL as follows:

Select dbo.GetNextDue('DTSTART:20090901T000000Z
DTEND:20000102T000000Z
RRULE:FREQ=MONTHLY;INTERVAL=24;BYMONTHDAY=1
', '9/1/2011') as NextDue

The result returned is 1/1/2000 (the default return value), which is obviously not correct.

As far as I can see, the only difference here is the version of the recurrence engine. The one created for the CLR function was created in 2008 and the one in my web app is the latest Telerik.Web.UI.dll.

Unfortunately one cannot import the whole Telerik.Web.UI.dll without it importing a whole bunch of .NET dll's also, so is it possible to get and updated version of the Telerik.Web.UI.RecurrenceEngine.dll that was built for this project?

[Neil]

For anybody else that needed an updated recurrence engine dll. The most awesome people at Telerik hav updated the dll in the KB article:

http://blogs.telerik.com/aspnet-ajax/posts/08-08-04/working-with-radscheduler-recurring-appointments-on-sql-server.aspx

[Denis Buchwald]

I've installed this new recurrenceEngine dll on my server and no matter what I do, it still returns 0 rows. Here is my query below. I have one record that has RecurrenceRule:

MeetingID: 41983, Start: 2015-07-09 09:30:00.000, End: 2015-07-09 10:30:00.000, Title: Weekly Team Meeting, Description: NULL , RoomID: NULL IsAllDay:0, RecurrenceRule:FREQ=WEEKLY;BYDAY=TH, RecurrenceId: NULL, RecurrenceException: NULL, StartTimeZone: NULL, EndTimeZone: NULL

 

I'm running this query and it returns me 0 rows:

DECLARE @@RangeStart as DATETIME
SET @@RangeStart = CAST('2010-07-01' AS DATETIME)

DECLARE @@RangeEnd as DATETIME
SET @@RangeEnd = CAST('2022-12-12' AS DATETIME)

SELECT a.MeetingID,
a.Title,
ISNULL(o.StartDate, a.Start) as Start,
ISNULL(o.EndDate, a.[End]) as [End]
FROM Meetings a
OUTER APPLY dbo.ExpandRecurrence(a.RecurrenceRule, @@RangeStart, @@RangeEnd) AS o
WHERE
(a.RecurrenceRule IS NOT NULL AND o.StartDate < @@RangeEnd AND o.EndDate > @@RangeStart)
ORDER BY MeetingID​

 

Can someone help me get this running. I have urgent report to finish and this RecurrenceRule parser is really holding me up.

 

Thanks

[Plamen]

Hello,

In such scenario the recurrence rule is not correct- you have to have interval property set so that the correct output is shown. RadScheduler is also using the DTSTART and DTEND fields.

Here is the sample rule that worked correctly at my side:
DTSTART:20150709T123000Z\nDTEND:20150709T130000Z\nRRULE:FREQ=WEEKLY;INTERVAL=1;BYDAY=TH

Hope this will help you solve the issue.

Regards,
Plamen
Telerik

[Denis Buchwald]

Plamen:

 

The record is set by Telerik MVC Scheduler. I can't change it the way it works. Scheduler itself is not setting the Interval.

Can someone at Telerik update RecurrenceEngine.dll to parse out recurrenceRule entries set by Telerik?

[Plamen]

Hello,

This blog post  and the thread are about the Ajax RadScheduler. it is expected that it is not working for MVC Scheduler.


Regards,
Plamen
Telerik

[Brian Mott]

Is this still valid in the current RadScheduler? All I need is a way to pull all the recurring apts from SQL using the Telerik logic to guarantee that my recurring apts match those in the scheduler calendar view.

If need be, I can do it from the control but I would prefer to offload this on SQL. 

[Josh]

[quote]Brian Mott said:

Is this still valid in the current RadScheduler? All I need is a way to pull all the recurring apts from SQL using the Telerik logic to guarantee that my recurring apts match those in the scheduler calendar view.

If need be, I can do it from the control but I would prefer to offload this on SQL. 

[/quote]

 

Brian,

 

I just setup it up this past week and it worked for me.  I believe they posted an updated sample to the blog within this thread but technically the code they are using for parsing the recurrence should work, unless the iCalendar standards change.

 
Thanks,

Josh

[Plamen]

Hello,

The blog post is still valid and if  you observe some issues with it please share the case with us so we could inspect and fix them.

Regards,
Plamen
Telerik

[RTA DEV]

Hi,

I find that there is a discrepancy in the .NET assembly of parsing the recurrence rule string compared to the ExpandRecurrence UDF function in SQL Server.

 For example, running the following query return a single row:

SELECT *
FROM ExpandRecurrence('DTSTART:20151217T191500Z
DTEND:20151217T194500Z
RRULE:FREQ=HOURLY;UNTIL=20151217T191500Z;INTERVAL=12
', '2015-12-01', '2015-12-20')

 

However, if I use the same rrule string in .NET code, it actually return a zero length list:

Dim rruleString = "DTSTART:20151217T191500Z
DTEND:20151217T194500Z
RRULE:FREQ=HOURLY;UNTIL=20151217T191500Z;INTERVAL=12"
Dim rrule = RecurrenceRule.TryParse(rruleString)
Dim ruleList = rrule.Occurrences.ToList

 

Is this a bug? If it is, could this be fixed?

 

Thanks!

[Plamen]

Hello,

Thank you for your feedback. We are going to validate the project and update it as soon as we could. Meanwhile you could also check the zip in the Code Library  and contains extracted source code and check it against the latest source code in the controls. 

Regards,
Plamen
Telerik

[Michael]

Any idea if this will work with Sql Server v12 on Azure?

[Plamen]

Hi,

The solution should work correctly as long as the CLR integration is possible in the environment. Yet I would rather recommend you to refer to some more specific Azure related forum regarding this possibility in your case.

Regards,
Plamen
Telerik

[Denis Buchwald]

Hello Plamen,

 

I have also found what I believe is a bug in the SQL UDF. When I try to parse daily events that have an interval value they return an event for everyday instead of using the interval value.

 

For example:

SELECT * FROM ExpandRecurrence('DTSTART:20151021T080000Z' + Char(10) + 'DTEND:20151021T090000Z' + Char(10) + 'RRULE:FREQ=DAILY;INTERVAL=2', '12/01/2015', '12/10/2015')

 

This will return an event for every day from Dec 1 to Dec 9. Is this a bug and how can I resolve it?

 

Thank you,

[Plamen]

Hello,

The Telerik.Web.UI.RecurrenceEngine.dll that is used in the Code Library is using quite an old version of our controls so you may try to build it from the source code with a newer version in case the issue is already fixed. If you still reproduce it with some recurrence rule in RadScheduler with the latest version of the controls  please let us know so we could inspect the issue and be more helpful.

Regards,
Plamen
Telerik

[Denis Buchwald]

Hello,

Is it possible you could post an updated copy of the DLL? I'm just working on it from the SQL side so I could swap out the DLL used in the function.

[Plamen]

Hi,

I have created a newer version of the Telerik.Web.UI.RecurrenceEngine.dll and am attaching it here. 

Please let me know if you still observe the issue.

Regards,
Plamen
Telerik

[Denis Buchwald]

Thank you! Unfortunately I'm unable to bring that into my SQL server installation. It is giving an error that the dll is not valid. I think it might have been compiled using .net 4.5 where SQL 2012 CLR typically stops at 4.0.

Sorry to be a pain but would it be possible to recompile the DLL for me for SQL 2012/.net 4.0?

Thank you again!

[Plamen]

Hi,

I am attaching the 4.0 version.

Regards,
Plamen
Telerik

[Denis Buchwald]

Good day!

Again thank you for your prompt service and assistance! I hate to be a pain but I'm still getting an error and I believe it's on the recurrence engine DLL side. I get the following:

 

A .NET Framework error occurred during execution of user-defined routine or aggregate "ExpandRecurrence":
System.TypeLoadException: Inheritance security rules violated while overriding member: 'Telerik.Web.UI.RecurrenceRule.GetObjectData(System.Runtime.Serialization.SerializationInfo, System.Runtime.Serialization.StreamingContext)'. Security accessibility of the overriding method must match the security accessibility of the method being overriden.
System.TypeLoadException:
   at UserDefinedFunctions.ExpandRecurrence(String recurrenceRule, SqlDateTime rangeStart, SqlDateTime rangeEnd)
.

I believe this is related to the GetObjectData override in the dll not having correct security permissions? A quick google search offered some quick solutions but I'm no expert.

 

Any suggestions?

Thank you,

[Plamen]

Hi,

Please excuse me for this inconvenience. I have inspected the issue once again and have attached here the update dll.

As for the issue it seems to be caused by the fact that when using such recurrence rule with daily frequency and interval 2 the rule that is generated and used by RadScheduler is a bit different:
 

SELECT * FROM ExpandRecurrence('DTSTART:20151021T080000Z' + Char(10) + 'DTEND:20151021T090000Z' + Char(10) + 'RRULE:FREQ=DAILY;INTERVAL=2;BYDAY=MO,TU,WE,TH,FR,SA,SU', '12/01/2015', '12/10/2015')

Hope this will explain the issue. 

Regards,
Plamen
Telerik

[Chinedu Opara]

Hi,

My client is working on all SQL Server 2008 R2, which as you know is locked to .Net Framework 2.0. Would it be possible to post a version of this ExpandRecurrence DLL compiled under Framework 2.0? Please? It would be a massive lifesaver for us. Thank you so much.

[Plamen]

Hello,

I am attaching a .net 2 dll that worked correctly at my side. Unfortunately it does not support timezones because they are depending on some .NET 3.5 only dlls.

Hope this will be helpful.

Regards,
Plamen
Telerik by Progress

[RTA DEV]

Hi,

May I know how do we use the CLR assembly in SQL 2017 without disabling "CLR strict security"?

Thank you.