This is a migrated thread and some comments may be shown as answers.

SQL Reporting: Display All Recurring Appointments

41 Answers 655 Views
Scheduler
This is a migrated thread and some comments may be shown as answers.
Josh
Top achievements
Rank 1
Josh asked on 02 Jul 2008, 08:54 PM
Does anyone know how to display a list of appointments (recurring specifically) in an SQL Reporting Services report?

Thanks!

41 Answers, 1 is accepted

Sort by
0
T. Tsonev
Telerik team
answered on 03 Jul 2008, 12:01 PM
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
0
Josh
Top achievements
Rank 1
answered on 03 Jul 2008, 12:53 PM
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!
0
T. Tsonev
Telerik team
answered on 08 Jul 2008, 02:11 PM
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
0
Jothi Ramashandran
Top achievements
Rank 1
answered on 09 Jul 2008, 05:33 PM
I am looking to accomplish the same thing Josh is. Would be interested in your solution as well.
0
Accepted
T. Tsonev
Telerik team
answered on 14 Jul 2008, 02:24 PM
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:

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
0
Josh
Top achievements
Rank 1
answered on 14 Jul 2008, 05:13 PM
Tsvetomir,

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

Thanks,
Josh
0
Josh
Top achievements
Rank 1
answered on 14 Jul 2008, 09:18 PM
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

0
Accepted
T. Tsonev
Telerik team
answered on 15 Jul 2008, 12:54 PM
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
0
ATS
Top achievements
Rank 1
answered on 24 Jul 2008, 09:47 PM
Can the setup script be modified for SQL 2000 databases? If so, how do I go about doing that?
0
T. Tsonev
Telerik team
answered on 25 Jul 2008, 01:19 PM
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.
0
GrZeCh
Top achievements
Rank 2
answered on 08 Oct 2008, 10:06 AM
please delete this post.
0
Keith
Top achievements
Rank 1
answered on 03 Feb 2011, 10:15 PM
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.
0
Fred
Top achievements
Rank 1
answered on 18 May 2011, 03:59 PM
Is this user defined function officially supported? Does it get patched if and when bugs get found in the RecurrenceEngine?

Thanks,

Fred
0
Peter
Telerik team
answered on 20 May 2011, 03:28 PM
Hi Fred,

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

Best wishes,
Peter
the Telerik team

Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

0
Todd
Top achievements
Rank 1
answered on 01 Jun 2011, 05:00 PM
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. 
0
Peter
Telerik team
answered on 02 Jun 2011, 11:27 AM
Hi Todd,

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

Greetings,
Peter
the Telerik team

Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

1
Todd
Top achievements
Rank 1
answered on 02 Jun 2011, 04:04 PM
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 [EndFROM    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?  
0
Neil
Top achievements
Rank 1
answered on 02 Sep 2011, 02:59 PM
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?
0
Neil
Top achievements
Rank 1
answered on 06 Sep 2011, 03:26 PM
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

0
Denis Buchwald
Top achievements
Rank 1
answered on 05 Oct 2015, 09:01 PM

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

0
Plamen
Telerik team
answered on 07 Oct 2015, 06:11 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Denis Buchwald
Top achievements
Rank 1
answered on 07 Oct 2015, 01:37 PM

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?

0
Plamen
Telerik team
answered on 08 Oct 2015, 11:42 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Brian Mott
Top achievements
Rank 1
answered on 06 Nov 2015, 08:10 PM

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. 

0
Josh
Top achievements
Rank 1
answered on 09 Nov 2015, 01:23 PM

[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

0
Plamen
Telerik team
answered on 10 Nov 2015, 06:09 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
RTA DEV
Top achievements
Rank 1
answered on 22 Dec 2015, 04:50 AM

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!

0
Plamen
Telerik team
answered on 24 Dec 2015, 02:19 PM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Michael
Top achievements
Rank 1
answered on 17 Jan 2016, 07:49 AM
Any idea if this will work with Sql Server v12 on Azure?
0
Plamen
Telerik team
answered on 20 Jan 2016, 09:04 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Denis Buchwald
Top achievements
Rank 1
answered on 22 Feb 2016, 03:56 PM

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,

0
Plamen
Telerik team
answered on 25 Feb 2016, 11:30 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Denis Buchwald
Top achievements
Rank 1
answered on 25 Feb 2016, 03:21 PM

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.

0
Plamen
Telerik team
answered on 26 Feb 2016, 06:25 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Denis Buchwald
Top achievements
Rank 1
answered on 26 Feb 2016, 09:07 PM

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!

0
Plamen
Telerik team
answered on 29 Feb 2016, 06:23 AM
Hi,

I am attaching the 4.0 version.

Regards,
Plamen
Telerik
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Denis Buchwald
Top achievements
Rank 1
answered on 29 Feb 2016, 04:22 PM

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,

0
Plamen
Telerik team
answered on 03 Mar 2016, 07:30 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Chinedu Opara
Top achievements
Rank 1
answered on 12 Sep 2016, 03:25 PM

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.

0
Plamen
Telerik team
answered on 15 Sep 2016, 07:34 AM
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
Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
0
RTA DEV
Top achievements
Rank 1
answered on 28 Oct 2019, 01:31 AM

Hi,

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

Thank you.

Tags
Scheduler
Asked by
Josh
Top achievements
Rank 1
Answers by
T. Tsonev
Telerik team
Josh
Top achievements
Rank 1
Jothi Ramashandran
Top achievements
Rank 1
ATS
Top achievements
Rank 1
GrZeCh
Top achievements
Rank 2
Keith
Top achievements
Rank 1
Fred
Top achievements
Rank 1
Peter
Telerik team
Todd
Top achievements
Rank 1
Neil
Top achievements
Rank 1
Denis Buchwald
Top achievements
Rank 1
Plamen
Telerik team
Brian Mott
Top achievements
Rank 1
RTA DEV
Top achievements
Rank 1
Michael
Top achievements
Rank 1
Chinedu Opara
Top achievements
Rank 1
Share this question
or