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; ENDWhitelisting of file extentions in sharepoint folder
Hi If I have shared a folder with multiple people in SharePoint and I expect only certain file types to be uploaded into my SharePoint's shared folder (e.g. docx,xlsx, ppt, txt) and block all other types of file extensions (e.g. 7zip,rar,exe, dll) so that I can reduce the risk of someone uploading a malicious or an encrypted file, or some kind of source code. I don't want to go by the way of blocking or blacklisting file extensions as that would be too extensive an exercise than whitelisting. Do we have such options in SharePoint that allow us to control the file extensions that can be uploaded? Just want to narrow down the risk from a security perspective. Thanks SAM
my account has been blocked for over a week and i need to recover it urgently
Hi everyone, it's been over a week that my account is blocked and honestly it's frustrating. i changed my phone number so i can't send a recovery code. And the informations i give when filling the forms are authentic. Microsoft sends me forms everyday because apparently it's not me. Please how can i fix this, i REALLY NEED to access my email, i'm waiting for job replies and government forms. This is so frustrating. thank you