Using SQL Alias unable to connect after power failure

Odd situation with SharePoint 2019 on premise - we had a power failure and on restart, the entire farm was down. It too a bit of research - logs were no help - simply 'could not connect'.

After going back through all of the settings, it turns out it was 'cliconfg' - the alias I set up (SPSQL) was still there, but instead of pointing to the server, it replaced the Server Name with SPSQL - go figure?

Reset via cliconfg and viola - everything back to normal

Calculating End Date by Business Days, Holidays or Both

I've always had issues trying to calculate dates and I've always forgotten to keep the code somewhere. It's easy to calculate based business days, but holidays are always a pain. I post this here to help you out as well as keep a record of this myself!

So this is C# and pretty simple stuff - works like a charm.

In your code, first you define a Class to enable you to create a list of Holidays from whatever source. This has two elements - the Date and the Number of Days to apply:

public class HolidayDate
{
    public DateTime HolidayDt;
    public int AddDaysForHoliday;
}

You can populate a list using a source like SQL or you can simply set the dates as so (and just in case you are wondering, to use List, you need to add a reference to System.Collections.Generic, I just hate when people don't include that):

public List<HolidayDate> PopulateHolidays()

{

    List<HolidayDate> ListOfHolidays = new List<HolidayDate>();

    HolidayDate VeteransDay = new HolidayDate();

    VeteransDay.HolidayDt = new DateTime(2019, 11, 11);

    VeteransDay.AddDaysForHoliday = 1;

    ListOfHolidays.Add(VeteransDay);

    HolidayDate ThanksGiving = new HolidayDate();

    ThanksGiving.HolidayDt = new DateTime(2019, 11, 28);

    ThanksGiving.AddDaysForHoliday = 2;

    ListOfHolidays.Add(ThanksGiving);

    HolidayDate ShutDownHoliday = new HolidayDate();

    ShutDownHoliday.HolidayDt = new DateTime(2019, 11, 04);

    ShutDownHoliday.AddDaysForHoliday = 5;

    ListOfHolidays.Add(ShutDownHoliday);

    HolidayDate ChristmasDay = new HolidayDate();

    ChristmasDay.HolidayDt = new DateTime(2019, 12, 25);

    ChristmasDay.AddDaysForHoliday = 1;

    ListOfHolidays.Add(ChristmasDay);

    HolidayDate NewYears = new HolidayDate();

    NewYears.HolidayDt = new DateTime(2019, 12, 31);

    NewYears.AddDaysForHoliday = 2;

    ListOfHolidays.Add(NewYears);

    return ListOfHolidays;

}

Notice that I add the number of the days for the Holiday - for example, the Shutdown time in this case is for a week - so setting the Holiday as 11/4 for 5 days blocks out the entire week.

So here are the primary methods:

public DateTime CalcDaysWithHolidaysOnly(DateTime StartDt, int DaysToAdd)

{

    List<HolidayDate> ListOfHolidayDates = new List<HolidayDate>();

    ListOfHolidayDates = PopulateHolidays();

    //

    int AddToDateDays = 0;

    //

    DateTime EndDate = StartDt.AddDays(DaysToAdd);

    DateTime TestDate = StartDt;

    //

    for (int AddDay = 0;AddDay < DaysToAdd; AddDay++)

    {

        //

        // Move forward for holiday:

        //

        AddToDateDays = 1;

        foreach (HolidayDate aHoliday in ListOfHolidayDates)

        {

            if (TestDate.Date.AddDays(1) == aHoliday.HolidayDt.Date)

            {

                int GetDaySpread = aHoliday.AddDaysForHoliday;

                if (GetDaySpread == 1)

                {

                        AddToDateDays++;

                }

                else

                {

                    for (int i = 0; i < GetDaySpread; i++)

                    {

                          AddToDateDays++;

                    }

                }

                break;

            }

        }

        TestDate = TestDate.AddDays(AddToDateDays);

        //Console.WriteLine("Test Date: " + TestDate.ToShortDateString());

    }

    //

    // Final Date:

    //

    EndDate = TestDate;

    //

    return EndDate;

}

public DateTime CalcBusinessDaysWithHolidays(DateTime StartDt, int DaysToAdd)

{

    List<HolidayDate> ListOfHolidayDates = new List<HolidayDate>();

    ListOfHolidayDates = PopulateHolidays();

    //

    int AddToDateDays = 0;

    //

    DateTime EndDate = StartDt.AddDays(DaysToAdd);

    DateTime TestDate = StartDt;

    //

    for (int AddDay = 0; AddDay < DaysToAdd; AddDay++)

    {

        //

        // Move forward for Non-Business Days:

        //

        AddToDateDays = 1;

        if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Saturday)

        {

            AddToDateDays = AddToDateDays + 2;

        }

        else if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Sunday)

        {

            AddToDateDays = AddToDateDays + 1;

        }

        //

        // Move forward for holiday:

        //

        foreach (HolidayDate aHoliday in ListOfHolidayDates)

        {

            if (TestDate.Date.AddDays(AddToDateDays) == aHoliday.HolidayDt.Date)

            {

                int GetDaySpread = aHoliday.AddDaysForHoliday;

                if (GetDaySpread == 1)

                {

                    if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Saturday)

                    {

                        AddToDateDays = AddToDateDays + 2;

                    }

                    else if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Sunday)

                    {

                        AddToDateDays = AddToDateDays + 1;

                    }

                    else

                    {

                        AddToDateDays++;

                    }

                }

                else

                {

                    for (int i = 0; i < GetDaySpread; i++)

                    {

                        if (TestDate.Date.AddDays(AddToDateDays + i).DayOfWeek == DayOfWeek.Saturday)

                        {

                            AddToDateDays = AddToDateDays + i + 2;

                        }

                        else if (TestDate.Date.AddDays(AddToDateDays + i).DayOfWeek == DayOfWeek.Sunday)

                        {

                            AddToDateDays = AddToDateDays + i + 1;

                        }

                        else

                        {

                            AddToDateDays++;

                        }

                    }

                }

                break;

            }

        }

        TestDate = TestDate.AddDays(AddToDateDays);

        //Console.WriteLine("Test Date: " + TestDate.ToShortDateString());

    }

    //

    // Final Date:

    //

    EndDate = TestDate;

    //

    if (EndDate.Date.DayOfWeek == DayOfWeek.Saturday)

    {

        EndDate = EndDate.AddDays(2);

    }

    if (EndDate.Date.DayOfWeek == DayOfWeek.Sunday)

    {

        EndDate = EndDate.AddDays(1);

    }

    return EndDate;

}

