Tag Archives: microsoft
New Teams App Browser version – Channel Tab – entire Website link converted to lower case
New Teams App Browser Version:
Classic Teams App – Web Version or Teams Desktop/Native:
I have recently encountered an issue with the Teams website link feature in the NEW Teams app in Browser Version. When opening the website link using “open in browser”, it converts the entire URL to lowercase and causing the website link to fail rendering.However, the same tab link functions correctly when opened from the “Classic Teams app -web version” or “Teams desktop/native”. Example: New Teams App Browser Version:Sample website link opens in a new tab when clicked on “Open in browser”:http://localhost:7101/report.jsp?pp=%252f%2540report%252fusers%252fadmin%252ffifa18_v1 Classic Teams App – Web Version or Teams Desktop/Native:Sample website link opens in a new tab when clicked on “Open in browser”:http://localhost:7101/Report.jsp?pp=%252F%2540Report%252FUsers%252FAdmin%252FFIFA18_V1 Read More
Conditional Access Policy – Only allow EntraID Joined devices to access SharePoint Online
Hi
I have a cloud-only Microsoft 365 Tenant, 40 devices all EntraID joined and I want to only allow users to access SharePoint Online from the EntraID devices and not for example from their home computers.
Is this achievable through Conditional Access policies? I see an option for hybrid joined but not EntraID joined
HiI have a cloud-only Microsoft 365 Tenant, 40 devices all EntraID joined and I want to only allow users to access SharePoint Online from the EntraID devices and not for example from their home computers. Is this achievable through Conditional Access policies? I see an option for hybrid joined but not EntraID joined Read More
How to list meetings created by a specific user in Exchange Online?
Hello everyone, everything good?
Hello everyone, everything good? Well, although I read and insisted on reading the existing documentation, I didn’t find a way to list the meetings created by a specific user in Exchange Online. I can even list everything in a calendar BUT not what was created by a specific user in a given period. Has anyone already done something in this regard? Read More
Does Microsoft buy apps through Marketplace?
Here’s one for the community: Can – and, more importantly, does – Microsoft buy third-party solutions through Marketplace? Has anyone here sold software to Microsoft through a Marketplace transaction?
#Sponsored #MarketplaceChampions
Here’s one for the community: Can – and, more importantly, does – Microsoft buy third-party solutions through Marketplace? Has anyone here sold software to Microsoft through a Marketplace transaction? #Sponsored #MarketplaceChampions Read More
On Demand Capacity Reservation is now available in Azure for US Government
Today, we’re announcing the general availability of on demand capacity reservations for Azure Virtual Machines in Azure for US Government Cloud . You can now manage and reserve capacity with guaranteed SLA for VM sizes available on Azure for US Government Cloud.
As part of our ongoing commitment to expanding our global reach and providing On Demand Capacity Reservation benefits to our customers, we have diligently worked hard for months to bring this feature to Azure for US Government Cloud.
On-demand capacity reservations can be leveraged in the following scenarios and beyond for the cloud:
Business-critical applications — use on-demand capacity reservations to protect capacity, for example when taking these VMs offline to perform updates.
Disaster recovery (DR) — set aside compute capacity to ensure a seamless recovery in the event of a natural disaster. The compute capacity can be repurposed to run other workloads whenever DR is not in effect.
Special events—claiming capacity ahead of time provides assurance that your business can handle the extra demand.
The feature is available to be used on Azure for US Government Cloud through Portal as well as clients like Powershell, CLI and REST API.
On demand capacity reservations come with a capacity SLA. Unused reserved capacity and Virtual Machines using reserved capacity are both eligible for Azure Reserved Virtual Machine Instance term discounts.
Existing and Future capabilities
The basic capability to use On Demand Capacity Reservation feature is Generally Available in Azure for US Government. Integration with Azure Site Recovery with On-Demand Capacity Reservation in Fair Fax cloud will come at a later release. Future target is to expand the footprint of the feature to other clouds like Azure in China and beyond.
The VM deployment methods supported with on demand capacity reservations are single VMs and Virtual Machine Scale Sets using uniform orchestration mode. Virtual Machine Scale Sets flexible orchestration mode is in public preview. Please read the documentation to learn more.
Resources to get started
There are two demonstration videos currently available:
Basic feature operation and use with single VM deployments.
Detailed feature operation and integration with Virtual Machine Scale Sets.
Additionally, you can read the on demand capacity reservation documentation that includes sample code.
Microsoft Tech Community – Latest Blogs –Read More
Curated resiliency recommendations for Azure Virtual Desktop
The Azure Proactive Resiliency Library (APRL) is a collection of best practices, recommendations, and scripts that help you improve the resiliency of your Azure Virtual Desktop environment. Incorporating the contributions of multiple subject matter experts around the globe, including Microsoft FastTrack and senior cloud solution architects, the library offers proven guidance based on thousands of hours helping organizations deploy Azure Virtual Desktop in complex environments.
IT professionals face many challenges, such as ensuring the availability, scalability, and performance of virtual machines, networks, and storage during deployment. We know that you need to guarantee your Azure Virtual Desktop environment can cope with high demand, unforeseen failures, and disaster recovery situations. You also need to track and improve Azure Virtual Desktop resource utilization to prevent overspending without negatively affecting the user experience.
We want you to have the best Azure Virtual Desktop experience possible. That’s why we have put together this collection of resources. Our goal is to help you overcome (or avoid) common challenges and boost your Azure Virtual Desktop resiliency. With the APRL, you get a collection of:
Best practices and guidelines for creating, operating, and managing your Azure Virtual Desktop environment.
Scripts and tools that help you implement the best practices and follow the recommendations.
Dashboards and reports that help you monitor and enhance your Azure Virtual Desktop resiliency metrics and trends.
You also benefit from recommendations on Azure resources utilized by Azure Virtual Desktop, improving the reliability of the Well- Architected Framework, and configuring, monitoring, and managing workloads.
What are the benefits of using the Azure Proactive Resiliency Library?
By utilizing the APRL, you can:
Ensure that your Azure Virtual Desktop environment is available and reliable by following the best practices for fault tolerance, redundancy, backup, and recovery.
Make your Azure Virtual Desktop environment more scalable and performant by following the best practices for load balancing, autoscaling, and optimization.
Benefit from best practices for encryption, authentication, and auditing, to help ensure that your Azure Virtual Desktop environment is better secure and compliant.
Find guidance on resource management and billing, and feedback to help optimize your environment for efficiency.
How do I use the Azure Proactive Resiliency Library?
The APRL is designed to be easy to use and integrate with your existing Azure Virtual Desktop environment. You can use APRL in three simple steps:
Download and install the APRL tooling.
Run the APRL assessment tool to scan your Azure Virtual Desktop environment and generate a report with your current resiliency score and recommendations.
Apply the recommendations by using the APRL scripts and tools, or by following the guidelines and best practices.
You can also use the APRL dashboards and reports to track and visualize your Azure Virtual Desktop resiliency metrics and trends over time. You can customize the dashboards and reports to suit your needs and preferences.
Get started with the Azure Proactive Resiliency Library today
The APRL is a must-have tool for any IT professional looking to boost Azure Virtual Desktop resiliency and achieve the best possible outcomes for their business and users. By using the library, you can improve Azure Virtual Desktop availability, scalability, performance, security, compliance, cost efficiency, and user satisfaction. APRL can also save you time and effort by automating and simplifying the implementation of the best practices and recommendations.
We would love to hear from you and help you optimize your Azure Virtual Desktop resilience so please leave us a comment below. Ready to get started with APRL? Visit the APRL website to learn more.
Special thanks to the team for driving the creation of this set of tools: Swathi Bhat, Prasad Gandham, Tom Hickling, Claus Emrich ,Yonah Shafner, Moises Gomez Cortez, Jesse Coyne, Marc Wolfson, Jonathan Core, and Dany Contreras.
Note: This is a free tool you can make use of in your environment; however, Microsoft does not provide support for this tool.
Microsoft Tech Community – Latest Blogs –Read More
Filtering only certain columns of a row to another worksheet
I am using Filter to move a copy of the data from one worksheet to another, but want to remove certain columns. Filter does not seem to allow that kind of manipulation. Is this even possible.
This is the FILTER statement I am using in the other worksheet: =FILTER(‘PI Package’!$A$3:$FA1600,(‘PI Package’!$A$3:$A$1600=”BUNDLE IDENTIFIER”),”NO RECORDS”)
which results in this output
I am using Filter to move a copy of the data from one worksheet to another, but want to remove certain columns. Filter does not seem to allow that kind of manipulation. Is this even possible.This is the FILTER statement I am using in the other worksheet: =FILTER(‘PI Package’!$A$3:$FA1600,(‘PI Package’!$A$3:$A$1600=”BUNDLE IDENTIFIER”),”NO RECORDS”) which results in this output Read More
Onedrive continously signing out
Hi, I wondered if anyone can help….
Onedrive used to sync files from MS Teams Sharepoint like a dream until about 10 days ago.
Now the desktop client seems to drop connection constantly as though the network is droppoing or firewall is blocking things…. Before I started there, I thought I would ask if anyone else has had this recently….
Nothing I do makes OneDrive stay signed in….
I have already ensured my Dell 7310 is fully up to date on Bios and software.
Signed out of MS Teams, O365, Onedrive
Cleared cache from C:UsersusernameAppDataLocalOneDrive
Full shutdown, restart, login everywhere and OneDrive signs in sees 15 changes and signs out…..
I have even unlinked the laptop, done the above again, relinked the laptop, it syncs and then signs out…..
Has anyone got any ideas??
Hi, I wondered if anyone can help….Onedrive used to sync files from MS Teams Sharepoint like a dream until about 10 days ago. Now the desktop client seems to drop connection constantly as though the network is droppoing or firewall is blocking things…. Before I started there, I thought I would ask if anyone else has had this recently…. Nothing I do makes OneDrive stay signed in….I have already ensured my Dell 7310 is fully up to date on Bios and software.Signed out of MS Teams, O365, OnedriveCleared cache from C:UsersusernameAppDataLocalOneDriveFull shutdown, restart, login everywhere and OneDrive signs in sees 15 changes and signs out…..I have even unlinked the laptop, done the above again, relinked the laptop, it syncs and then signs out….. Has anyone got any ideas?? Read More
Handling Entity Data in Sentinel
So, I have set up some playbooks that allow me to add IPs/Domains/File Hashes to the MDE Indicators list, which is awesome to have and saves time when we need to block malicious entities. However, I have not found a great way for Sentinel to give me more information regarding File Hashes.
Really, my main worry with just a list of hashes in an incident is not knowing the file name for each hash, like so:
So, in this case, I am to just assume that both file hashes go to the ‘FileCoAuth’ file. Easy enough. But, are there ever cases where something like msedge.exe shows up in this list of file hashes? Right now, I feel like in this ‘Info’ tab, it might be more helpful to have ‘File Name’, but I might be looking at this all wrong.
I guess, I am just looking for some guidance into this entity so that I don’t accidentally block the wrong file and end up breaking systems.
Even if these hashes only ever correspond to the one file entity in the incident, I am still a bit confused at how little data comes over into this. Even for the File entity:
Great, I know the name of the file and the path.. However, over in Defender, I get TONS of info for the file, including all the hashes connected to it, First seen / last seen, basic VirusTotal info, and a bunch of other items. Am I expecting too much by hoping that we wouldn’t have to jump over to Defender? We set up Sentinel with the hopes of making it the go-to, but still find ourselves going right back to Defender for investigations and I wasn’t sure if there was something that I am missing in this setup, or if there was a way to get more data enrichment without having to pay VirusTotal’s insane bill (we are SMB and were quoted 90k per year, minimum). Even then, when Defender has some of the basic VirusTotal info, I was hoping Sentinel would have that and more..
So, I have set up some playbooks that allow me to add IPs/Domains/File Hashes to the MDE Indicators list, which is awesome to have and saves time when we need to block malicious entities. However, I have not found a great way for Sentinel to give me more information regarding File Hashes. Really, my main worry with just a list of hashes in an incident is not knowing the file name for each hash, like so:So, in this case, I am to just assume that both file hashes go to the ‘FileCoAuth’ file. Easy enough. But, are there ever cases where something like msedge.exe shows up in this list of file hashes? Right now, I feel like in this ‘Info’ tab, it might be more helpful to have ‘File Name’, but I might be looking at this all wrong.I guess, I am just looking for some guidance into this entity so that I don’t accidentally block the wrong file and end up breaking systems. Even if these hashes only ever correspond to the one file entity in the incident, I am still a bit confused at how little data comes over into this. Even for the File entity:Great, I know the name of the file and the path.. However, over in Defender, I get TONS of info for the file, including all the hashes connected to it, First seen / last seen, basic VirusTotal info, and a bunch of other items. Am I expecting too much by hoping that we wouldn’t have to jump over to Defender? We set up Sentinel with the hopes of making it the go-to, but still find ourselves going right back to Defender for investigations and I wasn’t sure if there was something that I am missing in this setup, or if there was a way to get more data enrichment without having to pay VirusTotal’s insane bill (we are SMB and were quoted 90k per year, minimum). Even then, when Defender has some of the basic VirusTotal info, I was hoping Sentinel would have that and more.. Read More
Outlook rule for selective forwarding of email received via distribution list
After trying rules based on different things, searching the internet, etc I can not get a rule to work for this scenario:
Inbound email received from a government entity, sent to our company’s distribution list. I am one of several in the distribution list and have no problem receiving the emails. What I would like to do is forward these emails to only a few people who are not part of the distro (and do not need to receive all email to the distro). The from email is like email address removed for privacy reasons. (keyword is the same before and after “@”). The email subjects are varied, so unable to use subject.
Everything I’ve tried (thus far) doesn’t cause the email to auto forward. I’ve tried the email contains keyword and sent to distro name. I’ve tried from email contains keyword and to email contains keyword. I’ve tried sent to user a or b or c (members of distro). I’m not sure what I’m missing to get it working. I have similar rules to move emails sent to another distro I’m part of to a specific folder, and that seems to work fine.
I am wondering if there is some larger setting at play with IT and our parent company. Not sure what I can check on as a regular user that might get this working.
Thanks in advance!
jt
After trying rules based on different things, searching the internet, etc I can not get a rule to work for this scenario: Inbound email received from a government entity, sent to our company’s distribution list. I am one of several in the distribution list and have no problem receiving the emails. What I would like to do is forward these emails to only a few people who are not part of the distro (and do not need to receive all email to the distro). The from email is like email address removed for privacy reasons. (keyword is the same before and after “@”). The email subjects are varied, so unable to use subject. Everything I’ve tried (thus far) doesn’t cause the email to auto forward. I’ve tried the email contains keyword and sent to distro name. I’ve tried from email contains keyword and to email contains keyword. I’ve tried sent to user a or b or c (members of distro). I’m not sure what I’m missing to get it working. I have similar rules to move emails sent to another distro I’m part of to a specific folder, and that seems to work fine. I am wondering if there is some larger setting at play with IT and our parent company. Not sure what I can check on as a regular user that might get this working. Thanks in advance!jt Read More
Excel 2021. Double sided printing throws left and right margins off. No gutter setting.
Hello, I upgraded to Office 2021 Plus a few months ago from Office 2019. Ever since, when printing double sided documents, the front page is moved to the left with very little margin on the left and too much on the right. The flip side is exactly opposite with very little margin on the right and too much on the left. These pages are printed in landscape and flipped on long edges. Now they were set up and printed perfect in Office 2019, and only shifted when trying to print them through Office 2021 Plus.
I have searched the internet and have found many solutions to others having this issue by adjusting the “gutter” which as far as I can tell is supposed to be under the Page Setup/Margins tab. Unfortunately, my margins tab is not showing a gutter setting, nor is any tab under page setup. If there is someone that can help me with this issue, I would be very grateful.
Hello, I upgraded to Office 2021 Plus a few months ago from Office 2019. Ever since, when printing double sided documents, the front page is moved to the left with very little margin on the left and too much on the right. The flip side is exactly opposite with very little margin on the right and too much on the left. These pages are printed in landscape and flipped on long edges. Now they were set up and printed perfect in Office 2019, and only shifted when trying to print them through Office 2021 Plus.I have searched the internet and have found many solutions to others having this issue by adjusting the “gutter” which as far as I can tell is supposed to be under the Page Setup/Margins tab. Unfortunately, my margins tab is not showing a gutter setting, nor is any tab under page setup. If there is someone that can help me with this issue, I would be very grateful. Read More
Sharepoint tenant out of space
Hello,
Our Sharepoint tenant is out of space, see below:
But everything works at the moment, we can edit, upload files to Sharepoint sites. Why does it do it? If we are still able to do it, is it because its using the space from the recycle bin? Or deleted the version history by its own?
Best
Hello, Our Sharepoint tenant is out of space, see below:But everything works at the moment, we can edit, upload files to Sharepoint sites. Why does it do it? If we are still able to do it, is it because its using the space from the recycle bin? Or deleted the version history by its own? Best Read More
NIS2 Trainings May 2- 9, 2024
Dear Microsoft Partners,
The Network and Information Systems Directive 2 (NIS2) represents the European Union’s latest stride in bolstering cybersecurity across Member States, coming into effect in October 2024. As digital threats evolve, NIS2 represents a unique opportunity to help customers improve their cybersecurity posture now.
Our mission is to equip you with the knowledge and tools necessary for helping your customers not just meet NIS2 requirements but exceed them. We look forward to your participation.
Microsoft and our training partner Fast Lane partnered up for an exclusive webinar series designed to demystify the NIS2 Directive for you as our Partner and how you can use it as an opportunity to help customers improve their cybersecurity health.
The webinar series will run in English, German, French, Spanish and Italian between May 2nd and May 9th.
Agenda
Each 2-hour webinar will offer insight into:
The NIS2 Directive, with essential information across its legal interpretation on local country level
How the Microsoft platform can support customers meet NIS2 expectations
Guidance on how you as a Microsoft security partner can build a sales offer or solution adressing NIS2
At the end of each webinar, you will receive a Microsoft-commissioned playbook how to build your sales offer with NIS2 and evolve your practice with Microsoft Security.
Who should attend this event?
The webinars are suitable for any Microsoft partner, both sales and technical professionals with Microsoft Security background (any level)
Register today
Microsoft Tech Community – Latest Blogs –Read More
Tech Talks Presents: Power Pages Data Controls & External Data Connectivity | May 2nd
Join us on Thursday, May 2nd at 8am PT as Pranita Padalwar, Sr Product Manager presents Power Pages Data Controls & External Data Connectivity.
We hope you’ll join us!
Call to Action:
Click on the link to save the calendar invite: https://aka.ms/TechTalksInvite
View past recordings (sign in required): https://aka.ms/TechTalksRecording
Get started with the adoption tools here
Join us on Thursday, May 2nd at 8am PT as Pranita Padalwar, Sr Product Manager presents Power Pages Data Controls & External Data Connectivity.
We hope you’ll join us!
Call to Action:
Click on the link to save the calendar invite: https://aka.ms/TechTalksInvite
View past recordings (sign in required): https://aka.ms/TechTalksRecording
Get started with the adoption tools here Read More
Several Issues with Microsoft Bookings – Need Assistance
Hello everyone,
I’m new to Microsoft Bookings and encountering a few issues I hope the community can help me resolve.
1. Error on Booking Page:
No matter which access setting I choose (Available to anyone, No self-service, Available to people in your org), I keep receiving an error message on the booking page: “We aren’t offering services through the booking page right now. Please contact us directly or check back here later.” I’ve attached an image of the error below.
2. Viewing Staff Calendars on a Weekly/Monthly Basis:
Currently, I can sort the calendar by “day by staff,” which works well. However, I’m looking to view staff availability on a weekly or monthly basis. Is there a feature that allows this, or is it not possible within Bookings?
3. Address Field Not Showing Full Details in Outlook:
When creating an appointment, there’s a field for a Google address that is searchable and appears complete on Bookings. But, when I check the appointment notes in Outlook, it only shows the street address, not the full details. I will upload an image for clarity.
4. Rescheduling Appointments in Outlook:
We schedule appointments for our sales team, and these appointments sync with their Outlook calendars, which is fantastic. However, they are unable to drag and reschedule these appointments themselves. Must they access Bookings to change their schedules, or is there another way?
I appreciate any guidance or solutions you might offer!
Thank you!
Hello everyone,I’m new to Microsoft Bookings and encountering a few issues I hope the community can help me resolve.1. Error on Booking Page:No matter which access setting I choose (Available to anyone, No self-service, Available to people in your org), I keep receiving an error message on the booking page: “We aren’t offering services through the booking page right now. Please contact us directly or check back here later.” I’ve attached an image of the error below.2. Viewing Staff Calendars on a Weekly/Monthly Basis:Currently, I can sort the calendar by “day by staff,” which works well. However, I’m looking to view staff availability on a weekly or monthly basis. Is there a feature that allows this, or is it not possible within Bookings?3. Address Field Not Showing Full Details in Outlook:When creating an appointment, there’s a field for a Google address that is searchable and appears complete on Bookings. But, when I check the appointment notes in Outlook, it only shows the street address, not the full details. I will upload an image for clarity.4. Rescheduling Appointments in Outlook:We schedule appointments for our sales team, and these appointments sync with their Outlook calendars, which is fantastic. However, they are unable to drag and reschedule these appointments themselves. Must they access Bookings to change their schedules, or is there another way?I appreciate any guidance or solutions you might offer!Thank you! Read More
Data is Null. The method or property cannot be call on null values. Exception
In my application multiple users request the API and in the API I am calling the stored Procedure via Entity Framework but some times getting exception Data Is Null. The Method or Property cannot be call on null values. This is not consistent if 4-5 users are clicking on submit button to call api then api executing stored procedure then for some users exception coming.
This is my main procedure
ALTER PROCEDURE [dbo].[sp_SaveDcumentAndParties](
@Action VARCHAR(20),
@partiesHistoryJson NVARCHAR(MAX)=NULL,
@exportExtraHistoryJson NVARCHAR(MAX)=NULL,
@FieldSettingsJson NVARCHAR(MAX)=NULL,
@CodingSessionDetails NVARCHAR(MAX)=NULL,
@DocumentID VARCHAR(50) = NULL,
@ProjectId VARCHAR(50) = NULL,
@DocumentDate NVARCHAR(255) = NULL,
@DocumentType NVARCHAR(255) = NULL,
@EnteredById INT = NULL,
@Estimated NVARCHAR(255) = NULL,
@Title NVARCHAR(4000) = NULL,
@CodingQATime INT = NULL,
@IsCorrected INT = NULL,
@UserTask VARCHAR(10) = NULL,
@isHistoryDocument Bit,
@ReturnJSONResult NVARCHAR(MAX) =NULL
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @Description NVARCHAR(MAX);
IF @Action = ‘Save’
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
BEGIN TRY
— Deadlock avoidance mechanism
SET DEADLOCK_PRIORITY LOW;
DECLARE @IsAddPartiesAndPartiesHistory BIT;
DECLARE @IsAddCodedData BIT;
DECLARE @IsUpdateImportPages BIT;
DECLARE @IsUpdateAdminRegeTitle BIT;
DECLARE @IsUpdateExportExtras BIT;
DECLARE @IsInsUpdFieldAdminValidations BIT;
DECLARE @IsExecCodingSessionDetails BIT;
SAVE TRANSACTION MySavepoint; — Savepoint before inner procedure call
EXEC AddPartiesAndPartiesHistory @partiesHistoryJson,@exportExtraHistoryJson,
@DocumentID,@IsReturn=@IsAddPartiesAndPartiesHistory OUTPUT;
EXEC AddCodedData @DocumentID,@ProjectId, @DocumentDate,@DocumentType,@EnteredById,
@Estimated,@Title,@CodingQATime,@IsCorrected,@UserTask,@IsReturn=@IsAddCodedData
OUTPUT;
EXEC UpdateImportPages @DocumentID,@DocumentType,
@EnteredById,@UserTask,@IsReturn=@IsUpdateImportPages OUTPUT;
EXEC UpdateAdminRegeTitle @DocumentID,@DocumentType,
@EnteredById,@Title,@DocumentDate,@Estimated,@UserTask,@IsReturn=@IsUpdateAdminRegeTitle
OUTPUT;
If @UserTask=‘Coder’
Begin
EXEC UpdateExportExtras @DocumentID,@IsReturn=@IsUpdateExportExtras OUTPUT;
EXEC InsUpdFieldAdminValidations
@DocumentID,@FieldSettingsJson,@IsReturn=@IsInsUpdFieldAdminValidations OUTPUT;
END;
DECLARE @DocOrgID INT = (SELECT Id + 1 FROM ImportPages Where
Document_ID=@DocumentID);
DECLARE @NextDocumentId NVARCHAR(100);
DECLARE @NextId INT = 0;
DECLARE @IsReturnFlag BIT = 0;
DECLARE @CodeCompletedCount INT = 0;
DECLARE @QaCompletedCount INT = 0;
DECLARE @TotalDocCount INT = 0;
If @UserTask=‘QA’
BEGIN
EXEC ExecCodingSessionDetails
@DocumentID,@CodingSessionDetails,@IsReturn=@IsExecCodingSessionDetails OUTPUT;
— Retrieve total document count
SELECT @TotalDocCount = COUNT(Id) FROM ImportPages;
SET @Description=‘Total Document – ‘+@TotalDocCount;
— Retrieve QA completed document count
SELECT @QaCompletedCount = COUNT(Id) FROM ImportPages WHERE Coded = 1 AND
Revision = 1;
SET @Description=CONCAT(@Description,‘ / Total QA Doc ompleted –
‘+@QaCompletedCount);
END
ELSE
BEGIN
— Retrieve total document count and code completed document count
SELECT @TotalDocCount = COUNT(Id), @CodeCompletedCount = SUM(CASE WHEN Coded = 1
THEN 1 ELSE 0 END)
FROM ImportPages;
SET @Description=CONCAT(@Description,‘ / Total Coded Doc ompleted –
‘+@CodeCompletedCount);
END
DECLARE @LstDocumentId NVARCHAR(255);
DECLARE @LstId INT;
— Get the document by its ID
SELECT @LstDocumentId = Document_ID,@LstId=Id
FROM ImportPages
WHERE Id = @DocOrgId;
SET @Description=CONCAT(@Description,‘ / 1-Last Doc ID – ‘+@LstDocumentId+‘, ‘+‘Last
Id – ‘+@LstId);
IF @LstDocumentId IS NULL
BEGIN
— If document does not exist, check for the last document
SELECT @IsReturnFlag =
CASE
WHEN @UserTask = ‘Coder’ AND @TotalDocCount = @CodeCompletedCount THEN 1
WHEN @UserTask = ‘QA’ AND @TotalDocCount = @QaCompletedCount THEN 1
ELSE 0
END;
SET @Description=CONCAT(@Description,‘ / Is Return Flag – ‘+CAST(@IsReturnFlag AS
nvarchar(10)));
END
ELSE
BEGIN
IF @isHistoryDocument=0
BEGIN
/*Get Next Available Document*/
DECLARE @IsAssignedSameDoc BIT;
— Check if the document is assigned
SELECT @IsAssignedSameDoc = CASE WHEN EXISTS (SELECT 1 FROM CheckDocuments
WHERE Document_ID = @DocumentId) THEN 1 ELSE 0 END;
IF @IsAssignedSameDoc = 0
BEGIN
— If not assigned, set next document ID and ID to the current document
SET @NextDocumentId = @LstDocumentId;
SET @NextId = @LstId;
SET @Description=CONCAT(@Description,‘ / IsAssignedSameDoc – ‘+
Cast(@IsAssignedSameDoc AS NVARCHAR(10))+‘, NextDocumentId’+@NextDocumentId+‘,
NextId’+@NextId);
END
ELSE
BEGIN
— If assigned, find the next available document
DECLARE @AvailableDocumentId NVARCHAR(100);
DECLARE @AvailableId INT;
— Get the list of documents assigned to the same task
WITH AssignedDocs AS (
SELECT Document_ID
FROM CheckDocuments
WHERE UserTask = @UserTask
)
SELECT TOP 1 @AvailableDocumentId = Document_ID,@AvailableId=Id
FROM ImportPages
WHERE Document_ID NOT IN (SELECT Document_ID FROM AssignedDocs)
ORDER BY Document_ID;
IF @AvailableDocumentId IS NOT NULL
BEGIN
— If available document found, set its ID as next document ID
SET @NextDocumentId = @AvailableDocumentId;
SET @NextId = @AvailableId;
SET @Description=CONCAT(@Description,‘ / @AvailableDocumentId –
‘+ @AvailableDocumentId+‘, NextDocumentId’+@NextDocumentId+‘, NextId’+@NextId);
END
ELSE
BEGIN
— If not assigned, set next document ID and ID to the current
document
SET @NextDocumentId = @LstDocumentId;
SET @NextId = @LstId;
SET @Description=CONCAT(@Description,‘ / ELSE –
NextDocumentId’+@NextDocumentId+‘, NextId’+@NextId);
END
END
/*Remove Document from CheckDocument Table*/
— Check if the document exists in CheckDocuments table
IF EXISTS (
SELECT 1
FROM CheckDocuments
WHERE Document_ID = @DocumentID
AND ProjectId = @ProjectId
AND UserTask = @UserTask
AND DocumentStatus = 0
)
BEGIN
— Remove the document from CheckDocuments table
DELETE FROM CheckDocuments
WHERE Document_ID = @DocumentID
AND ProjectId = @ProjectId
AND UserTask = @UserTask
AND DocumentStatus = 0;
SET @Description=CONCAT(@Description,‘Removed Document from check
table’);
— Output informational message
PRINT ‘Removed Document from check table: ‘ + @DocumentID;
— Output informational message
PRINT ‘Check Table Data Removed: ‘ + @DocumentID;
END
END
ELSE
BEGIN
SET @NextDocumentId = @LstDocumentId;
SET @NextId = @LstId;
SET @Description=CONCAT(@Description,‘ / Outer ELSE –
NextDocumentId’+@NextDocumentId+‘, NextId’+@NextId);
END
END
PRINT ‘@nextId – ‘+ Cast(@nextId As VARCHAR(50));
SET @Description=CONCAT(@Description,‘ / Next button success’);
EXEC [dbo].[InsertSaveDocAndpartiesLogs] @DocumentID,@Description,0;
SET @ReturnJSONResult =
CASE
WHEN @isReturnFlag = 1 THEN N‘{“Response”: “LastDocument”, “Message”: “All
the documents are completed.”}’
WHEN @nextId > 0 THEN N‘{“Response”: “success”, “nextDocumentId”: “‘ +
@nextDocumentId + ‘”, “nextId”: ‘ + CAST(@nextId AS NVARCHAR(10)) + ‘, “Message”: “Document
updated successfully”}’
ELSE N‘{“Response”: “No documents to code”, “Statuscode”: 404}’
END;
— Output informational message
PRINT ‘JsonData: ‘ + @ReturnJSONResult;
SELECT @ReturnJSONResult As JsonResponse;
COMMIT TRANSACTION; PRINT ‘COMMIT’;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
IF @IsAddPartiesAndPartiesHistory = 0 OR @IsAddCodedData=0 OR
@IsUpdateImportPages=0
OR @IsUpdateAdminRegeTitle=0 OR @IsUpdateExportExtras=0 OR @
@IsInsUpdFieldAdminValidations=0
OR @IsExecCodingSessionDetails=0
ROLLBACK TRANSACTION MySavepoint; — Rollback to savepoint
ELSE
ROLLBACK TRANSACTION; — Rollback entire transaction
END
— Get error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @Description=CONCAT(@Description,‘ / ‘+@ErrorMessage);
EXEC [dbo].[InsertSaveDocAndpartiesLogs] @DocumentID,@Description,0;
— Set jsonResponse based on error
IF @ErrorMessage = ‘Sequence contains more than one element’
BEGIN
SET @ReturnJSONResult = N‘{“Response”: “Document Saving fail”, “Statuscode”:
500}’;
END
ELSE
BEGIN
IF @ErrorState = 1205
BEGIN
SET @ReturnJSONResult = N‘{“Response”: “1205 – Deadlock Detected”,
“Statuscode”: 500}’;
END
ELSE BEGIN
SET @ReturnJSONResult = N‘{“Response”: “‘ + @ErrorMessage +
‘”,”AddPartiesAndPartiesHistor”:’+CAST(@IsAddPartiesAndPartiesHistory AS NVARCHAR(10))+‘”,
“AddCodedData”:’+CAST(@IsAddCodedData AS
NVARCHAR(10))+‘”,”UpdateImportPages”:’+CAST(@IsUpdateImportPages AS NVARCHAR(10))+‘”,
“UpdateAdminRegeTitle”:’+CAST(@IsUpdateAdminRegeTitle AS
NVARCHAR(10))+‘”,”UpdateExportExtras”:’+CAST(@IsUpdateExportExtras AS NVARCHAR(10))+‘”,
“InsUpdFieldAdminValidations”:’+CAST(@IsInsUpdFieldAdminValidations AS
NVARCHAR(10))+‘”,
“ExecCodingSessionDetails”:’+CAST(@IsExecCodingSessionDetails AS
NVARCHAR(10))+‘”,”Statuscode”: 500}’; END;
END
SELECT @ReturnJSONResult As JsonResponse;
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END;
END;
Rest are nested procedures
ALTER PROCEDURE [dbo].[AddCodedData]
(
@DocumentID VARCHAR(50) = NULL,
@ProjectId VARCHAR(50) = NULL,
@DocumentDate NVARCHAR(255) = NULL,
@DocumentType NVARCHAR(255) = NULL,
@EnteredById INT = NULL,
@Estimated NVARCHAR(255) = NULL,
@Title NVARCHAR(4000) = NULL,
@CodingQATime INT = NULL,
@IsCorrected INT = NULL,
@UserTask VARCHAR(10) = NULL,
@IsReturn BIT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @Description NVARCHAR(MAX);
BEGIN TRANSACTION;
BEGIN TRY
SET @IsReturn=0;
DECLARE @TempCodedDatas AS TABLE (
[Document_ID] NVARCHAR(255), [Image_File_Name] NVARCHAR(500),
[page_label] NVARCHAR(255), [page_num] INT, [num_pages] INT,
[Coded] INT, [Revision] INT, [DocType] NVARCHAR(255),
[EnteredBy] INT, [HostDocId] NVARCHAR(255),
[ExportDate] DATETIME, [ImportDate] DATETIME, [Percentage]
INT, [SetId] INT, [DateCreated] DATETIME,
LastModified DATETIME, Host_Reference NVARCHAR(255),
[Document_Date] DATETIME, [Estimated] NVARCHAR(255),
[Document_Type] NVARCHAR(255),Title NVARCHAR(4000),
Document_DateValue NVARCHAR(255),
[CodingDate] DATETIME, [CodingTime] INT,[QADate] DATETIME,
[QATime] INT, [IsCorrected] INT
);
INSERT inTO @TempCodedDatas ([Document_ID],
[Image_File_Name], [page_label], [page_num], [num_pages], [Coded], [Revision], [DocType],
[EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId],
[DateCreated], [LastModified], [Host_Reference], [Document_Date], [Estimated],
[Document_Type],
[Title], [Document_DateValue], [CodingDate],[QADate],
[CodingTime],[QATime], [IsCorrected])
SELECT IPS.[Document_ID],IPS.[Image_File_Name],IPS.
[page_label],IPS.[page_num],IPS.[num_pages],1 AS [Coded],
(CASE WHEN @UserTask=‘Coder’ THEN 0 ELSE 1 END) AS
[Revision],@DocumentType AS [DocType],@EnteredById AS [EnteredBy],
IPS.[HostDocId],IPS.[ExportDate],IPS.[ImportDate],IPS.
[Percentage],IPS.[SetId],IPS.[DateCreated],GETDATE() AS [LastModified],
ICD.[Host_Reference],
CASE
WHEN ISNULL(@DocumentDate, ”) = ” THEN NULL
ELSE CONVERT(DATETIME, @DocumentDate)
END AS [DocDate],
@Estimated AS [Estimated],
@DocumentType AS [Document_Type],
CASE
WHEN LEN(@Title) > 0 THEN @Title
ELSE ‘Untitled’
END AS [Title],
CASE
WHEN ISNULL(ICD.Document_DateValue, ”) = ” THEN
NULL
ELSE CONVERT(DATETIME, ICD.Document_DateValue)
END AS [Document_DateValue],
(CASE WHEN @UserTask=‘Coder’ THEN GETDATE() ELSE NULL
END) AS [CodingDate],
(CASE WHEN @UserTask=‘Coder’ THEN NULL ELSE GETDATE()
END) AS [QADate],
(CASE WHEN @UserTask=‘Coder’ THEN @CodingQATime ELSE 0
END) AS [CodingTime],
(CASE WHEN @UserTask=‘Coder’ THEN 0 ELSE @CodingQATime
END) AS [CodingTime],
@IsCorrected AS [IsCorrected]
FROM
ImportPages IPS
INNER JOIN
ImportCodedDatas ICD ON ICD.Document_ID =
IPS.Document_ID
WHERE
IPS.Document_ID = @DocumentID
IF @UserTask = ‘Coder’
BEGIN
INSERT INTO CodedDatas ([Document_ID], [Image_File_Name],
[page_label], [page_num], [num_pages], [Coded], [Revision],
[DocType], [EnteredBy], [HostDocId],[ExportDate],
[ImportDate], [Percentage], [SetId], [DateCreated], [LastModified],
[main_id],[End_Page],[No_Pages],[Host_Reference],
[Document_Date], [Estimated], [Document_Type], [Title],
[Document_DateValue], [CodingDate], [QADate],
[CodingTime], [QATime], [IsCorrected],[CodingStatus],[QAStatus])
SELECT [Document_ID], [Image_File_Name], [page_label],
[page_num], [num_pages],[Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId],
[DateCreated], [LastModified],NULL,NULL,0, Host_Reference,
[Document_Date], [Estimated], [DocType],Title,
[Document_DateValue],[CodingDate], [QADate], [CodingTime], [QATime],
[IsCorrected],NULL,NULL
FROM @TempCodedDatas
SET @Description=‘Coded Data Saved’;
END
ELSE
BEGIN
INSERT INTO CodedDatas ([Document_ID], [Image_File_Name],
[page_label], [page_num], [num_pages], [Coded], [Revision],
[DocType], [EnteredBy], [HostDocId],[ExportDate],
[ImportDate], [Percentage], [SetId], [DateCreated], [LastModified],
[main_id],[End_Page],[No_Pages],[Host_Reference],
[Document_Date], [Estimated], [Document_Type], [Title],
[Document_DateValue], [CodingDate], [QADate],
[CodingTime], [QATime], [IsCorrected],[CodingStatus],[QAStatus])
SELECT [Document_ID], [Image_File_Name], [page_label],
[page_num], [num_pages],[Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId],
[DateCreated], [LastModified],NULL,NULL,0, Host_Reference,
[Document_Date], [Estimated], [DocType],Title,
[Document_DateValue],[CodingDate], [QADate], [CodingTime], [QATime],
[IsCorrected],NULL,NULL
FROM @TempCodedDatas
SET @Description=‘Review data Saved’;
END;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,1;
COMMIT TRANSACTION;
SET @IsReturn=1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
— Get error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @Description=@ErrorMessage;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,0;
SET @IsReturn=0;
END CATCH;
END
ALTER PROCEDURE [dbo].[UpdateAdminRegeTitle]
(
@DocumentID VARCHAR(50) = NULL,
@DocumentType NVARCHAR(255) = NULL,
@EnteredById INT = NULL,
@Title NVARCHAR(4000) = NULL,
@DocumentDate NVARCHAR(255) = NULL,
@Estimated NVARCHAR(255) = NULL,
@UserTask VARCHAR(10) = NULL,
@IsReturn BIT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @Description NVARCHAR(MAX);
BEGIN TRANSACTION;
BEGIN TRY
SET @IsReturn=0;
/*Update Admin Regex*/
DECLARE @AdminRegexCount INT;
DECLARE @CodedDataCount INT;
— Get the count of enabled admin regexes
SELECT @AdminRegexCount = COUNT(Id)
FROM AdminRegexs
WHERE Enabled = 1;
— Get the latest coded data for the document
SELECT @CodedDataCount = COUNT(Id)
FROM CodedDatas –WITH (UPDLOCK, SERIALIZABLE)
WHERE Document_ID = @DocumentID;
— Fetch the latest coded data
DECLARE @LatestCodedData TABLE (
Id INT,
Title NVARCHAR(MAX)
);
INSERT INTO @LatestCodedData (Id, Title)
SELECT TOP 1 Id, Title
FROM CodedDatas –WITH (UPDLOCK, SERIALIZABLE)
WHERE Document_ID = @DocumentID
ORDER BY LastModified DESC;
Declare @CodedDataTitle NVarchar(255)
Set @CodedDataTitle=(SELECT Title FROM
@LatestCodedData)
— Apply common updates conditionally
IF (@CodedDataTitle) != ‘Untitled’
BEGIN
UPDATE ImportCodedDatas
SET Document_Date = @DocumentDate,
Estimated = @Estimated,
Document_Type = @DocumentType
WHERE Document_ID = @DocumentID;
END;
DECLARE @TitleString NVARCHAR(MAX);
DECLARE @Replacement NVARCHAR(MAX);
— Apply title regex replacements
IF @AdminRegexCount > 0 AND @CodedDataCount > 0
BEGIN
— Loop through admin regexes
DECLARE @Index INT = 1;
WHILE @Index <= @AdminRegexCount
BEGIN
— Get the current admin regex and
replacement
SELECT @TitleString = Matchexpression,
@Replacement = Replacement
FROM (
SELECT Matchexpression, Replacement,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM AdminRegexs
) AS AdminRegex
WHERE RowNum = @Index;
— Update titles based on admin regex
IF @TitleString = ‘(Proprietary
Limited)+/g’
AND NOT EXISTS (SELECT 1 FROM
@LatestCodedData WHERE Title LIKE ‘%(Proprietary Limited)+/g%’)
BEGIN
SET @TitleString =
REPLACE(REPLACE(REPLACE(@TitleString, ‘(‘, ”), ‘)’, ”), ‘+/g’, ”);
END
ELSE
BEGIN
SET @TitleString =
REPLACE(REPLACE(REPLACE(REPLACE(@TitleString, ‘[‘, ”), ‘]’, ”), ‘+/g’, ”), ‘\’, ”);
SET @Title = REPLACE(CASE WHEN
@TitleString LIKE ‘%[,.;:()!?]+/g%’
THEN
REPLACE(CAST(REPLACE(@Title, @TitleString, @Replacement) AS NVARCHAR(MAX)),
‘[‘ + @TitleString +
‘]’, @Replacement) ELSE @Title END, @TitleString, @Replacement);
END
SET @Title = REPLACE(
CASE
WHEN CHARINDEX(@TitleString,
@CodedDataTitle) > 0 THEN
CASE
WHEN @CodedDataTitle
COLLATE SQL_Latin1_General_CP1_CI_AI LIKE ‘%’ + @TitleString + ‘%’ THEN
REPLACE(@CodedDataTitle, @TitleString, @Replacement)
ELSE @CodedDataTitle
END
ELSE @Title
END
, @TitleString, @Replacement);
— Update titles in ImportCodedDatas
table
UPDATE ImportCodedDatas
SET Title = @Title;
— Update titles in CodedDatas table
UPDATE CodedDatas
SET Title = @Title;
— Increment index
SET @Index = @Index + 1;
END;
End;
Else
Begin
DECLARE @NewTitle NVARCHAR(MAX);
SET @NewTitle = REPLACE(LTRIM(RTRIM(@Title)),
‘ ‘, ”); — Remove leading and trailing spaces
UPDATE ImportCodedDatas
SET Title = CASE
WHEN LEN(@NewTitle) > 0 THEN
@Title
ELSE ‘Untitled’
END
WHERE Document_ID = @DocumentID;
End;
COMMIT TRANSACTION;
SET @Description=‘Update Regex Title’;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,1;
SET @IsReturn=1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
— Get error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @Description=@ErrorMessage;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,0;
SET @IsReturn=0;
END CATCH;
END
ALTER PROCEDURE [dbo].[UpdateExportExtras]
(
@DocumentID VARCHAR(50) = NULL,
@IsReturn BIT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @Description NVARCHAR(MAX);
BEGIN TRANSACTION;
BEGIN TRY
SET @IsReturn=0;
DECLARE @MatchExpr NVARCHAR(MAX);
DECLARE @Replacement NVARCHAR(MAX);
DECLARE @UpdatedCount INT;
— Get Export_extras matching the Document_ID
SELECT
@MatchExpr = Matchexpression,
@Replacement = Replacement
FROM
AdminRegexs
WHERE
Enabled = 1;
— Update Export_extras for each AdminRegex
IF @MatchExpr IS NOT NULL AND @Replacement IS
NOT NULL
BEGIN
— Update theValue column
UPDATE Export_extras
SET theValue = REPLACE(theValue,
@MatchExpr, @Replacement)
WHERE Document_ID = @DocumentID AND
theValue IS NOT NULL;
SET @UpdatedCount = @@ROWCOUNT;
PRINT CONCAT(‘Updated ‘, @UpdatedCount, ‘
Export_extras (theValue) for AdminRegex: ‘, @MatchExpr, ‘. Document ID: ‘, @DocumentID);
— Update memoValue column
UPDATE Export_extras
SET memoValue = REPLACE(memoValue,
@MatchExpr, @Replacement)
WHERE Document_ID = @DocumentID AND
memoValue IS NOT NULL;
SET @UpdatedCount = @@ROWCOUNT;
SET @Description=‘Updated’+
@UpdatedCount+ ‘ Export_extras (memoValue) for AdminRegex: ‘+ @MatchExpr;
PRINT CONCAT(‘Updated’, @UpdatedCount, ‘
Export_extras (memoValue) for AdminRegex: ‘, @MatchExpr, ‘. Document ID: ‘, @DocumentID);
— Update textValue column
UPDATE Export_extras
SET textValue = REPLACE(textValue,
@MatchExpr, @Replacement)
WHERE Document_ID = @DocumentID AND
textValue IS NOT NULL;
SET @UpdatedCount = @@ROWCOUNT;
SET @Description=‘Updated ‘+
@UpdatedCount+ ‘ Export_extras (textValue) for AdminRegex: ‘+ @MatchExpr ;
PRINT CONCAT(‘Updated ‘, @UpdatedCount, ‘
Export_extras (textValue) for AdminRegex: ‘, @MatchExpr, ‘. Document ID: ‘, @DocumentID);
END;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,1;
COMMIT TRANSACTION;
SET @IsReturn=1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
— Get error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @Description=@ErrorMessage;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,0;
SET @IsReturn=0;
END CATCH;
END
ALTER PROCEDURE [dbo].[UpdateImportPages]
(
@DocumentID VARCHAR(50) = NULL,
@DocumentType NVARCHAR(255) = NULL,
@EnteredById INT = NULL,
@UserTask VARCHAR(10) = NULL,
@IsReturn BIT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @Description NVARCHAR(MAX);
BEGIN TRANSACTION;
BEGIN TRY
SET @IsReturn=0;
/*Updating ImportPage*/
IF @UserTask = ‘Coder’
BEGIN
UPDATE ImportPages
SET Coded = 1,
EnteredBy = @EnteredById,
DocType = @DocumentType,
LastModified = GETDATE()
WHERE Document_ID = @DocumentID;
END
ELSE
BEGIN
UPDATE ImportPages
SET Revision = 1,
EnteredBy = @EnteredById,
LastModified = GETDATE()
WHERE Document_ID = @DocumentID;
END;
SET @Description=‘Updated Import Pages’;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,1;
COMMIT TRANSACTION;
SET @IsReturn=1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
— Get error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @Description=@ErrorMessage;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,0;
SET @IsReturn=0;
END CATCH;
END
In my application multiple users request the API and in the API I am calling the stored Procedure via Entity Framework but some times getting exception Data Is Null. The Method or Property cannot be call on null values. This is not consistent if 4-5 users are clicking on submit button to call api then api executing stored procedure then for some users exception coming. This is my main procedure ALTER PROCEDURE [dbo].[sp_SaveDcumentAndParties](
@Action VARCHAR(20),
@partiesHistoryJson NVARCHAR(MAX)=NULL,
@exportExtraHistoryJson NVARCHAR(MAX)=NULL,
@FieldSettingsJson NVARCHAR(MAX)=NULL,
@CodingSessionDetails NVARCHAR(MAX)=NULL,
@DocumentID VARCHAR(50) = NULL,
@ProjectId VARCHAR(50) = NULL,
@DocumentDate NVARCHAR(255) = NULL,
@DocumentType NVARCHAR(255) = NULL,
@EnteredById INT = NULL,
@Estimated NVARCHAR(255) = NULL,
@Title NVARCHAR(4000) = NULL,
@CodingQATime INT = NULL,
@IsCorrected INT = NULL,
@UserTask VARCHAR(10) = NULL,
@isHistoryDocument Bit,
@ReturnJSONResult NVARCHAR(MAX) =NULL
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @Description NVARCHAR(MAX);
IF @Action = ‘Save’
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
BEGIN TRY
— Deadlock avoidance mechanism
SET DEADLOCK_PRIORITY LOW;
DECLARE @IsAddPartiesAndPartiesHistory BIT;
DECLARE @IsAddCodedData BIT;
DECLARE @IsUpdateImportPages BIT;
DECLARE @IsUpdateAdminRegeTitle BIT;
DECLARE @IsUpdateExportExtras BIT;
DECLARE @IsInsUpdFieldAdminValidations BIT;
DECLARE @IsExecCodingSessionDetails BIT;
SAVE TRANSACTION MySavepoint; — Savepoint before inner procedure call
EXEC AddPartiesAndPartiesHistory @partiesHistoryJson,@exportExtraHistoryJson,
@DocumentID,@IsReturn=@IsAddPartiesAndPartiesHistory OUTPUT;
EXEC AddCodedData @DocumentID,@ProjectId, @DocumentDate,@DocumentType,@EnteredById,
@Estimated,@Title,@CodingQATime,@IsCorrected,@UserTask,@IsReturn=@IsAddCodedData
OUTPUT;
EXEC UpdateImportPages @DocumentID,@DocumentType,
@EnteredById,@UserTask,@IsReturn=@IsUpdateImportPages OUTPUT;
EXEC UpdateAdminRegeTitle @DocumentID,@DocumentType,
@EnteredById,@Title,@DocumentDate,@Estimated,@UserTask,@IsReturn=@IsUpdateAdminRegeTitle
OUTPUT;
If @UserTask=’Coder’
Begin
EXEC UpdateExportExtras @DocumentID,@IsReturn=@IsUpdateExportExtras OUTPUT;
EXEC InsUpdFieldAdminValidations
@DocumentID,@FieldSettingsJson,@IsReturn=@IsInsUpdFieldAdminValidations OUTPUT;
END;
DECLARE @DocOrgID INT = (SELECT Id + 1 FROM ImportPages Where
Document_ID=@DocumentID);
DECLARE @NextDocumentId NVARCHAR(100);
DECLARE @NextId INT = 0;
DECLARE @IsReturnFlag BIT = 0;
DECLARE @CodeCompletedCount INT = 0;
DECLARE @QaCompletedCount INT = 0;
DECLARE @TotalDocCount INT = 0;
If @UserTask=’QA’
BEGIN
EXEC ExecCodingSessionDetails
@DocumentID,@CodingSessionDetails,@IsReturn=@IsExecCodingSessionDetails OUTPUT;
— Retrieve total document count
SELECT @TotalDocCount = COUNT(Id) FROM ImportPages;
SET @Description=’Total Document – ‘+@TotalDocCount;
— Retrieve QA completed document count
SELECT @QaCompletedCount = COUNT(Id) FROM ImportPages WHERE Coded = 1 AND
Revision = 1;
SET @Description=CONCAT(@Description,’ / Total QA Doc ompleted –
‘+@QaCompletedCount);
END
ELSE
BEGIN
— Retrieve total document count and code completed document count
SELECT @TotalDocCount = COUNT(Id), @CodeCompletedCount = SUM(CASE WHEN Coded = 1
THEN 1 ELSE 0 END)
FROM ImportPages;
SET @Description=CONCAT(@Description,’ / Total Coded Doc ompleted –
‘+@CodeCompletedCount);
END
DECLARE @LstDocumentId NVARCHAR(255);
DECLARE @LstId INT;
— Get the document by its ID
SELECT @LstDocumentId = Document_ID,@LstId=Id
FROM ImportPages
WHERE Id = @DocOrgId;
SET @Description=CONCAT(@Description,’ / 1-Last Doc ID – ‘+@LstDocumentId+’, ‘+’Last
Id – ‘+@LstId);
IF @LstDocumentId IS NULL
BEGIN
— If document does not exist, check for the last document
SELECT @IsReturnFlag =
CASE
WHEN @UserTask = ‘Coder’ AND @TotalDocCount = @CodeCompletedCount THEN 1
WHEN @UserTask = ‘QA’ AND @TotalDocCount = @QaCompletedCount THEN 1
ELSE 0
END;
SET @Description=CONCAT(@Description,’ / Is Return Flag – ‘+CAST(@IsReturnFlag AS
nvarchar(10)));
END
ELSE
BEGIN
IF @isHistoryDocument=0
BEGIN
/*Get Next Available Document*/
DECLARE @IsAssignedSameDoc BIT;
— Check if the document is assigned
SELECT @IsAssignedSameDoc = CASE WHEN EXISTS (SELECT 1 FROM CheckDocuments
WHERE Document_ID = @DocumentId) THEN 1 ELSE 0 END;
IF @IsAssignedSameDoc = 0
BEGIN
— If not assigned, set next document ID and ID to the current document
SET @NextDocumentId = @LstDocumentId;
SET @NextId = @LstId;
SET @Description=CONCAT(@Description,’ / IsAssignedSameDoc – ‘+
Cast(@IsAssignedSameDoc AS NVARCHAR(10))+’, NextDocumentId’+@NextDocumentId+’,
NextId’+@NextId);
END
ELSE
BEGIN
— If assigned, find the next available document
DECLARE @AvailableDocumentId NVARCHAR(100);
DECLARE @AvailableId INT;
— Get the list of documents assigned to the same task
WITH AssignedDocs AS (
SELECT Document_ID
FROM CheckDocuments
WHERE UserTask = @UserTask
)
SELECT TOP 1 @AvailableDocumentId = Document_ID,@AvailableId=Id
FROM ImportPages
WHERE Document_ID NOT IN (SELECT Document_ID FROM AssignedDocs)
ORDER BY Document_ID;
IF @AvailableDocumentId IS NOT NULL
BEGIN
— If available document found, set its ID as next document ID
SET @NextDocumentId = @AvailableDocumentId;
SET @NextId = @AvailableId;
SET @Description=CONCAT(@Description,’ / @AvailableDocumentId –
‘+ @AvailableDocumentId+’, NextDocumentId’+@NextDocumentId+’, NextId’+@NextId);
END
ELSE
BEGIN
— If not assigned, set next document ID and ID to the current
document
SET @NextDocumentId = @LstDocumentId;
SET @NextId = @LstId;
SET @Description=CONCAT(@Description,’ / ELSE –
NextDocumentId’+@NextDocumentId+’, NextId’+@NextId);
END
END
/*Remove Document from CheckDocument Table*/
— Check if the document exists in CheckDocuments table
IF EXISTS (
SELECT 1
FROM CheckDocuments
WHERE Document_ID = @DocumentID
AND ProjectId = @ProjectId
AND UserTask = @UserTask
AND DocumentStatus = 0
)
BEGIN
— Remove the document from CheckDocuments table
DELETE FROM CheckDocuments
WHERE Document_ID = @DocumentID
AND ProjectId = @ProjectId
AND UserTask = @UserTask
AND DocumentStatus = 0;
SET @Description=CONCAT(@Description,’Removed Document from check
table’);
— Output informational message
PRINT ‘Removed Document from check table: ‘ + @DocumentID;
— Output informational message
PRINT ‘Check Table Data Removed: ‘ + @DocumentID;
END
END
ELSE
BEGIN
SET @NextDocumentId = @LstDocumentId;
SET @NextId = @LstId;
SET @Description=CONCAT(@Description,’ / Outer ELSE –
NextDocumentId’+@NextDocumentId+’, NextId’+@NextId);
END
END
PRINT ‘@nextId – ‘+ Cast(@nextId As VARCHAR(50));
SET @Description=CONCAT(@Description,’ / Next button success’);
EXEC [dbo].[InsertSaveDocAndpartiesLogs] @DocumentID,@Description,0;
SET @ReturnJSONResult =
CASE
WHEN @isReturnFlag = 1 THEN N'{“Response”: “LastDocument”, “Message”: “All
the documents are completed.”}’
WHEN @nextId > 0 THEN N'{“Response”: “success”, “nextDocumentId”: “‘ +
@nextDocumentId + ‘”, “nextId”: ‘ + CAST(@nextId AS NVARCHAR(10)) + ‘, “Message”: “Document
updated successfully”}’
ELSE N'{“Response”: “No documents to code”, “Statuscode”: 404}’
END;
— Output informational message
PRINT ‘JsonData: ‘ + @ReturnJSONResult;
SELECT @ReturnJSONResult As JsonResponse;
COMMIT TRANSACTION; PRINT ‘COMMIT’;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
IF @IsAddPartiesAndPartiesHistory = 0 OR @IsAddCodedData=0 OR
@IsUpdateImportPages=0
OR @IsUpdateAdminRegeTitle=0 OR @IsUpdateExportExtras=0 OR @
@IsInsUpdFieldAdminValidations=0
OR @IsExecCodingSessionDetails=0
ROLLBACK TRANSACTION MySavepoint; — Rollback to savepoint
ELSE
ROLLBACK TRANSACTION; — Rollback entire transaction
END
— Get error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @Description=CONCAT(@Description,’ / ‘+@ErrorMessage);
EXEC [dbo].[InsertSaveDocAndpartiesLogs] @DocumentID,@Description,0;
— Set jsonResponse based on error
IF @ErrorMessage = ‘Sequence contains more than one element’
BEGIN
SET @ReturnJSONResult = N'{“Response”: “Document Saving fail”, “Statuscode”:
500}’;
END
ELSE
BEGIN
IF @ErrorState = 1205
BEGIN
SET @ReturnJSONResult = N'{“Response”: “1205 – Deadlock Detected”,
“Statuscode”: 500}’;
END
ELSE BEGIN
SET @ReturnJSONResult = N'{“Response”: “‘ + @ErrorMessage +
‘”,”AddPartiesAndPartiesHistor”:’+CAST(@IsAddPartiesAndPartiesHistory AS NVARCHAR(10))+'”,
“AddCodedData”:’+CAST(@IsAddCodedData AS
NVARCHAR(10))+'”,”UpdateImportPages”:’+CAST(@IsUpdateImportPages AS NVARCHAR(10))+'”,
“UpdateAdminRegeTitle”:’+CAST(@IsUpdateAdminRegeTitle AS
NVARCHAR(10))+'”,”UpdateExportExtras”:’+CAST(@IsUpdateExportExtras AS NVARCHAR(10))+'”,
“InsUpdFieldAdminValidations”:’+CAST(@IsInsUpdFieldAdminValidations AS
NVARCHAR(10))+'”,
“ExecCodingSessionDetails”:’+CAST(@IsExecCodingSessionDetails AS
NVARCHAR(10))+'”,”Statuscode”: 500}’; END;
END
SELECT @ReturnJSONResult As JsonResponse;
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END;
END;Rest are nested procedures ALTER PROCEDURE [dbo].[AddCodedData]
(
@DocumentID VARCHAR(50) = NULL,
@ProjectId VARCHAR(50) = NULL,
@DocumentDate NVARCHAR(255) = NULL,
@DocumentType NVARCHAR(255) = NULL,
@EnteredById INT = NULL,
@Estimated NVARCHAR(255) = NULL,
@Title NVARCHAR(4000) = NULL,
@CodingQATime INT = NULL,
@IsCorrected INT = NULL,
@UserTask VARCHAR(10) = NULL,
@IsReturn BIT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @Description NVARCHAR(MAX);
BEGIN TRANSACTION;
BEGIN TRY
SET @IsReturn=0;
DECLARE @TempCodedDatas AS TABLE (
[Document_ID] NVARCHAR(255), [Image_File_Name] NVARCHAR(500),
[page_label] NVARCHAR(255), [page_num] INT, [num_pages] INT,
[Coded] INT, [Revision] INT, [DocType] NVARCHAR(255),
[EnteredBy] INT, [HostDocId] NVARCHAR(255),
[ExportDate] DATETIME, [ImportDate] DATETIME, [Percentage]
INT, [SetId] INT, [DateCreated] DATETIME,
LastModified DATETIME, Host_Reference NVARCHAR(255),
[Document_Date] DATETIME, [Estimated] NVARCHAR(255),
[Document_Type] NVARCHAR(255),Title NVARCHAR(4000),
Document_DateValue NVARCHAR(255),
[CodingDate] DATETIME, [CodingTime] INT,[QADate] DATETIME,
[QATime] INT, [IsCorrected] INT
);
INSERT inTO @TempCodedDatas ([Document_ID],
[Image_File_Name], [page_label], [page_num], [num_pages], [Coded], [Revision], [DocType],
[EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId],
[DateCreated], [LastModified], [Host_Reference], [Document_Date], [Estimated],
[Document_Type],
[Title], [Document_DateValue], [CodingDate],[QADate],
[CodingTime],[QATime], [IsCorrected])
SELECT IPS.[Document_ID],IPS.[Image_File_Name],IPS.
[page_label],IPS.[page_num],IPS.[num_pages],1 AS [Coded],
(CASE WHEN @UserTask=’Coder’ THEN 0 ELSE 1 END) AS
[Revision],@DocumentType AS [DocType],@EnteredById AS [EnteredBy],
IPS.[HostDocId],IPS.[ExportDate],IPS.[ImportDate],IPS.
[Percentage],IPS.[SetId],IPS.[DateCreated],GETDATE() AS [LastModified],
ICD.[Host_Reference],
CASE
WHEN ISNULL(@DocumentDate, ”) = ” THEN NULL
ELSE CONVERT(DATETIME, @DocumentDate)
END AS [DocDate],
@Estimated AS [Estimated],
@DocumentType AS [Document_Type],
CASE
WHEN LEN(@Title) > 0 THEN @Title
ELSE ‘Untitled’
END AS [Title],
CASE
WHEN ISNULL(ICD.Document_DateValue, ”) = ” THEN
NULL
ELSE CONVERT(DATETIME, ICD.Document_DateValue)
END AS [Document_DateValue],
(CASE WHEN @UserTask=’Coder’ THEN GETDATE() ELSE NULL
END) AS [CodingDate],
(CASE WHEN @UserTask=’Coder’ THEN NULL ELSE GETDATE()
END) AS [QADate],
(CASE WHEN @UserTask=’Coder’ THEN @CodingQATime ELSE 0
END) AS [CodingTime],
(CASE WHEN @UserTask=’Coder’ THEN 0 ELSE @CodingQATime
END) AS [CodingTime],
@IsCorrected AS [IsCorrected]
FROM
ImportPages IPS
INNER JOIN
ImportCodedDatas ICD ON ICD.Document_ID =
IPS.Document_ID
WHERE
IPS.Document_ID = @DocumentID
IF @UserTask = ‘Coder’
BEGIN
INSERT INTO CodedDatas ([Document_ID], [Image_File_Name],
[page_label], [page_num], [num_pages], [Coded], [Revision],
[DocType], [EnteredBy], [HostDocId],[ExportDate],
[ImportDate], [Percentage], [SetId], [DateCreated], [LastModified],
[main_id],[End_Page],[No_Pages],[Host_Reference],
[Document_Date], [Estimated], [Document_Type], [Title],
[Document_DateValue], [CodingDate], [QADate],
[CodingTime], [QATime], [IsCorrected],[CodingStatus],[QAStatus])
SELECT [Document_ID], [Image_File_Name], [page_label],
[page_num], [num_pages],[Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId],
[DateCreated], [LastModified],NULL,NULL,0, Host_Reference,
[Document_Date], [Estimated], [DocType],Title,
[Document_DateValue],[CodingDate], [QADate], [CodingTime], [QATime],
[IsCorrected],NULL,NULL
FROM @TempCodedDatas
SET @Description=’Coded Data Saved’;
END
ELSE
BEGIN
INSERT INTO CodedDatas ([Document_ID], [Image_File_Name],
[page_label], [page_num], [num_pages], [Coded], [Revision],
[DocType], [EnteredBy], [HostDocId],[ExportDate],
[ImportDate], [Percentage], [SetId], [DateCreated], [LastModified],
[main_id],[End_Page],[No_Pages],[Host_Reference],
[Document_Date], [Estimated], [Document_Type], [Title],
[Document_DateValue], [CodingDate], [QADate],
[CodingTime], [QATime], [IsCorrected],[CodingStatus],[QAStatus])
SELECT [Document_ID], [Image_File_Name], [page_label],
[page_num], [num_pages],[Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
[ExportDate], [ImportDate], [Percentage], [SetId],
[DateCreated], [LastModified],NULL,NULL,0, Host_Reference,
[Document_Date], [Estimated], [DocType],Title,
[Document_DateValue],[CodingDate], [QADate], [CodingTime], [QATime],
[IsCorrected],NULL,NULL
FROM @TempCodedDatas
SET @Description=’Review data Saved’;
END;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,1;
COMMIT TRANSACTION;
SET @IsReturn=1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
— Get error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @Description=@ErrorMessage;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,0;
SET @IsReturn=0;
END CATCH;
END
ALTER PROCEDURE [dbo].[UpdateAdminRegeTitle]
(
@DocumentID VARCHAR(50) = NULL,
@DocumentType NVARCHAR(255) = NULL,
@EnteredById INT = NULL,
@Title NVARCHAR(4000) = NULL,
@DocumentDate NVARCHAR(255) = NULL,
@Estimated NVARCHAR(255) = NULL,
@UserTask VARCHAR(10) = NULL,
@IsReturn BIT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @Description NVARCHAR(MAX);
BEGIN TRANSACTION;
BEGIN TRY
SET @IsReturn=0;
/*Update Admin Regex*/
DECLARE @AdminRegexCount INT;
DECLARE @CodedDataCount INT;
— Get the count of enabled admin regexes
SELECT @AdminRegexCount = COUNT(Id)
FROM AdminRegexs
WHERE Enabled = 1;
— Get the latest coded data for the document
SELECT @CodedDataCount = COUNT(Id)
FROM CodedDatas –WITH (UPDLOCK, SERIALIZABLE)
WHERE Document_ID = @DocumentID;
— Fetch the latest coded data
DECLARE @LatestCodedData TABLE (
Id INT,
Title NVARCHAR(MAX)
);
INSERT INTO @LatestCodedData (Id, Title)
SELECT TOP 1 Id, Title
FROM CodedDatas –WITH (UPDLOCK, SERIALIZABLE)
WHERE Document_ID = @DocumentID
ORDER BY LastModified DESC;
Declare @CodedDataTitle NVarchar(255)
Set @CodedDataTitle=(SELECT Title FROM
@LatestCodedData)
— Apply common updates conditionally
IF (@CodedDataTitle) != ‘Untitled’
BEGIN
UPDATE ImportCodedDatas
SET Document_Date = @DocumentDate,
Estimated = @Estimated,
Document_Type = @DocumentType
WHERE Document_ID = @DocumentID;
END;
DECLARE @TitleString NVARCHAR(MAX);
DECLARE @Replacement NVARCHAR(MAX);
— Apply title regex replacements
IF @AdminRegexCount > 0 AND @CodedDataCount > 0
BEGIN
— Loop through admin regexes
DECLARE @Index INT = 1;
WHILE @Index <= @AdminRegexCount
BEGIN
— Get the current admin regex and
replacement
SELECT @TitleString = Matchexpression,
@Replacement = Replacement
FROM (
SELECT Matchexpression, Replacement,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM AdminRegexs
) AS AdminRegex
WHERE RowNum = @Index;
— Update titles based on admin regex
IF @TitleString = ‘(Proprietary
Limited)+/g’
AND NOT EXISTS (SELECT 1 FROM
@LatestCodedData WHERE Title LIKE ‘%(Proprietary Limited)+/g%’)
BEGIN
SET @TitleString =
REPLACE(REPLACE(REPLACE(@TitleString, ‘(‘, ”), ‘)’, ”), ‘+/g’, ”);
END
ELSE
BEGIN
SET @TitleString =
REPLACE(REPLACE(REPLACE(REPLACE(@TitleString, ‘[‘, ”), ‘]’, ”), ‘+/g’, ”), ‘\’, ”);
SET @Title = REPLACE(CASE WHEN
@TitleString LIKE ‘%[,.;:()!?]+/g%’
THEN
REPLACE(CAST(REPLACE(@Title, @TitleString, @Replacement) AS NVARCHAR(MAX)),
‘[‘ + @TitleString +
‘]’, @Replacement) ELSE @Title END, @TitleString, @Replacement);
END
SET @Title = REPLACE(
CASE
WHEN CHARINDEX(@TitleString,
@CodedDataTitle) > 0 THEN
CASE
WHEN @CodedDataTitle
COLLATE SQL_Latin1_General_CP1_CI_AI LIKE ‘%’ + @TitleString + ‘%’ THEN
REPLACE(@CodedDataTitle, @TitleString, @Replacement)
ELSE @CodedDataTitle
END
ELSE @Title
END
, @TitleString, @Replacement);
— Update titles in ImportCodedDatas
table
UPDATE ImportCodedDatas
SET Title = @Title;
— Update titles in CodedDatas table
UPDATE CodedDatas
SET Title = @Title;
— Increment index
SET @Index = @Index + 1;
END;
End;
Else
Begin
DECLARE @NewTitle NVARCHAR(MAX);
SET @NewTitle = REPLACE(LTRIM(RTRIM(@Title)),
‘ ‘, ”); — Remove leading and trailing spaces
UPDATE ImportCodedDatas
SET Title = CASE
WHEN LEN(@NewTitle) > 0 THEN
@Title
ELSE ‘Untitled’
END
WHERE Document_ID = @DocumentID;
End;
COMMIT TRANSACTION;
SET @Description=’Update Regex Title’;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,1;
SET @IsReturn=1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
— Get error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @Description=@ErrorMessage;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,0;
SET @IsReturn=0;
END CATCH;
END
ALTER PROCEDURE [dbo].[UpdateExportExtras]
(
@DocumentID VARCHAR(50) = NULL,
@IsReturn BIT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @Description NVARCHAR(MAX);
BEGIN TRANSACTION;
BEGIN TRY
SET @IsReturn=0;
DECLARE @MatchExpr NVARCHAR(MAX);
DECLARE @Replacement NVARCHAR(MAX);
DECLARE @UpdatedCount INT;
— Get Export_extras matching the Document_ID
SELECT
@MatchExpr = Matchexpression,
@Replacement = Replacement
FROM
AdminRegexs
WHERE
Enabled = 1;
— Update Export_extras for each AdminRegex
IF @MatchExpr IS NOT NULL AND @Replacement IS
NOT NULL
BEGIN
— Update theValue column
UPDATE Export_extras
SET theValue = REPLACE(theValue,
@MatchExpr, @Replacement)
WHERE Document_ID = @DocumentID AND
theValue IS NOT NULL;
SET @UpdatedCount = @@ROWCOUNT;
PRINT CONCAT(‘Updated ‘, @UpdatedCount, ‘
Export_extras (theValue) for AdminRegex: ‘, @MatchExpr, ‘. Document ID: ‘, @DocumentID);
— Update memoValue column
UPDATE Export_extras
SET memoValue = REPLACE(memoValue,
@MatchExpr, @Replacement)
WHERE Document_ID = @DocumentID AND
memoValue IS NOT NULL;
SET @UpdatedCount = @@ROWCOUNT;
SET @Description=’Updated’+
@UpdatedCount+ ‘ Export_extras (memoValue) for AdminRegex: ‘+ @MatchExpr;
PRINT CONCAT(‘Updated’, @UpdatedCount, ‘
Export_extras (memoValue) for AdminRegex: ‘, @MatchExpr, ‘. Document ID: ‘, @DocumentID);
— Update textValue column
UPDATE Export_extras
SET textValue = REPLACE(textValue,
@MatchExpr, @Replacement)
WHERE Document_ID = @DocumentID AND
textValue IS NOT NULL;
SET @UpdatedCount = @@ROWCOUNT;
SET @Description=’Updated ‘+
@UpdatedCount+ ‘ Export_extras (textValue) for AdminRegex: ‘+ @MatchExpr ;
PRINT CONCAT(‘Updated ‘, @UpdatedCount, ‘
Export_extras (textValue) for AdminRegex: ‘, @MatchExpr, ‘. Document ID: ‘, @DocumentID);
END;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,1;
COMMIT TRANSACTION;
SET @IsReturn=1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
— Get error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @Description=@ErrorMessage;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,0;
SET @IsReturn=0;
END CATCH;
END
ALTER PROCEDURE [dbo].[UpdateImportPages]
(
@DocumentID VARCHAR(50) = NULL,
@DocumentType NVARCHAR(255) = NULL,
@EnteredById INT = NULL,
@UserTask VARCHAR(10) = NULL,
@IsReturn BIT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @Description NVARCHAR(MAX);
BEGIN TRANSACTION;
BEGIN TRY
SET @IsReturn=0;
/*Updating ImportPage*/
IF @UserTask = ‘Coder’
BEGIN
UPDATE ImportPages
SET Coded = 1,
EnteredBy = @EnteredById,
DocType = @DocumentType,
LastModified = GETDATE()
WHERE Document_ID = @DocumentID;
END
ELSE
BEGIN
UPDATE ImportPages
SET Revision = 1,
EnteredBy = @EnteredById,
LastModified = GETDATE()
WHERE Document_ID = @DocumentID;
END;
SET @Description=’Updated Import Pages’;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,1;
COMMIT TRANSACTION;
SET @IsReturn=1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
— Get error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @Description=@ErrorMessage;
EXEC [dbo].[InsertSaveDocAndpartiesLogs]
@DocumentID,@Description,0;
SET @IsReturn=0;
END CATCH;
END Read More
Business Applications Partner News: Week of April 29
Check out this week’s top resources to stay up-to-date on the latest Business Applications Partner News. Remember to sign up for the monthly Dynamics 365 and Power Platform partner pulse newsletters.
What to register for:
THIS WEEK!
April 30 (PDT): Dynamics 365 Supply Chain Management – Demand Planning Workshop
May 2: Supply Chain Premium partner webinar
July 22: Microsoft Partner FY25 GTM Launch Event for Business Applications
What to review/like/share:
Case study: Power Platform influencer MNP Digital helps customers achieve low-code success
Leapwork brings testing efficiencies to Dynamics 365 and Power Platform partners: LinkedIn post | direct blog link
What to watch:
April 18 Tech Talk: Power Apps and SQL
Reminders: Register for the upcoming partner events!
Events:
May 9: SMB Partner Hour
May 16-17: Directions Asia
May 21-23: Microsoft Build
May 30: Copilot Studio Partner Opportunity Webinar
Register for upcoming Microsoft AI Training Roadshows
Trainings:
April 1-May 31: Q4 Microsoft Catalyst Partner Training
FY24 High Volume Acceleration Program (Advanced)
June 11 (IST) (BST): Dynamics 365 Supply Chain Management – Demand Planning Workshop
May 14-16 (PDT) | May 15-17 (IST) (BST): SMB Sales Bootcamp
Check out this week’s top resources to stay up-to-date on the latest Business Applications Partner News. Remember to sign up for the monthly Dynamics 365 and Power Platform partner pulse newsletters.
What to register for:
THIS WEEK!
April 30 (PDT): Dynamics 365 Supply Chain Management – Demand Planning Workshop
May 2: Supply Chain Premium partner webinar
July 22: Microsoft Partner FY25 GTM Launch Event for Business Applications
What to review/like/share:
Case study: Power Platform influencer MNP Digital helps customers achieve low-code success
Leapwork brings testing efficiencies to Dynamics 365 and Power Platform partners: LinkedIn post | direct blog link
What to watch:
April 18 Tech Talk: Power Apps and SQL
Reminders: Register for the upcoming partner events!
Events:
May 9: SMB Partner Hour
May 16-17: Directions Asia
May 21-23: Microsoft Build
May 30: Copilot Studio Partner Opportunity Webinar
Register for upcoming Microsoft AI Training Roadshows
Trainings:
April 1-May 31: Q4 Microsoft Catalyst Partner Training
FY24 High Volume Acceleration Program (Advanced)
June 11 (IST) (BST): Dynamics 365 Supply Chain Management – Demand Planning Workshop
May 14-16 (PDT) | May 15-17 (IST) (BST): SMB Sales Bootcamp Read More
See attached
I am not able to post this, maybe it will post eventually. It keeps asking me to correct highlited errors but cant find any
I am not able to post this, maybe it will post eventually. It keeps asking me to correct highlited errors but cant find any Read More
Removal of Duplicate Task option in ToDo
I have a number of template tasks which include a number of steps in ToDo which I do frequently but not on a regular schedule eg approving someone’s annual leave. I have a list of templates and when I need a new copy I used to duplicate the template task then move the second copy to where I needed it.
In the latest update Microsoft have removed the Duplicate function so I have to manually type the “duplicate” task in again. This is the same in Outlook app and online versions of Outlook and ToDo.
Why are MS taking core functionality away from this app? Do they ever consult users before making changes like this?
Does anyone have a workaround? Please …..
I have a number of template tasks which include a number of steps in ToDo which I do frequently but not on a regular schedule eg approving someone’s annual leave. I have a list of templates and when I need a new copy I used to duplicate the template task then move the second copy to where I needed it. In the latest update Microsoft have removed the Duplicate function so I have to manually type the “duplicate” task in again. This is the same in Outlook app and online versions of Outlook and ToDo.Why are MS taking core functionality away from this app? Do they ever consult users before making changes like this?Does anyone have a workaround? Please ….. Read More
MS Office Outlook rules
I get a lot of spam. most is 1x-3x and then the email domain ‘evaporates.’
but some just continue on for weeks/months/years.
so I set up a “rule” – if sender’s address include (text); permanently delete.
seems the “Rules” function will only accept five
text1 or
text2 or
…
entries.
so I established a second Rule – to add additional permanent delete spammer url.
problem:
there is a check block “and stop processing more rules” – which I uncheck and it auto rechecks.
I initially thought it meant ‘stop processing more rules on-that-message’
but apparently it means ‘stop processing any more rules of any kind’
so what happens is….
“rule #1” encounters an address to permanently delete in my inbox
and does not process “rule #2” – so I’m left with all the spam-to-be-permanently deleted contained in the five spammer url’s of rule #2
how does one tell MS Office Outlook to ‘keep on chugging’ and process all rules listed?
I get a lot of spam. most is 1x-3x and then the email domain ‘evaporates.’but some just continue on for weeks/months/years. so I set up a “rule” – if sender’s address include (text); permanently delete. seems the “Rules” function will only accept fivetext1 ortext2 or…entries. so I established a second Rule – to add additional permanent delete spammer url.problem:there is a check block “and stop processing more rules” – which I uncheck and it auto rechecks.I initially thought it meant ‘stop processing more rules on-that-message’but apparently it means ‘stop processing any more rules of any kind’so what happens is….”rule #1″ encounters an address to permanently delete in my inboxand does not process “rule #2” – so I’m left with all the spam-to-be-permanently deleted contained in the five spammer url’s of rule #2 how does one tell MS Office Outlook to ‘keep on chugging’ and process all rules listed? Read More