SharePoint Server, MOSS, SharePoint Development, Collaboration: 2013

Monday, November 4, 2013

Fixing a SharePoint 2010 Stuck Timer Job

Resolution :Toresolve this issue, clear the file system cache on all servers in the serverfarm on which the Windows SharePoint Services Timer service is running. To dothis, follow these steps:

Stop the Timer service. To do this, follow these steps:

Click Start, point to Administrative Tools, and then click Services.
Right-click Windows SharePoint Services Timer, and then click Stop.
Close the Services console.

On the computer that is running Microsoft Office SharePoint Server 2007 and on which the Central Administration site is hosted, click Start, click Run, type explorer, and then press ENTER.
In Windows Explorer, locate and then double-click the following folder:

Drive:\Documents andSettings\All Users\Application Data\Microsoft\SharePoint\Config\GUID

Notes

The Drive placeholder specifies the letter of the drive on which Windows is installed. By default, Windows is installed on drive C.
The GUID placeholder specifies the GUID folder.
The Application Data folder may be hidden. To view the hidden folder, follow these steps:

On the Tools menu, click Folder Options.
Click the View tab.
In the Advanced settings list, click Show hidden files and folders under Hidden files and folders, and then click OK.

In Windows Server 2008, the configuration cache is in the following location:

Drive:\ProgramData\Microsoft\SharePoint\Config\GUID

Back up the Cache.ini file.
Delete all the XML configuration files in the GUID folder. Do this so that you can verify that the GUID folder is replaced by new XML configuration files when the cache is rebuilt.

Note When you empty the configuration cache in the GUID folder, make sure that you do not delete the GUID folder and the Cache.ini file that is located in the GUID folder.

Double-click the Cache.ini file.
On the Edit menu, click Select All.
On the Edit menu, click Delete.
Type 1, and then click Save on the File menu.
On the File menu, click Exit.
Start the Timer service. To do this, follow these steps:

Click Start, point to Administrative Tools, and then click Services.
Right-click Windows SharePoint Services Timer, and then clickStart.
Close the Services console.

Note The file systemcache is re-created after you perform this procedure. Make sure that youperform this procedure on all servers in the server farm.

Make sure that the Cache.ini file in the GUID folder now contains its previous value. For example, make sure that the value of the Cache.ini file is not 1.
Click Start, point to Programs, point to Administrative Tools, and then clickSharePoint 3.0 Central Administration.
Click the Operations tab, and then click Timer job status under Global Configuration.
In the list of timer jobs, verify that the status of the Config Refresh entry issucceeded.
On the File menu, click Close.

Sunday, September 29, 2013

How to Create a Minimal Master Page


To create a minimal master page



Open SharePoint Designer.


On the File menu, click New, point to SharePoint Content, and then click the Page tab.


Double-click Master Page to create a new master page.


Click Design to show the master page in design view. You should see header and left margin areas and several content placeholders in the master page.


Click Code to show the master page in code view.


Copy the following code into the master page.

<%-- Identifies this page as a .master page written in Microsoft Visual C# and registers tag prefixes, namespaces, assemblies, and controls. --%>
<%@ Master language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Register Tagprefix="SPSWC" Namespace="Microsoft.SharePoint.Portal.WebControls" Assembly="Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="PublishingWebControls" Namespace="Microsoft.SharePoint.Publishing.WebControls" Assembly="Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="PublishingNavigation" Namespace="Microsoft.SharePoint.Publishing.Navigation" Assembly="Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="wssuc" TagName="Welcome" src="~/_controltemplates/Welcome.ascx" %>
<%@ Register TagPrefix="wssuc" TagName="DesignModeConsole" src="~/_controltemplates/DesignModeConsole.ascx" %>
<%@ Register TagPrefix="PublishingVariations" TagName="VariationsLabelMenu" src="~/_controltemplates/VariationsLabelMenu.ascx" %>
<%@ Register Tagprefix="PublishingConsole" TagName="Console" src="~/_controltemplates/PublishingConsole.ascx" %>
<%@ Register TagPrefix="PublishingSiteAction" TagName="SiteActionMenu" src="~/_controltemplates/PublishingActionMenu.ascx" %>
<%-- Uses the Microsoft Office namespace and schema. --%>
<html>
  <WebPartPages:SPWebPartManager runat="server"/>
  <SharePoint:RobotsMetaTag runat="server"/>

  <%-- The head section includes a content placeholder for the page title and links to CSS and ECMAScript (JScript, JavaScript) files that run on the server. --%>
  <head runat="server">
    <asp:ContentPlaceHolder runat="server" id="head">
      <title>
        <asp:ContentPlaceHolder id="PlaceHolderPageTitle" runat="server" />
      </title>
    </asp:ContentPlaceHolder>
    <Sharepoint:CssLink runat="server"/>
    <asp:ContentPlaceHolder id="PlaceHolderAdditionalPageHead" runat="server" />
  </head>
  
  <%-- When loading the body of the .master page, SharePoint Server 2007 also loads the SpBodyOnLoadWrapper class. This class handles .js calls for the master page. --%>
  <body onload="javascript:_spBodyOnLoadWrapper();">
    <%-- The SPWebPartManager manages all of the Web part controls, functionality, and events that occur on a Web page. --%>
    <form runat="server" onsubmit="return _spFormOnSubmitWrapper();">
      <wssuc:Welcome id="explitLogout" runat="server"/>
      <PublishingSiteAction:SiteActionMenu runat="server"/>  
      <PublishingWebControls:AuthoringContainer id="authoringcontrols" runat="server">
        <PublishingConsole:Console runat="server" />
      </PublishingWebControls:AuthoringContainer>
      <%-- The PlaceHolderMain content placeholder defines where to place the page content for all the content from the page layout. The page layout can overwrite any content placeholder from the master page. Example: The PlaceHolderLeftNavBar can overwrite the left navigation bar. --%>
      <asp:ContentPlaceHolder id="PlaceHolderMain" runat="server" />
        <asp:Panel visible="false" runat="server">
        <%-- These ContentPlaceHolders ensure all default SharePoint Server pages render with this master page. If the system master page is set to any default master page, the only content placeholders required are those that are overridden by your page layouts. --%>
