Errors were encountered during the configuration of the Search Service Application

Issue:
Errors were encountered during the configuration of the Search Service Application

Solution:
Server Role was set to “Application”, which means the SharePoint Search service was stopped.

Changed the role to “Application with Search” and then tried creating Search Service Application and it worked.

Advertisements

SSRS Labels And Rectangle Position And Size Issue When Rendered

It took some time to understand what was happening. So, thought of writing about it.

The problem is quite frustrating until you understand why it is happening.

When you run the report, you can see the alignments of rectangles and textboxes are not correct. If you see the report in design view, apparently they look aligned.

If you see in the below image, the rectangle inside the another rectangle is of the same size, but by mistake (usually because of dragging controls) there’s some value assigned in Left location. So, what happens when it renders it moves to the right and makes all the alignment incorrect.

suggestion, always place controls by cut copy inside a rectangle and move that rectangle by dragging.

Hiding Links in Project Server

If there is no permission option to disable a feature. Use the following CSS in a Content Editor webpart (click here to know how to add). I’m using the following CSS to hide ‘Select from Existing Assignments’ and ‘Add Personal Task’ in Timesheet. You can use the same CSS for hiding other parts of the page.

<style type="text/css">
/*Following CSS selector uses backslash (\) as id is using periods (.)*/
#Ribbon\.ContextualTabs\.TiedMode\.Home\.Tasks\.AddLine\.Menu\.Add\.AddNewLine-Menu16 {
    display:none;
}

/* Commented the following three
#Ribbon\.ContextualTabs\.TiedMode\.Home\.Tasks\.AddLine\.Menu\.Add\.CreateNewTask-Menu16 {
    display:none;
}
#Ribbon\.ContextualTabs\.TiedMode\.Home\.Tasks\.AddLine\.Menu\.Add\.FromCurrentProject-Menu16 {
    display:none;
}
#Ribbon\.ContextualTabs\.TiedMode\.Home\.Tasks\.AddLine\.Menu\.Add\.FromTeamTasks-Menu16 {
    display:none;
}
*/

#Ribbon\.ContextualTabs\.TiedMode\.Home\.Tasks\.AddLine\.Menu\.Add\.NewPersonalTask-Menu16 {
    display:none;
}
</style> 

Preventing SharePoint List Form From Multiple Submission/Entries

If you open SharePoint List item page, enter data and click multiple times on Save button, it will eventually save multiple items. And this very critical if you have some event handler attached to the list, which does some calculations and takes time, so a user will definitely be going to click again thinking it didn’t work on the first click. To avoid this, use the following script that prevents a user from re-submission of form.

<script src="/PWA/ProjectsCenter/Style%20Library/js/jquery-1.10.2.js" type="text/javascript"></script><script type="text/javascript"> 

$("form").submit(function() {
    $(this).submit(function() {
        return false;
    });
    return true;
});
</script> 

Querying SharePoint List Using SQL

#######WARNING#######
Though this method is not supported by Microsoft, and even I don’t recommend it. However, there might be some occasion where you have to break rules and do the job. I have used this query for reporting purpose only and with No Lock so believe there’s no harm.
################

