Code snippet to update Sharepoint 2010 Modified By

When uploading files, the date created and date last modified is not taken from the file system. Instead, it is generated by Sharepoint.
You can build your own API to upload a file to Sharepoint and update the fields programmatically.

SPFieldUserValue author = new SPFieldUserValue(web, spUser.ID, spUser.LoginName);
listItem[SPBuiltInFieldId.Created] = dtCreated;
listItem[SPBuiltInFieldId.Modified] = dtModified;
listItem[SPBuiltInFieldId.Author] = author;
listItem[SPBuiltInFieldId.Editor] = author;
listItem.Update();

Infopath 2010 - Select a Photo from Picture Library

1. Copy the picture library by going to Picture Library Settings – Save Site as template (and include content) – optional. You can use the existing if you wish.
2. Take note of the photo URL of any picture – you will need this later.

3. Create a new Custom list. Just add one field, Employees as Person or Group. Make this field compulsory. You will add in the rest of the fields from Infopath later.4. Edit the form in Infopath 2010.

Infopath 2010 Only create new list item within a given date range

Create a Sharepoint 2010 list and add some fields.
Open the list in Infopath 2010 for editing.

Add a new variable, e.g. Today.
Add a new Form Load rule and assign the variable to the current date and time. This will be used for checking the date later.

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!

Snacks to bring back from Taiwan

From Taiwan with love.

Honey Jujube 蜜棗 - Seasonal local produce. A type of fruit that is very crisp and juicy and sweet.

Red Bean Glutinous Rice Cakes Q餅 from Saint Paul Patisserie 聖保羅

Bebek Tepi Sawah Restaurant, Ubud, Bali

Although it has been almost 1 year since my Bali visit, I never got around to completing the rest of the posts.

Of all the restaurants and warungs that we have visited in Bali, Bebek Tepi Sawah left the most lasting impression on me. It deserves a post of its own!

We were lucky for opting to dine there during lunch instead of dinner, as the beautiful surroundings and deco of the restaurant would be barely visible at night.

The most popular picture-taking spot (above).

Hai Siang Seafood Restaurant, Simpang Ampat

Made our way to Hai Siang one lazy afternoon as Ms M could not forget the memorable taste of their Yam Duck.

Saw a stall displaying Pi Pa duck so we decided to give it a try.
The accompanying sauce was absolutely divine.




The next dish to be served was the Teo Chew Mee Tiau.

I miss my NIkki.

Dear Nikon,

It's been months since I last picked you up and spent quality time with you. I miss you!

DQL to list workitems of a document

select i.r_object_id, a.object_name, i.r_performer_name, wf.object_name as work_flow_name, wf.r_object_id as workflow_id
from dmi_workitem i, dm_activity a, dm_workflow wf
where i.r_act_def_id = a.r_object_id
and i.r_workflow_id = wf.r_object_id
and wf.r_object_id in
(select r_workflow_id from dmi_package
where any r_component_id in
(select r_object_id from dm_sysobject (all)
where i_chronicle_id in
(select i_chronicle_id
from dm_sysobject where r_object_id in (SELECT r_object_id FROM dm_document
WHERE object_name LIKE 'N0050675%'
)
)
)
)
order by wf.r_object_id

Email Bounce handling with vbscript

Basically, what we need to do:
1 - Setup a new email account to store all bounced emails.
2 - Designate the bounce-back email to a dedicated email address. For easy handling, I set a custom header in the email to store the email address to track.
e.g. x-MyCustomHdr =
3 - Create a script to parse this mailbox, and save the email address to a database table. This script comes with an INI file. Credits to http://www.westphil.nl/systemadministration/vbscript/index.php?name=mailread for the original source code.

Pre-requisites:
Since Outlook 2007, Microsoft no longer bundles the MAPI.Session ActiveX object. This component needs to be installed on the machine. The component can be downloaded from Microsoft: http://www.microsoft.com/download/en/details.aspx?DisplayLang=en&id=3671
The solution is only 32bit compatible.
Outlook needs to be installed on the machine.

This is a script to parse an Outlook account, and save the email address to database.

