Friday, December 28, 2007

Things to take note about SharePoint master pages

Stumbled across this page that note down some useful information about master page in MOSS 2007. Definitely will refer to it once a while when I’m doing customization on MOSS pages.

Custom page in "_layouts" that switches master page depending on site context

Ok, that’s a long title. Let me explain what I’m trying to do here. I have a custom ASP.NET 2.0 pages deployed on the “_layouts” folder. What I want to do is have the master page setting of these custom pages to change according to the SharePoint site context.

For example, viewing http://server/SiteA/_layouts/custom.aspx will use materpage from http://server/SiteA/_catalogs/masterpage/default.master, while viewing http://server/SiteB/_layouts/custom.aspx will use materpage from http://server/SiteB/_catalogs/masterpage/default.master.

Lots of information are already on the web, such as, and The most promising method mentioned are by building a custom httpModule class which can be applied to the whole MOSS web application.

Below is my simplistic take that fits my scenario:


<%@ Page Language="C#" MasterPageFile="~/_layouts/default.master" AutoEventWireup="true" CodeBehind="CustomPage.aspx.cs" Inherits="CustomMaster. CustomPage" %>

<asp:Content ContentPlaceHolderId="PlaceHolderPageTitle" runat="server">


<asp:Content ContentPlaceHolderId="PlaceHolderPageTitleInTitleArea" runat="server">


<asp:Content contentplaceholderid="PlaceHolderAdditionalPageHead" runat="server">


<asp:Content ContentPlaceHolderId="PlaceHolderMain" runat="server">



public partial class CustomPage : System.Web.UI.Page


protected void Page_PreInit(object sender, EventArgs e)


Page page = sender as Page;

if (page != null)


if (page.MasterPageFile != null)


SPWeb mySite = SPContext.Current.Web;

page.MasterPageFile = mySite.CustomMasterUrl.ToString();





Now, the master page of my CustomPage will change dynamically depending on the site context. All I need to do now is to make this a base class for my custom application to inherit, and all the pages will have this behavior.

Wednesday, November 14, 2007

Sorting project items under SSIS projects

For some reason, SSIS projects in VS.NET 2005 will not sort project files, such as dtsx in alphabetical order. As far as I know, there are no built-in function in VS.NET 2005 to sort the items.

Therefore, the workaround is to open the SSIS project file and sort it manually. The SSIS project file is a valid xml document file. This means I can manipulate it in Excel, such as sorting it. However, I’ve opened the project file in notepad and copied part of the xml to a separate xml file to open in Excel, just in case Excel did more than what it should. Make sure the created xml document conform to xml standard.

After opening the xml file in Excel as xml table, perform sorting of the document in whichever order desired (usually A to Z). Once done, save the file, open the file in notepad and copy the changes and replace the project item section of the project file. Now the project items should be sorted accordingly. Do make sure backup is done before attempting this change.

Thursday, November 1, 2007

Detecting long running SQL Agent jobs

Typically, you would like SQL Agent jobs to send error notification if it failed for some reason. If there’s no email, the job “should” be running ok, right?


Actually, one of the SQL Agent job that I’ve observed actually seems to be stucked half way through in one of the step that executes multiple SSIS packages. One of the package are extracting data from another SQL server via link server. For some reason, the job will not fail even if there’s some connection issue with the link server. The job does not fail in this case, thus no email notification will be sent.


Therefore, I have to check which SQL Agent job has run for too long. Fortunately, this has been done by many others. The trick is to make use of some SQL server built-in stored proc to query for the job status and its start time. Compare the start time with current time, coupled with the execution status yields you the long running jobs. The following are the reference that I used to built my own long running job detector.


To test out the stroed proc, I’ve created a job that executes the sql statement “waitfor delay '1:00:00'” to simulate a job that runs forever.

Thursday, October 18, 2007

Custom Application on top of MOSS 2007