<asp:ContentPlaceHolder id="PlaceHolderSearchArea" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderTitleBreadcrumb" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderPageTitleInTitleArea"  runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderLeftNavBar" runat="server"/>
<asp:ContentPlaceHolder ID="PlaceHolderPageImage" runat="server"/>
<asp:ContentPlaceHolder ID="PlaceHolderBodyLeftBorder" runat="server"/>
<asp:ContentPlaceHolder ID="PlaceHolderNavSpacer" runat="server"/>
<asp:ContentPlaceHolder ID="PlaceHolderTitleLeftBorder" runat="server"/>
<asp:ContentPlaceHolder ID="PlaceHolderTitleAreaSeparator" runat="server"/>
<asp:ContentPlaceHolder ID="PlaceHolderMiniConsole" runat="server"/>
<asp:ContentPlaceHolder id="PlaceHolderCalendarNavigator" runat ="server" />
<asp:ContentPlaceHolder id="PlaceHolderLeftActions" runat ="server"/>
<asp:ContentPlaceHolder id="PlaceHolderPageDescription" runat ="server"/>
<asp:ContentPlaceHolder id="PlaceHolderBodyAreaClass" runat ="server"/>
<asp:ContentPlaceHolder id="PlaceHolderTitleAreaClass" runat ="server"/>
<asp:ContentPlaceHolder id="PlaceHolderBodyRightMargin" runat="server" />
</asp:Panel>
    </form>
  </body>
</html>

Monday, March 4, 2013

Select Sql Date / DateTime Formats By Name

– SQL format datetime by name

– Default format: Oct 23 2006 10:40AM

SELECT [Default]=CONVERT(varchar,GETDATE(),100)

– US-Style format: 10/23/2006

SELECT [US-Style]=CONVERT(char,GETDATE(),101)

– ANSI format: 2006.10.23

SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

– UK-Style format: 23/10/2006

SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

– German format: 23.10.2006

SELECT [German]=CONVERT(varchar,GETDATE(),104)

– ISO format: 20061023

SELECT ISO=CONVERT(varchar,GETDATE(),112)

– ISO8601 format: 2008-10-23T19:20:16.003

SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)

————

– SQL Server datetime formats

– Century date format MM/DD/YYYY usage in a query

– Format dates SQL Server 2005

SELECT TOP (1)

      SalesOrderID,

      OrderDate = CONVERT(char(10), OrderDate, 101),

      OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

/* Result

SalesOrderID      OrderDate               OrderDateTime

43697             07/01/2001          2001-07-01 00:00:00.000

*/

How to Create SQL Server Date Formatting function

In this post we will learn How to create a SQL Server function to display Date in different formats

– SQL Server date formatting function – convert datetime to string

————

– SQL datetime functions

– SQL Server date formats

– T-SQL convert dates

– Formatting dates sql server

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

    DECLARE @StringDate VARCHAR(32)

    SET @StringDate = @FormatMask

    IF (CHARINDEX (‘YYYY’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘YYYY’,

                         DATENAME(YY, @Datetime))

    IF (CHARINDEX (‘YY’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘YY’,

                         RIGHT(DATENAME(YY, @Datetime),2))

    IF (CHARINDEX (‘Month’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘Month’,

                         DATENAME(MM, @Datetime))

    IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

       SET @StringDate = REPLACE(@StringDate, ‘MON’,

                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))

    IF (CHARINDEX (‘Mon’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘Mon’,

                                     LEFT(DATENAME(MM, @Datetime),3))

    IF (CHARINDEX (‘MM’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘MM’,

                  RIGHT(’0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

    IF (CHARINDEX (‘M’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘M’,

                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

    IF (CHARINDEX (‘DD’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘DD’,

                         RIGHT(’0′+DATENAME(DD, @Datetime),2))

    IF (CHARINDEX (‘D’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘D’,

                                     DATENAME(DD, @Datetime))   

RETURN @StringDate

END

GO

– Microsoft SQL Server date format function test

– MSSQL formatting dates

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’)           – 01/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’)           – 03/01/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’)            – 1/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’)             – 1/3/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’)               – 1/3/12

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’)             – 01/03/12

SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’)         – JAN 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’)         – Jan 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’)       – January 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’)           – 2012/01/03

SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’)             – 20120103

SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’)           – 2012-01-03

– CURRENT_TIMESTAMP returns current system date and time in standard internal format

SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’)      – 12.01.03

GO

....

In the following post we will see how to select Sql DATE/DATETIME FORMATS by name, Follow me :)

Formating Datetime & Date in Microsoft Sql Server

Data formats available in SQL Server.

We will start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.

– Microsoft SQL Server T-SQL date and datetime formats

– Date time formats – mssql datetime 

– MSSQL getdate returns current system date and time in standard internal format

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

                                        – Oct  2 2008 11:01AM          

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008                  

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

                                        – Oct  2 2008 11:02:44:013AM   

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

                                        – 02 Oct 2008 11:02:07:577     

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

                                        – 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126)                 – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8)          – mon yyyy

In the following post we will create a function to convert datetime to string, follow it

Monday, January 7, 2013

SQL Queries for SharePoint Content Database

We will begin with some of the basic tables:

Features Table that holds information about all the activated features for each site collection or site.
Sites Table that holds information about all the site collections for this content database.
Webs Table that holds information about all the specific sites (webs) in each site collection.
UserInfo Table that holds information about all the users for each site collection.
Groups Table that holds information about all the SharePoint groups in each site collection.
Roles Table that holds information about all the SharePoint roles (permission levels) for each site.
AllLists Table that holds information about lists for each site.
GroupMembership Table that holds information about all the SharePoint group members.
AllUserData Table that holds information about all the list items for each list.
AllDocs Table that holds information about all the documents (and all list items) for each document library and list.
RoleAssignment Table that holds information about all the users or SharePoint groups that are assigned to roles.
SchedSubscriptions Table that holds information about all the scheduled subscriptions (alerts) for each user.
ImmedSubscriptions Table that holds information about all the immediate subscriptions (alerts) for each user.

Some of the common queries that can be used against the content database:
-- Query to get all the top level site collections
SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated
FROM dbo.Webs
WHERE (ParentWebId IS NULL)

-- Query to get all the child sites in a site collection
SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated
FROM dbo.Webs
WHERE (NOT (ParentWebId IS NULL))

-- Query to get all the SharePoint groups in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.Groups.ID AS Expr1,
dbo.Groups.Title AS Expr2, dbo.Groups.Description
FROM dbo.Groups INNER JOIN
dbo.Webs ON dbo.Groups.SiteId = dbo.Webs.SiteId

-- Query to get all the users in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.UserInfo.tp_ID,
dbo.UserInfo.tp_DomainGroup, dbo.UserInfo.tp_SiteAdmin, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Email
FROM dbo.UserInfo INNER JOIN
dbo.Webs ON dbo.UserInfo.tp_SiteID = dbo.Webs.SiteId
-- Query to get all the members of the SharePoint Groups
SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.GroupMembership INNER JOIN
dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId INNER JOIN
dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID
-- Query to get all the sites where a specific feature is activated
SELECT dbo.Webs.Id AS WebGuid, dbo.Webs.Title AS WebTitle, dbo.Webs.FullUrl AS WebUrl, dbo.Features.FeatureId,
dbo.Features.TimeActivated
FROM dbo.Features INNER JOIN
dbo.Webs ON dbo.Features.SiteId = dbo.Webs.SiteId AND dbo.Features.WebId = dbo.Webs.Id
WHERE (dbo.Features.FeatureId = '00BFEA71-D1CE-42de-9C63-A44004CE0104')

-- Query to get all the users assigned to roles
SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,
dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.UserInfo ON dbo.RoleAssignment.PrincipalId = dbo.UserInfo.tp_ID

-- Query to get all the SharePoint groups assigned to roles
SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,
dbo.Groups.Title AS GroupName
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN dbo.Groups ON dbo.RoleAssignment.SiteId = dbo.Groups.SiteId AND
dbo.RoleAssignment.PrincipalId = dbo.Groups.ID

-- Query to get all Files by date

SELECT AllLists.tp_Title AS 'List Name',

AllDocs.LeafName AS 'File Name',

AllDocs.[TimeLastModified] AS 'TimeLastModified',

AllDocs.DirName AS 'URL'

FROM AllDocs

JOIN AllLists

ON AllLists.tp_id = AllDocs.ListId WHERE AllDocs.[TimeLastModified]>='2012-01-01' AND AllDocs.Type <> 1 AND (LeafName NOT LIKE '%.stp') AND (LeafName NOT LIKE '%.aspx') AND (LeafName NOT LIKE '%.xfp') AND (LeafName NOT LIKE '%.dwp') AND (LeafName NOT LIKE '%template%') AND (LeafName NOT LIKE '%.inf') AND (LeafName NOT LIKE '%.css')