Wednesday 30 October 2013

Adding SQL Maintenance Cleanup Task

This article is a follow up on Creating a Simple SQL Maintenance Plan where we added a Maintenance Cleanup Task in our maintenance plan. This will show you the steps on how to add a maintenance cleanup task in order to house keep our backup files.


  1. Drag a Maintenance Cleanup Task from toolbox. Double click to edit properties.
  2. In properties window we can set which folder to watch and what is the file extension. We can also set the number of days, weeks, months or years on how long SQL needs to keep the files.
  3. Maintenance Cleanup Task Properties




















  4. Click OK button to save settings.
  5. Sample Maintenance Cleanup Task


















In the above example, the Maintenance Cleanup Task will delete all backup files (with bak as file extension) that are older than 4 weeks from the selected folder in the properties window.




Creating a Simple SQL Maintenance Plan

In this article, I will discuss the steps on how to create a simple maintenance plan in SQL that will backup both database and transaction logs.

Here are the steps to follow:
  1. Right click Maintenance Plan >> Select New Maintenance Plan.
  2. New Maintenance Plan













  3. Drag a Back up Database Task from toolbox.
  4. Back Up Database Task











  5. Double click Back up Database Task to set the properties. Set Backup type to FULL.
  6. Back Up Database Task




















  7. Add another Back Up Database Task and rename it to Back Up Transaction Log Task.
  8. Back Up Database Task




















  9. Double click Backup Transaction Log Task to set properties. Set Backup Type to Transaction Log.
  10. Back Up Transaction Log Task





















In the steps mentioned above, we did not shrink the .ldf file but rather we simply backup the transaction log after full database back up. The effect of backing up the transaction log tells SQL Server that it is safe to reuse the space consumed by the transaction log. So if the size of transaction log at that time is 300 MB, SQL will reuse the 300 MB space to record new transactions instead of consuming more hard disk space.

As a finishing touch you can add a File Maintenance Task to delete the backup files in server. Note: If it is production I hope you back up your database somewhere before you delete the backup in your server.

Maintenance Cleanup Task



Monday 28 October 2013

How To Highlight Current Page Link on Left Navigation Menu

Last time, I encountered an issue in SharePoint site where the current page link on the left navigation menu is not being highlighted. And as a workaround, I just created a script using jQuery based on the html structure and css class of the left navigation menu.

Here is the sample script:

<script type="text/javascript" src="/_layouts/1033/jquery.js"></script>
<script type="text/javascript">

$(document).ready(function() {
var oUrl = window.location.href;
       
$("table .ms-navitem td a").each(function() {
var ohref = $(this).attr("href");
            if(oUrl.indexOf(ohref) != -1)
            {
                var oClass = $(this).parent().parent().parent().parent().attr("class") + " ms-selectednav";    
                $(this).parent().parent().parent().parent().attr("class", oClass);
            }
});
});

</script>


Basically, in order to explore and understand the html structure of the page, I have used an IE Developer Toolbar as shown below.

IE Developer Toolbar


After adding the script in the master page, the current page link will now be highlighted.

Quick Launch Menu




Tuesday 22 October 2013

Attaching Event Handler To A SharePoint List

Attaching an event handler / event receiver to a custom list in SharePoint can be done programmatically. You can create a console application so that every time you require to attach an event handler to a list, you just need to run that console application.

Below is the full code of the sample console application that will attach an event handler to a SharePoint list via code. Basically, the console application only supports the following type of event:

  • ItemAdding - An event that occurs before an item has been added.
  • ItemAdded - An event that occurs after an item has been added.
  • ItemUpdating - An event that occurs before an item is updated.
  • ItemUpdated- An event that occurs after an item has been updated.
  • ItemDeleting - An event that fires before an item is deleted.
  • ItemDeleted - An event that occurs after an item has been deleted.

Also, the console application requires four input parameters as listed below:
  1. Site URL (the full URL of the SharePoint site)
  2.          e.g. http://www.abc.com/log
  3. List Name (the display name of the list)
  4.          e.g. ListName
  5. Event Receivers (The type of event receivers separated by ';')
  6.          e.g. itemadded;itemupdated
  7. Assembly Path (the location path of the assembly)
  8.          e.g. C:\\Sample.dll