One of the old school method of building build custom web application on top of SharePoint Portal is to deploy the web application within SharePoint’s web application. Most oftenly, the _layouts folder is used. I do have to note that this might not be the best way to do it, but it’s the most straight forward way.

With CMS now part of SharePoint in MOSS 2007, there is an additional step required: the PublishingHttpModule need to be explicitly removed from the _layouts folder. If this is not done so, the custom application will not work properly. The web.config file in the wss virtual directory need to include the following settings within the <configuration> section:


<location path="_layouts">



<remove name="PublishingHttpModule" />





SideNote: I’ve almost forgotten about this setting until a colleague of mine bumped into this same issue, which already happened to me earlier this year when I haven’t started blogging yet. I would credit this finding to some resource on the web but unfortunately I don’t recall where I found this info from already. Anyway, belated thanx, whoever your are...

Cannot acquire connection to Excel when executing SSIS package via SQL Agent Job

My current work involves building ETL solution using Microsoft SQL Server SSIS. All has been working well in development until we moved the SSIS packages into production environment (don’t that always happen?).

Anyway, our SSIS packages that has Excel file as data source hits the following problem:

Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009

Eventually, it seems that the issue is due to account privilege. Our packages are executed via SQL Agent Job, in which the job step is configured to Run as a one of the designated AD account, that was setup as the proxy for the job. According to this Microsoft kb article , the particular AD account does not have rights to the Temp folder of SQL Server Agent proxy account. It would be logical to assume that this temp folder is used as a working folder by the SQL Agent job if required, such as putting the Excel file there for execution perhaps?

We solve this issue by using the default Run as account, which is SQL Agent Service Account. This Microsoft KB article has the detail:

Friday, May 11, 2007

SPS2003 "Add Link To Site" page stops responding

A client of mine is having a problem provisioning new team sites in their SPS2003 intranet portal. After some investigation, the problem is caused by the freezing on the 2nd page during site provisioning, the "Add Link To Site" page that adds a list item in the the Sites list in the Sites area. This page is important as it's the only place with a link from the portal to the team sites. Microsoft KB article 934229 states that it is a known issue and at this time is researching for a resolution. For the time being, the hanged provisioning process of the site can still continued by navigating to the site itself (http://portal/sites/newwsssite). From there, choose a site template and click OK, and the site is created. However, no link is provided in the Sites list. Hope MS comes up with a fix ASAP.

Tuesday, May 8, 2007

Enabling "Send to Records Center"