'On Error Resume next

Dim objSession
Dim objIs 
Dim objFldSource
Dim objFldDestination
Dim objMessage
Dim objAttachment
Dim ivalue 
Dim strSubj, strBody, strEmail
Dim strConn, objConn

strIniFile = "MailRead.ini"
strSection = "Config"


strMapiProfile = ReadINI(strINIFile, strSection, "MapiProfile")
strMailBox = ReadINI(strINIFile, strSection, "MailBox")
strKey = ReadINI(strINIFile, strSection, "MailHeader")

strConn = "Driver={SQL Server};Data Source=XXXXXXX;Database=XXXXXXXX;UID=XXXXXXXXXX;PWD=XXXXXXXXXXXXXX;"
set objConn = CreateObject("ADODB.Connection")
objConn.open strConn

' Logon
Set objSession = CreateObject("MAPI.Session")
Call objSession.Logon(strMapiProfile)
Wscript.echo "Log on to profile : " & strMapiProfile 

' Get Mailbox
Set objIs = objSession.InfoStores(strMailBox) 
Wscript.echo "Logged on to Mailbox : " & objIs.name

' Get Root folder, inbox and deleted items
Set objFldSource = objIs.RootFolder
'Wscript.echo objFldSource.Folders.Count
For Each objF in objFldSource.Folders
 If objF.Name = "Inbox" Then
  Set objFldSource = objF
 End If
 If objF.Name = "Deleted Items" Then
  Set objFldDestination = objF
 End If
 'WScript.Echo objF.Name
Next

' Loop through messages. 
With objFldSource.Messages
  For Index = .Count To 1 Step -1 
   
   With .Item(Index)
     ' Process the message.
       Set objMessage = objFldSource.Messages(Index) 
 strSubj = objMessage.Subject
 strBody = objMessage.Text
       Wscript.Echo strSubj

 ' Get the original email
 arrLines = Split(strBody,vbCrLf)
 strEmail = ""
 Wscript.echo "Attempting to parse " & strKey 
 For Each strLine in arrLines
 
         If UCase(Left(strLine, Len(strKey) + 1)) = UCase(strKey & ":") Then
           strEmail = Mid(strLine, InStr(strLine, ": ") + 1)
   Wscript.Echo strEmail
           Exit For
         End If
 Next
 UpdateInvalidEmail objConn, strEmail, strSubj

       ' Move the message.
       Call .MoveTo(objFldDestination.ID) 
   End With

  Next
End With

Wscript.Echo "Finised parsing mailbox."

objConn.Close
Set objConn = Nothing

' Uitloggen.
Call objSession.Logoff 
Set objSession = Nothing

Function ReadINI(strINIFile, strSection, strKey)
 Dim objFSO, objTextFile, strLine
 
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objTextFile = objFSO.OpenTextFile(strINIFile)
 
 'loop through each line and check for key value
 Do While Not objTextFile.AtEndOfStream
   strLine = objTextFile.ReadLine
   'wscript.echo strLine
   If UCase(strLine) = UCase("[" & strSection & "]") Then
     Do While Not objTextFile.AtEndOfStream
       strLine = objTextFile.ReadLine
       If UCase(Left(strLine, Len(strKey) + 1)) = _
          UCase(strKey & "=") Then
         ReadINI = Mid(strLine, InStr(strLine, "=") + 1)
         Exit do
       End If
     Loop
     Exit Do
   End If
 Loop
 
 objTextFile.Close
 
End Function

Sub UpdateInvalidEmail(objConn, email, subject)

 If Len(email) > 50 Then email = Left(email, 50)
 If Len(subject) > 500 Then subject = Left(subject, 500)

 email = Replace(email, "'", "''")
 subject = Replace(subject, "'", "''")
 objConn.Execute "sp_UpdateInvalidEmails '" & email & "', '" & subject & "'"

End Sub

MailRead.ini

[Config]
;name of outlook profile to use
MapiProfile=Default Outlook Profile
;Name of mailbox as it appears in outlook
MailBox=Mailbox - Sender Name
;Any special extra headers
MailHeader=x-MyCustomHdr