Select  
	--TaskName as [Deliverable Name],
	--TaskStartDate,
	--TaskFinishDate,
	--mdata.tp_Columnset,
	EPM.ProjectUID,
	ProjectName,
	[Portfolio Number],
	[Initiative Number],
	--[Initiative Sponsor],
	MData.tp_ID																					as [Deliverable Number],
	MData.tp_ColumnSet.value('(/ntext2/node())[1]','nvarchar(max)')								as [Description],
	MData.tp_ColumnSet.value('(/int4/node())[1]','int')											as [Acceptance From] ,
	MData.tp_ColumnSet.value('(/nvarchar4/node())[1]','nvarchar(250)')							as [Deliverable Status] ,
	dbo.udf_GetSPDateFieldValue(MData.tp_ColumnSet.value('(/datetime4/node())[1]','datetime'))  as [Certificate of Completion Date],			
	MData.tp_ColumnSet.value('(/nvarchar5/node())[1]','nvarchar(250)')							as [Certificate of Completion Status] ,
	MData.tp_ColumnSet.value('(/float2/node())[1]','float')										as [Deductions] ,
	MData.tp_ColumnSet.value('(/ntext3/node())[1]','nvarchar(max)')								as [Deductions Justification],
	MData.tp_ColumnSet.value('(/ntext4/node())[1]','nvarchar(max)')								as [Acceptance Standards],
	MData.tp_ColumnSet.value('(/ntext5/node())[1]','nvarchar(max)')								as [Notes],
	--MData.tp_ColumnSet.value('(/nvarchar1/node())[1]','nvarchar(250)') as [PO Status] ,
	--MData.tp_ColumnSet.value('(/datetime1/node())[1]','datetime')  as [PO Date],			
	--convert(date,DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),MData.tp_ColumnSet.value('(/datetime1/node())[1]','datetime')))	 as [PO Date],		
	dbo.udf_GetSPDateFieldValue(MData.tp_ColumnSet.value('(/datetime1/node())[1]','datetime'))	as [PO Date],
	MData.tp_ColumnSet.value('(/nvarchar1/node())[1]','nvarchar(250)')							as [PO Number] ,
	MData.tp_ColumnSet.value('(/int1/node())[1]','int')											as [ProjectDeliverableID],
	--MData.tp_ColumnSet.value('(/nvarchar12/node())[1]','nvarchar(250)') as [Payment Order Status] ,
	MData.tp_ColumnSet.value('(/nvarchar2/node())[1]','nvarchar(250)')							as [Payment Order Status], 
	dbo.udf_GetSPDateFieldValue(MData.tp_ColumnSet.value('(/datetime2/node())[1]','datetime'))	as [Status Date],

	MData.tp_ColumnSet.value('(/float4/node())[1]','float')										as [Initiation] ,
	MData.tp_ColumnSet.value('(/float6/node())[1]','float')										as [Planning] ,
	MData.tp_ColumnSet.value('(/float7/node())[1]','float')										as [Execution] ,
	MData.tp_ColumnSet.value('(/float5/node())[1]','float')										as [Closing] ,
	MData.tp_ColumnSet.value('(/float8/node())[1]','float')										as [D.I.C.E. %] ,
	MData.tp_ColumnSet.value('(/sql_variant1/node())[1]','float')								as [Quality Index]

	--MData.tp_ColumnSet --To get all the fields xml

From 
--If you not using EPM you can commit the following join
WSS_Content_MOEKSA.pjrep.[MSP_EpmProject_UserView] EPM WITH (NOLOCK)
inner join WSS_Content_MOEKSA.[dbo].Webs as MWebs WITH (NOLOCK) on  CHARINDEX((MWebs.FullUrl+' ') collate SQL_Latin1_General_CP1_CI_AS  , (epm.ProjectWorkspaceInternalHRef+' ')) >0
inner join WSS_Content_MOEKSA.[dbo].[AllLists] Lists WITH (NOLOCK) on Lists.tp_WebId = MWebs.Id 
inner join  WSS_Content_MOEKSA.[dbo].AllUserData MData WITH (NOLOCK) on MData.tp_ListId = Lists.tp_Id

Where tp_Title Like 'Deliverables Log/%' 
and (Mdata.tp_DeleteTransactionId = 0x or Mdata.tp_DeleteTransactionId is NULL)

Following function is used to convert SharePoint date field if your Time Zone is different for the site where list is residing from the main site.

-- =============================================
-- Author:		Kashif
-- Create date: 
-- Description:	Convert SharePoint date field into correct value
-- =============================================
ALTER FUNCTION [dbo].[udf_GetSPDateFieldValue] 
(
	-- Add the parameters for the function here
	@date Datetime
)
RETURNS date
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result date

	-- Add the T-SQL statements to compute the return value here
	
	--SELECT @Result = convert(date,DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),@date))	 		
	-- Adding 1 hour to difference of UTC time and date which is 3 hours because of KSA time. Some dates have this format 2017-03-29T20:00:00
        -- Main site was set to (UTC+3 Kuwait,Riyadh) and subsite from where data was being pulled was set to (UTC+4 Abu Dhabi, Muscat). This is the reason for using following function 


	SELECT @Result = convert(date,DATEADD(HOUR,DATEDIFF(HOUR,getutcdate(),GETDATE())+1,@date))	 		


	-- Return the result of the function
	RETURN @Result

END