When you run the console application, the format should be:

AttachEventHandlerToSPList <site url> <List Name> <Event Receivers> <Assembly Path>

Example:

AttachEventHandlerToSPList http://www.abc.com/log ListName itemadded;itemupdated C:\\Sample.dll




Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using System.Reflection;

namespace AttachEventHandlerToSPList
{
    class Program
    {
        static void Main(string[] args)
        {
            string strClassName = string.Empty;
            string strAssembly = string.Empty;
            try
            {
                if (args.Length != 4)
                {
                    Console.WriteLine("ERROR: Invalid no. of parameters. \n");
                    usage();
                    Environment.Exit(1);
                }


                string siteURL = args[0]; // "http://www.abc.com/sites/DomainRequest"; //
                string listName = args[1]; // "Branch";  //   "Domain Account Request Application";
                string eventReceivers = args[2]; // "itemadded;itemupdated"; //   "itemadding";
                string path = args[3]; // "C:\Documents and Settings\Administrator\Desktop\.net workflow\DomainRequest\DomainRequestEventHandler\bin\Release\DomainRequestEventHandler.dll"
                //AssemblyName assemblyName = AssemblyName.GetAssemblyName(@"C:\Documents and Settings\Administrator\Desktop\.net workflow\DomainRequest\DomainRequestEventHandler\bin\Release\DomainRequestEventHandler.dll");
                Assembly assembly = Assembly.LoadFrom(@path);
                strAssembly = assembly.FullName;
                Type[] types = assembly.GetTypes();
               

                foreach (Type type in types)
                {
                    if (type.BaseType.Name == "SPItemEventReceiver")
                        strClassName = type.FullName;
                }


                if (!string.IsNullOrEmpty(eventReceivers))
                {
                    string[] events = eventReceivers.ToLower().Split(';');

                    int nCnt = events.Count();

                    if (nCnt > 0)
                    {
                        using (SPSite site = new SPSite(siteURL))
                        {

                            using (SPWeb web = site.OpenWeb())
                            {
                                SPList list = web.Lists[listName];

                                foreach (string eventType in events)
                                {
                                    DeleteEventHandler(list, eventType, strClassName);

                                    switch (eventType)
                                    {
                                        case "itemadding":
                                            {
                                                list.EventReceivers.Add(SPEventReceiverType.ItemAdding,
                                                    strAssembly,
                                                    strClassName);
                                                list.Update();

                                                Console.WriteLine(string.Format("EventHandler (ItemAdding) has been added correctly in {0}!", listName));
                                                break;
                                            }
                                        case "itemadded":
                                            {
                                                list.EventReceivers.Add(SPEventReceiverType.ItemAdded,
                                                    strAssembly,
                                                    strClassName);
                                                list.Update();

                                                Console.WriteLine(string.Format("EventHandler (ItemAdded) has been added correctly in {0}!", listName));
                                                break;
                                            }
                                        case "itemupdating":
                                            {
                                                list.EventReceivers.Add(SPEventReceiverType.ItemUpdating,
                                                    strAssembly,
                                                    strClassName);
                                                list.Update();

                                                Console.WriteLine(string.Format("EventHandler (ItemUpdating) has been added correctly in {0}!", listName));
                                                break;
                                            }
                                        case "itemupdated":
                                            {
                                                list.EventReceivers.Add(SPEventReceiverType.ItemUpdated,
                                                    strAssembly,
                                                    strClassName);
                                                list.Update();

                                                Console.WriteLine(string.Format("EventHandler (ItemUpdated) has been added correctly in {0}!", listName));
                                                break;
                                            }
                                        case "itemdeleting":
                                            {
                                                list.EventReceivers.Add(SPEventReceiverType.ItemDeleting,
                                                    strAssembly,
                                                    strClassName);
                                                list.Update();

                                                Console.WriteLine(string.Format("EventHandler (ItemDeleting) has been added correctly in {0}!", listName));
                                                break;
                                            }
                                        case "itemdeleted":
                                            {
                                                list.EventReceivers.Add(SPEventReceiverType.ItemDeleted,
                                                    strAssembly,
                                                    strClassName);
                                                list.Update();

                                                Console.WriteLine(string.Format("EventHandler (ItemDeleted) has been added correctly in {0}!", listName));
                                                break;
                                            }
                                    }


                                }
                            }

                        }
                      
                    }
                    else
                    {
                        Console.WriteLine("Please input EventReceivers Type (separated by ';' if multiple)");
                    }
                }
            }

            catch (Exception ex)
            {

                Console.WriteLine("Error! stack:" + ex.StackTrace);

                Console.WriteLine("Error! message:" + ex.StackTrace);

                Console.WriteLine("press any key to exit ...");

                Console.Read();

            }

        }


