Sharepoint 2010 - Get Work Week

Sharepoint 2010 - Get Work Week

Excellent post on getting the Work Week or Week of Year from http://joranmarkx.wordpress.com/2011/12/09/use-the-calculated-field-to-show-weeknumber-in-sharepoint-2010/

For my own notes only, no plagiarism intended.

Work Week calculation:

If the work week starts from the Monday of the week that contains the first Thursday of the year:
=INT(([datecolumn]-DATE(YEAR([datecolumn]-WEEKDAY([datecolumn]-1)+4),1,3)+WEEKDAY(DATE(YEAR([datecolumn]-WEEKDAY(datecolumn-1)+4),1,3))+5)/7)

If the work week starts from the Monday of the week that contains the first Monday of the year:
=INT(([datecolumn]-DATE(YEAR([datecolumn]-WEEKDAY([datecolumn]-1)+4),1,3)+WEEKDAY(DATE(YEAR([datecolumn]-WEEKDAY(datecolumn-1)+4),1,3))+1)/7)

-Calculate the distance between the beginning of the year (with regard of the start of week 1) to the datecolumn
-Add the day of the week of the 1st January of the datecolumn year added with 5 (Thursday)
-Divide by number of days in a week
-Floor by doing a cast to INT

If week 1 starts in the week of the 1st January (USA Standards):
=INT(([datecolumn]-DATE(YEAR([datecolumn]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([datecolumn]),1,1)),"d")))/7)+1

-Calculate the distance between the beginning of the year to the datecolumn
-Add day of the week of the 1st January of the datecolumn year
-Add divide by number of days in a week
-Cast to integer (is Floor)
-Add one day

Sharepoint 2010 - How to filter a view based on Sharepoint Groups

Some of you might have noticed that when we create views in Sharepoint and try to apply a filter based on Sharepoint Groups using equals to [Me] method, the filter will not work properly.

e.g.
User A is a member of Sharepoint Group S1.
In a list, one of the People columns, for example a list of people who can view, might contain S1 but not User A.
When User A views the list which is filtered based on the People column, the results he is supposed to view do not show up.

Workaround:
Open the view (with the filter) in Sharepoint Designer 2010 and locate the filter conditions. It will usually look like this:
<Query><Where><Or><Membership Type="CurrentUserGroups"><FieldRef Name="WhoCanView"/></Membership><Eq><FieldRef Name="WhoCanView"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq></Or></Where></Query>


Append the following before the <eq> tag:
<Membership Type="CurrentUserGroups"><FieldRef Name="WhoCanView"/></Membership>

Like this:
<Query><Where><Or><Membership Type="CurrentUserGroups"><FieldRef Name="WhoCanView"/></Membership><Eq><FieldRef Name="WhoCanView"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq></Or></Where></Query>

CSS Content showing in Sharepoint 2010 Site Settings after Creating Site from Template

After saving a site that has custom CSS as template, and creating a new site based on this template, the user-created content can be loaded properly but pages like Site Settings, Site Permissions etc all show the css content as text near the top section of the page.


To fix, set the AlternateHeader value to "" using PowerShell.
$web = Get-SPWeb "http://"
$web.AlternateHeader (this will list the url of the AlternativeCSS)
$web.AlternateHeader = ""
$web.Update()

How to copy Sharepoint 2010 Blog between sites

A Sharepoint 2010 Blog does not have Save Site as Template link in the Site Settings.

The hard way:

Using Granular Backup and Restore from Site Administration, choose the Blog:
http://mysites/personal//Blog/default.aspx

Backup to local drive:
\\\xxx.cmp

Created a site named Blog, of type Blog.
e.g. http://sharepointsite/MyNewBlog

Use powershell to run this command in the Application Server:
Start > All Programs > Microsoft SharePoint 2010 Products > SharePoint 2010 Management Shell
Import-SPWeb -identity http://sharepointsite/MyNewBlog -path \\\xxx.cmp

The easy way:
or use the shortcut to save Blog site as template
_layouts/savetmpl.aspx
And then upload the site to the solutions gallery and activate it.
Create site from template.

Cannot connect to database master at SQL Server xxxxx. The database might not exist, or the current user does not have permission to connect to it.

When setting up Sharepoint 2010 and running the Sharepoint Products Configuration Wizard for the first time, I got stumped when configuring the content database.

This is the error I see:
"Cannot connect to database master at SQL Server xxxxx. The database might not exist, or the current user does not have permission to connect to it."

I was connecting to the SQL Server 2008 on a Win2k8 R2 machine using Windows Authentication.


I initially suspected it might be a Domain Controller issue so I tried checking the Firewall configuration and Inbound rules for the SQL server. In this case the Firewall was turned off and ports 1433, 1434 were allowing incoming traffic so that does not solve my problem.

I also tried enabling TCP/IP on the SQL Server using the SQL Server Configuration Manager. Still not working.

Next I tried remoting into another server as the same user, launching SQL Server Management Studio and connecting, another error came up:
"Cannot generate SSPI context."
Checking this Microsoft KB usually solves the issue for the majority of cases but it did not help.

By now I have a general idea that this is probably because of some settings on the SQL server itself.
After parsing dozens of articles I finally came across THE one that resolved my issue.
http://dotnettim.wordpress.com/2010/08/26/cannot-generate-sspi-context-error-from-sql-server-management-studio/

Basically the SPN (Service Principal Name) was somehow generated wrongly and could not be deleted. You could view a list of the SPNs by running this command:
setspn -L

I compared it with the SPNs of other SQL servers and there was a couple of extra SPNs like this:
MSSQLSvc/.domain.com:1433
MSSQLSvc/.domain.com

I was unable to delete the SPN using setspn command due to lack of account privileges.

Resolution:
1. Stop SQL Server Agent. Modify the SQL Server service to run under the Local System account.
2. Start SQL Server.
3. Stop SQL Server.
4. Restore the SQL Server service to run under the account that it was set up with initially.
5. Start SQL Server Agent.
6. Run setspn –L to verify SPN again.

*Poof* problem solved!