I've just discovered that MOSS 2007 has built-in functionality for Records Management. To know more about Records Management, refer to Microsoft Records Management Team Blog as they provide plenty of information on the 5W of Records Management with MOSS 2007. While trying it out myself on my MOSS 2007 sandbox, I am not able to get the feature where a user can send a document from anywhere to the Records Center. Apparently some setup is required before you get the Send To --> Records Center link in the document library item's context-sensitive drop down menu. First off, you need to create the Records Center site collection, which will provide you the basic Record Center stuff such as the Unclassified Records doclib and a few lists for routing and holding. To enable the "Send To" link, you need to specify "where" is the Records Center. This is done at the Central Admin --> Application Management --> External Service Connections --> Records Center. Enter the location of the Records Center web services asmx location. (an example is shown to point to http://server/portal/_vti_bin/officialfile.asmx) Enter the Display name to be used on the "Send To" link here as well. Click OK to save the setting. No iisreset is required. Now try it on any documents in doclib, you should get the "Send to" link now. For now, any documents being sent to Records Center site collection should be routed to the one and only doclib, which is the Unclassified Records. Hopefully I can find some more time to experiment with the routing and holding list soon, and see how all these jells together to give some cool records management functionality. Reference: Information on Records Management: On how to enable "Send To" link: On planning Records Management:

Friday, April 13, 2007

Enabling Ajax in MOSS 2007

Ajax greatly enhance any web application by reducing postback of ASP.NET pages. This instantly gives better user experience with the Ajax-enabled web application. Best of all, implementing it is fairly straight forward. That is, if SharePoint 2007 is not in the picture.

If you're building a ajax web application that sits on top of MOSS 2007, there's a couple of things you need to set:

If your custom app uses any of the SharePoint master page (such as default.master), ensure the wss user control Welcome is removed:

<wssuc:welcome id="IdWelcome" runat="server" enableviewstate="false"></wssuc:Welcome> This affects Ajax postbacks. Removing it will partially fix the problem. One-time ajax postback works, subsequently, it will fail. Secondly, the other change required is at LAYOUTS\1033\init.js file 1. Look for javascript function _spFormOnSubmitWrapper. 2. Under “if (_spFormOnSubmitCalled)” code block, remove/comment the “return false;” code. I'm not sure whether making this change would affect SharePoint, but so far seems ok. Do use this at your own risk though. Hopefully there's a fix for this in SharePoint soon so that Ajax will work without this kind of hack.

Reference made to Vincent Rothwell's blog:

Tuesday, March 27, 2007

Taking Advantage of SharePoint Session State Management

Knowing that the custom web application (which sits on top of SharePoint) that I am developing will be hosted by two web servers for high availability, I have designated a less-loaded back-end server as the State Server to host the session state of both web servers. Having gone through all the ropes on how to setup the State Server, at the end I found out that actually, nothing needs to be done. SharePoint 2007's Shared Services Provider has configured the web frontend to make use of SQL Server for state management. By default, state management using SQL Server is turned on when SSP is configured. It still can be turned on/off at SharePoint 3.0 Central Administration --> Application Management --> Office SharePoint Servers Shared Services --> Configure session state. A quick test shows that the session variables stored by the custom web application were indeed stored and retrieved successfully in between requests from different web servers. I guess that is another plus point for developing application on top of SharePoint. More information about MOSS state at

Sunday, March 18, 2007

Be cautious of web.config settings during deployments

A colleague of mine just solved a SharePoint performance issue that pondered our project team for days. The discovery was by accident and the cause is quite classic. I decided to put in as much detail as possible to make this an interesting read. And the story goes... We are building a custom web application that sits on top of SharePoint 2007. Not having enough servers for different phases of the development, a box is allocated for two purpose, training and SIT. Training was installed on the host machine, while SIT is installed as a virtual server on the same box. All went well until suddenly the SIT SharePoint pages were loading at crawling speed. What's funny was that shutting down the SIT would cause the Training instance to slow down as well, but only at MySite. Eventually, the infra team configured their ISA and DNS settings and both servers are working normally again. Weeks gone by until the issue resurfaces when SIT were shutdown again and causes the Training environment to crawling speed. Wouldn't want to bring impact to training, we started back SIT immediately, and decided to look into it later when we have the time (That never happen). By chance, a colleague of mine were setting up a VPC version of the SharePoint application and faces the same performance issue on the VPC. Again, only on happens to MySite. After going through all the web.config settings, making the changes that seems logical, MySite is running at top speed again. What actually causes the problem is due to the Enterprice Library configuration in web.config. One of the parameter of the configuration settings were machineName, which is set to the machine name of the SIT server. This means when the application tries to write logs, it tries to write to the SIT server's Application log. MySite is where enterprise library's logging were used heavily. This explains why:
  1. Infra team has to configure ISA and DNS pointing properly so that Training server can write event logs to SIT server. If training server can't find SIT server to write log, it waits until timeout occurs.
  2. SIT server shutdown will cause slow down in Training server.
  3. VPC version is slow because it was seperated from the LAN and has no access to SIT server.

The moral of the story So why does this happen in the first place? Often we copy web.config for deployment instead of making manual changes to the target environment's web.config files. Copying the settings from SIT server to Training server without making all the necessary changes is the root of this issue.

Therefore, one should go through the whole web.config settings throughly to ensure that the settings are meant for the target deployment environment.

Ajax Bandwidth Overhead

Although Ajax reduces the postback size for ASP.NET applications, it does have a little drawback. Ajax rely on a suite of javascript library to perform its magic. Those javascript files such as ScriptResource.axd and WebResource.axd needs to be downloaded to the client browser. First I would thought, web browsers would cache these files. So the users would only need to download these files once only, right? A close inspection to the requests sent to the servers for these files show that they were requested with query strings at the back e.g. .../WebResource.axd?d=2KPNGySHzRkl024_lWT1Qg2&t=633052587689473359. Usually browsers will not cache such files. The more Ajax features you implement into your ASP.NET application, more js files need to be downloaded. However, the size of these javascript files can be reduced by:
  1. Ensure debug mode is set to off in the web.config. For my case, the reduction of the *.axd files went from over 300+kb to 128kb. That's 60% size reduction.
  2. Making use of IIS compression. Although I suspect this wouldn't help much.

Therefore, one should consider this size factor when implementing Ajax into their web application. Some similar functionalities still can be achieved without using Ajax.

Saturday, March 17, 2007

Building Ajax-enabled custom web application on top of SharePoint

Ajax seems to be the in-thing for any web application nowadays. It is easy to implement into to existing ASP.NET application and bring huge improvement in the total user experience of a web application.

However, building an Ajax-enabled custom web application on top of SharePoint requires some tweaking before you get the wow effect of Ajax.

  1. If the Ajax-enabled custom application uses the SharePoint master pages, such as default.master master page, remove this SharePoint tag from it: <wssuc:Welcome id="IdWelcome" runat="server" EnableViewState="false"></wssuc:Welcome>. This affects Ajax postbacks. Removing it will partially fix the problem, e.g. one Ajax postback can be made, but subsequently no postbacks.
  2. Open LAYOUTS\1033\init.js file and look for javascript function _spFormOnSubmitWrapper. Under “if (_spFormOnSubmitCalled)” code block, remove/comment the “return false;” code. Not sure what is the effect of removing this bit though thus do it on your own risk!

Alternatively, the javascript function _spFormOnSubmitWrapper can be overwritten only on the pages that uses Ajax. This can be done by puting the modified version of the js function to a seperate .js file and reference it right after the line where reference to init.js were made.

Thanx to the post from Vincent Rothwell and comment by Phillip Lanier.

Turn on SharePoint error messages

Building application on top of SharePoint would be easier if exceptions thrown from the custom application are shown on the SharePoint error page. By default, SharePoint only shows the generic error message, "Unknown error occured". It does not show the cause of the error and it is difficult to debug the custom application.

Fortunately, found a posting from Shane Perran and Andrew Connell that shows how to give more sense to the errors thrown by modifying the default SharePoint web.config file:

  • Turn the call stack on by searching for the SafeMode element and set CallStack="true".
  • Turn off the SharePoint error pages by turning off custom error pages: search for customErrors tag and set mode="Off".
  • And also, search compilation tag and set debug="true".

Wednesday, March 7, 2007

SQL Server login failed due to "The password of the account must be changed"

After deploying an ASP.NET application to the production server, I encoutnered this error: Login failed for user 'db_user'. Reason: The password of the account must be changed. The application is using SQL user authentication to logon to SQL server. Therefore, a close inspection on the user account is warranted. It appears that the application user account has set the Enforce password policy, Enforce password expiration and User must change password at next login were turned on. Therefore the password must be modified before the application could access the database. I've got hold of the the DBA login to logon to the database server, and try to remove the checks mentioned above on the application user account, but an error says MUST_CHANGE constraint, and the checks cannot be removed. Therefore, I login into the SQL Server again using the application user account in question, and then I get a prompt to modify the password. Once the password is changed, the checks can be removed, and I change the password back to the previous password that was meant to be used for production. That seems to solve the problem as the application is able to load the data drom DB without error.