        static void DeleteEventHandler(SPList splist, string eventtype, string strClassNme)
        {
            SPEventReceiverType type = (SPEventReceiverType)Enum.Parse(typeof(SPEventReceiverType), eventtype, true);

            for (int i = 0; i < splist.EventReceivers.Count; i++)
            {
                if (splist.EventReceivers[i].Class == strClassNme)
                {
                    if (splist.EventReceivers[i].Type == type)
                    {
                        splist.EventReceivers[i].Delete();
                        splist.Update();
                        Console.WriteLine(eventtype + " Event deleted to " + splist.Title + " list.");
                    }
                }               
            }
        }


        public static void usage()
        {
            Console.WriteLine("Format: AttachEventHandlerToSPList <site url> <List Name> <Event Receivers> <Assembly Path> \n" +
                              "<site url>             -  The sharepoint site url \n" +
                              "<List Name>            -  The display name of the list \n" +
                              "<Event Receivers>      -  The type of event receivers \n" +
                              "                          separated by ';'. (e.g. itemadded;itemupdated) \n" +
                              "<Assembly Path>        -  The location path of the assembly.");

            Console.WriteLine("e.g. Attach the eventhandler in the List: \n" +
                              "AttachEventHandlerToSPList http://www.abc.com/log ListName itemadded;itemupdated C:\\Sample.dll ");
        }
    }
}




Wednesday 9 October 2013

Set Value To A Lookup Field By Using Name

The following example shows how to set value to a SharePoint lookup field by using name.

The example method or function accepts two parameters. The first one is SPList which is equal to your lookup list and the second one is a string which is equal to the name of your lookup item. This method will find the item from the lookup list by using Name. If the item is found, it will be used in creating SPFieldLookupValue category using the item id & item title. SPFieldLookupValue category will then be returned by the method and it can be used now as a value of the lookup field.



static SPFieldLookupValue GetCategory(SPList list, string lookupitemName)
    {
        SPFieldLookupValue category = null;
        //get id from Categories List
        for (int ItemNum = 0; ItemNum < list.Items.Count; ItemNum++)
        {
            SPListItem listItem = list.Items[ItemNum];

            if (Convert.ToString(listItem["Title"]).Trim().ToLower() == lookupitemName.Trim().ToLower())
            {
                category = new SPFieldLookupValue(listItem.ID, listItem.Title);
                break;
            }
        }

        return category;

    }


Here is the sample code on how to use the above method:

listitem["Category"] = GetCategory(CategoriesList, "Admin Matters");




Monday 7 October 2013

Provision SharePoint Central Administration

The SharePoint Central Administration site is use to do administration tasks for SharePoint Products and Technologies from a central location. You can provision and unprovision it by using the psconfig.exe command-line tool with the adminvs command. Provisioning creates a new SharePoint Central Administration Web application and an application pool running under the server farm administrator's account, whereas unprovisioning removes the SharePoint Central Administration Web application and its application pool.

Below are example commands to manage the SharePoint Central Administration web application on the local computer:

>> create new central administration (provision)

psconfig.exe -cmd adminvs -provision -port 8080 -windowsauthprovider onlyusentlm


>> delete central administration (unprovision)

psconfig.exe -cmd adminvs -unprovision


For more details on how to use psconfig.exe commands, please check this article: http://technet.microsoft.com/en-us/library/cc263093(office.12).aspx




Customize Post.aspx Without Error

In order to customize SharePoint blog's Post.aspx page, you need to convert it into XSLT Data View using SharePoint designer. But, the problem is when you try to convert it, the below error occurs:

"Failed setting processor stylesheet: Expression expected. -->=<--@Author"


