Lesson Learned #444:Handling the “Row Value Expressions Exceeds Maximum Allowed” Error in SQL Server

by | Oct 15, 2023 | Technology

This article is contributed. See the original author and article here.

Some days ago, we faced the following error message: “Msg 10738, Level 15, State 1, Line 2 The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.” our customer is using ODBC Driver 18 for SQL Server and they got the previous message. With this complete error:  Error: (‘42000’, ‘[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values. (10738) (SQLExecDirectW)’).


 


Understanding the Error:


 


The error message you’re encountering, often referred to as “Row Value Expressions Exceeds Maximum Allowed,” occurs when you attempt to insert more than 1000 rows using a single INSERT statement. SQL Server has a built-in limitation that restricts the number of rows you can insert in one go to prevent performance and stability issues on the server.


 


Why Does It Happen?


This limitation exists to safeguard the server from processing excessively large insertions that could impact its performance negatively. By restricting the number of rows per INSERT statement, SQL Server can maintain a balance between data consistency and system resources.


 


Example of the script:


 

INSERT INTO MiTabla (ID, Edad) values
(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50),(1,50)

 


Solutions:


Now that we’ve established why this error occurs, let’s explore some potential solutions to address it:


 


Batched Insertions:


One straightforward approach to overcome this limitation is to divide your data into smaller batches and perform multiple INSERT statements. For example, if you need to insert 2000 rows, split them into two batches of 1000 rows each and execute two separate INSERT statements.

-- First batch
INSERT INTO MiTabla (ID, , Age)
VALUES
    -- 1000 rows here

-- Second batch
INSERT INTO MiTabla (ID, Age)
VALUES
    -- 1000 rows here

 


Use Temporary Tables or Staging Tables:


Another approach is to use temporary tables or staging tables to hold your data temporarily. You can insert your data into these tables in smaller chunks and then transfer the data to the target table using a series of INSERT INTO … SELECT statements.


 

-- Create a staging table
CREATE TABLE StagingTable (
    ID INT,
    Age INT
)

-- Insert data into the staging table in batches

-- Then transfer the data to the target table
INSERT INTO MiTabla (ID, Age)
SELECT ID, Age FROM StagingTable

 


Conclusion:


The “Row Value Expressions Exceeds Maximum Allowed” error in SQL Server serves as a safeguard to prevent excessive insertions in a single INSERT statement. By understanding why this error occurs and employing batched insertions or other strategies, you can efficiently manage and insert large datasets into your SQL Server database without encountering this limitation. You could find more information here: Table Value Constructor (Transact-SQL) – SQL Server | Microsoft Learn


 


 


 


 

Kusto NLog connector now supports Azure Data Explorer Free Clusters

Kusto NLog connector now supports Azure Data Explorer Free Clusters

by | Oct 14, 2023 | Technology

This article is contributed. See the original author and article here.

Screenshot 2023-10-14 192356.png


Introduction


NLog is a popular logging framework used by developers to log messages from .NET applications. NLog, a robust logging framework, captures and stores critical information, providing essential insights for debugging and optimization.


 


Earlier in the summer we released the open-source NLog connector for Azure Data Explorer. Azure Data Explorer (ADX) is a fast and highly scalable data exploration service that can be used to store and analyze large volumes of data. 


 


In this blog post, we walk you through the latest enhancements in NLog connector for Azure Data Explorer which marks a significant leap forward in offering seamless integration with Azure Data Explorer for an unparalleled application behavior insights and monitoring.


 


In the latest connector update, a host of powerful features has been introduced. This includes seamless support for Free Azure Data Explorer Clusters and integration with KQL Database in Microsoft Fabric Real-Time Analytics. Now, developers can dive into the world of ADX without the need for an Azure account subscription or credit card. Simply create a free ADX account, and you’re all set to harness the full potential of your logs using an ADX cluster.


 


Leveraging the expressive power of Kusto Query Language (KQL), developers can uncover invaluable insights into their application’s behavior, performance metrics, error occurrences, and even detect anomalies. This newfound capability opens a world of possibilities for fine-tuning and optimizing applications based on data-driven decisions.


 


Moreover, bid farewell to the IngestionEndpoint property and embrace the power of Kusto Connection Strings. These strings serve as your all-access pass to a range of authentication modes, including the convenient User Prompt Authentication and the secure User Token Authentication.


 


Diving into the ADX Target Sample Application with latest updates


You can find the detailed initial setup steps in the previous blog post here: Getting started with NLog and Azure Data Explorer – Microsoft Community Hub


 


This will be like the previous blog but in context of Free Cluster and Connection string support.


 


Pre-requisites:



Steps


Create a table in Azure Data Explorer to store logs. The following command can be used to create a table with the name “ADXNLogSample”.


 

.create table ADXNLogSample (Timestamp:datetime, Level:string, Message:string, FormattedMessage:dynamic, Exception:string, Properties:dynamic)

 


Clone the NLog-ADX target git repo.


 

git clone https://github.com/Azure/azure-kusto-nlog-sink.git

 


Set the following environment variables in the sample application:



  • CONNECTION_STRING : Kusto ConnectionString of ADX cluster created.

  • Eg: Data Source=https://ingest-..kusto.windows.net;Fed=True

  • DATABASE: The name of the database to which data should be ingested into.


Simply build and run the application:



  • Install the ADX Target for NLog


The ADX Target for NLog is available as a NuGet package. To install it, open the Package Manager Console and enter the following command:


 

dotnet add package NLog.Azure.Kusto --version 2.0.1

 


Build the application and run it



  • Open a Powershell window, navigate to NLog ADX Target base folder and run the following command.   

    dotnet build


  • Once build got completed, navigate to src/Nlog.Azure.Kusto.Samples/ run the following command to run the sample application.

    dotnet run


  • This is making the sample application, open a login prompt in your default browser, where you need to enter your username and password.


asaharn_0-1697093574870.jpeg


 



  • The ingested log data can be verified by querying the created log table (ADXNLogSample in our case) by using the following KQL command.

    ADXNLogSample | take 10​



asaharn_1-1697093574880.png


Conclusion


The NLog Azure Data Explorer Target connector is a great tool for log management that allows developers to send their log messages to ADX for analysis and visualization. With the new features of the connector such as support for ADX free cluster and Microsoft Fabric cluster, developers can now use ADX without an Azure account or credit card. By using KQL queries, developers can gain insights into their application behavior, performance, errors, and anomalies. We hope this article has helped you understand how to use the new features of the connector and how to make sense of your logs using an ADX cluster.


 


Documentation: Ingest data with the NLog sink into Azure Data Explorer – Azure Data Explorer | Microsoft Learn


Open-Source Repository: Azure/azure-kusto-nlog-sink: Nlog custom target for storing logs to ADX (github.com)


NLog Kusto Connector Nuget: NuGet Gallery | NLog.Azure.Kusto 2.0.1


Cumulative Update #23 for SQL Server 2019 RTM

by | Oct 13, 2023 | Technology

This article is contributed. See the original author and article here.

The 23rd cumulative update release for SQL Server 2019 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.
To learn more about the release or servicing model, please visit:



Starting with SQL Server 2017, we adopted a new modern servicing model. Please refer to our blog for more details on Modern Servicing Model for SQL Server


How Azure is keeping customers secure against the Rapid Reset DDoS Vulnerability

by | Oct 12, 2023 | Technology

This article is contributed. See the original author and article here.

Bad actors can expose a new security vulnerability to initiate a DDoS attack on a customer’s infrastructure. This attack is leveraged against servers implementing the HTTP/2 protocol. Windows, .NET Kestrel, and HTTP .Sys (IIS) web servers are also impacted by the attack. Azure Guest Patching Service keeps customers secure by ensuring the latest security and critical updates are applied using Safe Deployment Practices on their VM and VM Scale Sets.  


 


As the latest security fixes are released from Windows and other Linux distributions, Azure will apply them for customers opted into to either Auto OS Image Upgrades or Auto Guest Patching. By opting into the auto update mechanisms through Azure, customers can remain proactive against security issues rather than reacting to attackers. Customers not leveraging the auto update capabilities through Azure Guest Patching Service are recommended to update their fleet with the latest security updates (KB5031364 for Windows and fix for CVE-2023-44487 related to Open-Source Software distributions).  


 


Without the latest security updates, organizations risk exposing their systems and data to potential security threats and web attacks. It is important for organizations to plan for this update to avoid any disruption to their business operations.  


 


Microsoft recommends enabling Azure Web Application Firewall (WAF) on Azure Front Door or Azure Application Gateway to further improve security posture. WAF rate limiting rules are effective in providing additional protection against these attacks. See additional recommendations from Microsoft Security Response Center for this vulnerability.  


 
Enabling Auto Update Features: Azure recommends the following features to ensure VM and VM Scale Sets are secured with the latest security and critical updates in a safe manner: 


 


Auto OS Image Upgrades: Azure replaces the OS disk with the latest OS Image. Supports rollback and rolls the upgrade across scale sets throughout all the regions. 


Auto Guest Patching: Azure applies the latest security and critical updates to an asset and rolls the update across the fleet throughout all the regions.  


 


The recent announcement of a new security issue is an important reminder for organizations to stay current with their software solutions to avoid any security or performance issues. Azure continues to keep customers secure by rolling out the latest security updates through multiple mechanisms for VM and VM Scale Sets in a safe manner. Customers are recommended to leverage the auto update capabilities in Azure to ensure they remain proactive against bad actors.  

Introducing multiple recurrence support for the work hour calendar in Universal Resource Scheduling (URS)

Introducing multiple recurrence support for the work hour calendar in Universal Resource Scheduling (URS)

by | Oct 11, 2023 | Dynamics 365, Microsoft 365, Technology

This article is contributed. See the original author and article here.

The work hour calendar multiple recurrence feature is a new URS functionality that allows you to create and manage work hour calendars with more flexibility and efficiency. You can now define multiple recurrence patterns for your work hour calendar events, such as daily, weekly or monthly, and specify different start and end dates for each pattern. This way, you can easily accommodate different work schedules, holidays, and special events in your organization.

The new multiple recurrence feature in the upcoming V2 work hour calendar can help you to:

  • Add multiple recurrences within a single day, to represent different instances of recurring shift work e.g. morning, afternoon and evening shifts in a single day with different recurrences
  • Have overlapping recurrences within a week e.g. A recurrence for Mon and Wed, and a recurrence for Tues. Previously the Tuesday recurrence would have deleted the Mon and Wed entries; now they can coexist alongside each other.
  • Input work hour events in different timezones, which is helpful for workers who travel. Previously, the calendar supports only one timezone across all work hour calendar events.

What are work hour calendar events and why are they needed?

Work hour events define when a resource is available to perform work, and they exist as 2 types:

  • Occurrences (one-time events) are work hour events that happen only once on a specific date and time. Occurrences always take priority over Recurrences. E.g. team cohesion days, seminars or emergencies.
  • Recurrences (repeating events) are work hour events that repeat on a regular basis according to a pattern and frequency. E.g. rotational shift work, weekly cadences, monthly client visits

Occurrences and recurrences can be used today in URS to define different types of work hours, such as working hours, non-working hours, breaks and time off.

How did URS handle work hour events before (V1 work hour calendar)?

Before this update, only 1 recurrence event is supported per calendar day, for a given date span.

Scenario 1, Jane is a doctor who does shift work at various clinics:

  • Recurrence 1 (morning shifts): 7am-12pm UTC, repeats Mon, Tues, Wed
  • Recurrence 2 (afternoon shifts): 1pm-5pm UTC, repeats Tues, Wed, Thu
  • Recurrence 3 (night shifts): 7pm-11pm UTC, repeats Wed, Thu and Fri

The old work hour calendar does not support more than 1 work hour event per calendar day, so this scenario would not be supported

Scenario 2, John is a utilities engineer with different work hours on alternating days:

  • Recurrence 1: 8am-5pm UTC, repeats Mon, Wed and Fri
  • Recurrence 2: 6am-8pm UTC, repeats Tues and Thu

Adding both recurrences was not supported in the old work hour calendar; Recurrence 2 would have deleted the Mon, Wed and Fri entries from Recurrence 1 for a given date span.

Scenario 3, Becca is a travelling salesperson who works in both Seattle and Singapore:

  • Recurrence 1 (work in Seattle): 8am-5pm PT, repeats all days of the week
  • Recurrence 2 (work in Singapore): 8am-5pm SGT, repeats all days of the week

Adding both recurrences of different timezones was not supported in the old work hour calendar.

How does URS handle work hour events now (V2 work hour calendar)?

The new V2 work hours calendar now follows the following logic:

  • Occurrences have a higher priority than Recurrence rules for a given calendar day. So if there were two rules (one occurrence and one recurrence) on the same day, the daily occurrence or time-off occurrence will take the priority over the weekly recurrence for the entire calendar day.(Unchanged from previous)
  • When there are multiple recurrences within the same date span:
    • If the times do not intersect, they will both remain on the calendar
    • If the times conflict, the rule that was most recently created/modified will be the one that is considered for the resource’s calendar. All other conflicting rules in the date span will be removed. In the event that some recurrences have conflicts on some dates but not on others, the rule will be spliced to retain the non-conflicting events, while removing the events on dates that do have conflicts.

Scenario 1, Jane is a doctor who does shift work at various clinics:

  • Recurrence 1 (morning shifts): 7am-12pm UTC, repeats Mon, Tues, Wed
  • Recurrence 2 (afternoon shifts): 1pm-5pm UTC, repeats Tues, Wed, Thu
  • Recurrence 3 (night shifts): 7pm-11pm UTC, repeats Wed, Thu and Fri

Create Recurrence 1, Recurrence 2, then Recurrence 3 in succession. All will now show up on the calendar as seen below

Scenario 2, John is a utilities engineer with different work hours on alternating days:

  • Recurrence 1: 8am-5pm UTC, repeats Mon, Wed and Fri
  • Recurrence 2: 6am-8pm UTC, repeats Tues and Thu

Create Recurrence 1, then create Recurrence 2 in succession. Both will now show up on the calendar as seen below:

Scenario 3, Becca is a travelling salesperson who works in both Seattle and Singapore:

  • Recurrence 1 (work in Seattle): 8am-5pm PT, repeats all days of the week
  • Recurrence 2 (work in Singapore): 8am-5pm SGT, repeats all days of the week

As seen above both Seattle and Singapore work hours are both easily expressed on the V2 work hours calendar. Note that the Singapore work hours are shifted to match the dispatcher’s Timezone i.e. Pacific Time – the timezone of the calendar itself is visible at the bottom left of the calendar, and the dispatcher can be altered this in <Personalization Settings>.

What else has changed/remains unchanged?

Previously in the V1 Work Hour Calendar, only 1 recurrence is allowed per calendar day, thus adding any new recurrences will completely override the work hour events for that calendar day.

With the V2 Work Hour Calendar, the previous work hour events will only be overridden if there is a direct conflict in time between the 2 recurrences.

For instance, Joel is an equipment technician with the following work hours:

  • Recurrence 1 (regular work hours): 9am-5pm PT, repeats all days of the week
  • Recurrence 2 (temporary work hours): 1-9pm PT, only from Jul 10-14

Create Recurrence 1, then create Recurrence 2 in succession. As seen below, Recurrence 2 overrides Recurrence 1 for the Jul 10-14 period because there is a direct conflict between the recurrences. All other work hour events remain.

The following dialogue will now appear whenever a new work hour event is added, in order to remind users of this behavior:

Occurrences remain unchanged from the previous V1 calendar i.e. Occurrences always take priority over Recurrences and will override recurrences for the entire day.

For instance, Duke is an equipment technician with the following work hours:

  • Recurrence 1 (regular work hours): 9am-5pm PT, repeats all days of the week
  • Occurrence 1 (team cohesion): 6-9pm PT, only on Aug 1

Create Recurrence 1, then create Occurrence 1 in succession. As seen below, Occurrence 1 completely overrides all other work hours events for the Aug 1 calendar day even if there is no direct collision between the Recurrence and the Occurrence.

When will the V2 Work Hour Calendar be available, and how can I get my hands on it?

The V2 Work Hour Calendar will be available early-September 2023 in our Early Adoption Wave 2 update. You can opt in through Power Platform Admin Center, as seen below:

How can I find out more?

If you want to learn more about the new work hour calendar multiple recurrence feature, you can:

Read the documentation here: Edit work hour calendars by using APIs in Dynamics 365 Field Service – Dynamics 365 Field Service | Microsoft Learn

Join the community forum here: https://community.dynamics.com/

Contact the support team here: https://support.microsoft.com/en-us/contactus/

We hope you enjoy the new work hour calendar multiple recurrence feature and find it useful for your business needs. We appreciate your feedback and suggestions on how to improve our products and services. Thank you for choosing Dynamics 365!

The post Introducing multiple recurrence support for the work hour calendar in Universal Resource Scheduling (URS) appeared first on Microsoft Dynamics 365 Blog.

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.