public DateTime CalcBusinessDaysOnly(DateTime StartDt, int DaysToAdd)

{

    List<HolidayDate> ListOfHolidayDates = new List<HolidayDate>();

    ListOfHolidayDates = PopulateHolidays();

    //

    int AddToDateDays = 0;

    //

    DateTime EndDate = StartDt.AddDays(DaysToAdd);

    DateTime TestDate = StartDt;

    //

    for (int AddDay = 0; AddDay < DaysToAdd; AddDay++)

    {

        //

        // Move forward for Non-Business Days:

        //

        AddToDateDays = 1;

        if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Saturday)

        {

            AddToDateDays = AddToDateDays + 2;

        }

        else if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Sunday)

        {

            AddToDateDays = AddToDateDays + 1;

        }

        TestDate = TestDate.AddDays(AddToDateDays);

        Console.WriteLine("Test Date: " + TestDate.ToShortDateString());

    }

    //

    // Final Date:

    //

    EndDate = TestDate;

    //

    if (EndDate.Date.DayOfWeek == DayOfWeek.Saturday)

    {

        EndDate = EndDate.AddDays(2);

    }

    if (EndDate.Date.DayOfWeek == DayOfWeek.Sunday)

    {

        EndDate = EndDate.AddDays(1);

    }

    return EndDate;

}

Calling is just as simple:

DateTime StartDt = new System.DateTime(2019, 10, 29);

int DaysToAdd = 60;

DateTime EndingDate = CalcDaysWithHolidaysOnly(StartDt, DaysToAdd);

DateTime EndingDateBus = CalcBusinessDaysWithHolidays(StartDt, DaysToAdd);

DateTime EndingDateBusOnly = CalcBusinessDaysOnly(StartDt, DaysToAdd);

So hopefully this helps you - if anyone has a better routine, I'd like to see it!!

Removing the Default Documents Library in SharePoint

So you want to delete the default Documents (or Shared Documents) library - which you should but in many site templates it cannot be deleted (the Delete this library link isn't shown). 

Quick and easy, do it in PowerShell:

# Set Site URL, i.e. http://<site>, http://site/sites/abc, https://, etc.

$theWeb = Get-SPWeb <Site URL>

# Display the object (will display the URL to the site)

$theWeb

$list = $theWeb.Lists[“Documents”]

# Display List Details

$list

# Make it deletable

$list.AllowDeletion = $True

# Update it

$list.Update()

# Now delete it

$list.Delete()

PS: This is also a reminder for myself - I know what to do just forget sometimes!

Setting Time Servers (Windows)

A note here for future reference! You might find yourself off time on a server, laptop, pc, etc. and it is a great idea to sync with a known time service. While you have to have admin rights on the individual box (or VM), you can set the time servers using an elevated Command prompt (i.e. Run as Administrator).

I happen to use the NTP servers in the US for synching all of my servers. So - how to set these? A few simple commands - stop the time service, set the sync option with server list, set as reliable and start it back up - the last two commands show you the configuration and status.

Open a command prompt (cmd) using Run as administrator and enter the following (careful, second line is wrapped):

net stop w32time
w32tm /config /syncfromflags:manual /manualpeerlist:"0.us.pool.ntp.org,1.us.pool.ntp.org,2.us.pool.ntp.org,3.us.pool.ntp.org"
w32tm /config /reliable:yes
net start w32time

w32tm /query /configuration
w32tm /query /status

All set!

Active Directory Error 0X800708C5

I get questions about this error all the time - the answer is usually very simple - the password you are attempting to apply does not meet the criteria required.

Hope that helps!

The timer service failed to recycle

This is one I see all the time on client sites - under Review problems and solutions you see the message "The timer service failed to recycle". The problem is actually due to running other jobs that conflict with the recycle process - in short, something else is running.

Many have a habit of having long running jobs start after Midnight (00:00) which is generally around the time the Recycle job runs - if something else is running, the job fails. The solution is quite simple - either A) review and modify jobs that run at the same time as the Recycle job and make sure they don't overlap or B) simply change the time that the recycle happens. Since A may take some time, B is quite simple.

Open Central Administration and click Monitoring then Review job definitions and look for "Timer Service Recycle" (you'll have to page through to find it). Click to open the job and change the Starting time to be either earlier or later than other long running jobs. In most cases (depending on the installation and/or 24 x 7 usage), you can usually set it for late evening (like 10 PM).

For more reference, see here:
https://docs.microsoft.com/en-us/sharepoint/technical-reference/the-timer-service-failed-to-recycle

SharePoint Most Popular does not appear in Lists

As we all know, document libraries have the option to review Most Popular items. While there can be issues with this (showing no counts for example), it does work in most cases. Assuming you are getting counts, you might notice that this option does not appear in Lists.

Enabling this is easy enough - simply activate the Cross Site Publishing feature - Site Settings > Site Collection Features > Cross-Site Collection Publishing.