Solution:

  1. In your SharePoint blog site, go to Post.aspx.
  2. Click Site Actions >> Edit Page
  3. Delete Post webpart
  4. Add new Post webpart.
  5. Exit edit mode.
  6. Open Post.aspx using Sharepoint Designer
  7. Right-click Post webpart and then convert to XSLT Data View. (You should now be able to convert it without error)
  8. In Common Data View Tasks >> Parameters >> add new parameter ID (Parameter source = Query String; Query String Variable = ID)
  9. In Common Data View Tasks >> Filter >> add filter ID Equals [ID]. 

After that, you can now edit the default display page of a SharePoint blog site based on your requirements.




Saturday 5 October 2013

Restore Web Application By Using Content Database Backup

Below are the steps on how to restore a SharePoint web application by using the content database backup. (e.g. Testing2 is the name of content database backup)

  1. Go to the SharePoint central administration and create new web application with the same name & database name that you will restore.
  2. In Application Management >> Content databases, select your newly created web application and then delete the newly created content db (remove content database)
  3. In Sql Server, restore your content database backup. If there's an error saying that it's currently in use, just wait for 3 to 5 mins and then try again.
  4. In Application Management >> Content databases, add a content database. Set the database name equal to the restored content db in step 3. (for this case, it's Testing2)



Get User Profile Picture URL Using JQuery

I encountered an issue while working on a discussion board in SharePoint 2007. The issue is the user's profile picture is not displaying in the flat view of the discussion board even though there is a picture associated with the user profile. Instead the picture from this url "_layouts/images/person.gif" is displaying. So in order to fix this issue, I just used jQuery.

Here is the sample script on how to get the user profile picture url through jQuery:

function spjs_QueryPicURL(itm){
var pic=null;

var query = "<Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+itm+"</Value></Eq></Where></Query>";
$().SPServices({
    operation: "GetListItems",  
    async: false,
    listName: "6ccdab10-1068-4d14-8f72-2a55f02938da",
CAMLQuery: query,
    CAMLViewFields: "<ViewFields><FieldRef Name='Picture' /></ViewFields>",  
    completefunc: function (xData, Status) {    
      $(xData.responseXML).find("[nodeName='z:row']").each(function() {
        pic = $(this).attr("ows_Picture");
      });
    }
  });
 
if(pic == 'undefined')
pic=null;

return pic;
}


**** Note:

listName is the list ID of User Information List

****************


Thursday 3 October 2013

Remove Workflow Column from SharePoint List View

When you attach a workflow to a SharePoint list, the workflow column will be added automatically to the default view of the list. This column shows the status of the workflow and when you click on the status, you will be redirected to the Workflow Status page. Usually, this information is of no practical use for end users therefore you don't want this column to appear in the list view. So in order to achieve it, you need to write a custom code that will remove the workflow column programmatically.


Below is the sample code of a console application that removes workflow column from the default view of the SharePoint list:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace RemoveWorkFlowColumnFromSPView
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length != 4)
            {
                Console.WriteLine("Please input parameters.");
                Console.WriteLine("Format: <site url> <list name> <view name> <workflow template name>");
                return;
            }

            string workflowColName = string.Empty;
            string siteUrl = args[0]; // "http://www.vpc.com/sites/PFW"; //
            string listName = args[1]; // "Perimeter Firewall Request"; //
            string viewName = args[2]; // "All Requests"; //
            string workflowName = args[3]; // "FirewallRequestWorkFlow"; //


            using (SPSite site = new SPSite(siteUrl))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    web.AllowUnsafeUpdates = true;

                    SPView objView = web.Lists[listName].Views[viewName];

                    //static name of the workflow column is equal to 8_character_name_of_workflow_column,
                    // without space and is case sensitive.
                    workflowColName = workflowName.Substring(0, 8);

                    if (objView.ViewFields.Exists(workflowColName))
                    {
                        objView.ViewFields.Delete(workflowColName);
                        objView.Update();

                        Console.WriteLine(string.Format("{0} column: {1} has been removed successfully from the {2} view",
                            workflowName,
                            workflowColName,
                            viewName));
                    }

                   

                    web.AllowUnsafeUpdates = false;

                   
                }
            }

           

        }
    }
}