Tuesday, December 9, 2014

A temporary SQL table

Sometimes we might require to have a temporary SQL table without physically creating it in database.


Following is a very simple example.

SELECT 0 AS ID, 'Fruits' AS Name UNION ALL
SELECT 1 AS ID, 'Vegetables' AS Name UNION ALL
SELECT 2 AS ID, 'Exercise' AS Name UNION ALL
SELECT 3 AS ID, 'Meditation' AS Name UNION ALL
SELECT 4 AS ID, 'Positive Thinking' AS Name UNION ALL
SELECT 5 AS ID, 'Enough Sleep' AS Name


I once needed it in SSAS Data Source View to define Target and Red-Line for a cube measure. 

 Cheers :)

Monday, June 10, 2013

Undo Visual Upgrade in SharePoint 2010

Hi friends, you might have performed visual upgrade in SharePoint 2010 after upgrading your SharePoint farm from MOSS 2007. If you want to revert the user interface back to MOSS 2007 style, there isn't any link in Site Settings as you did visual upgrade. To do this for one web site, you can run PowerShell command below.


  1.  Go to Start menu, click All Programs.



  2. Click Microsoft SharePoint 2010 Products.



  3. Click SharePoint 2010 Management Shell.



  4. At the Windows PowerShell command prompt, type the following command:



$Web=Get-SPWeb http://yoururl/yourweb
$Web.UIVersion=3
$Web.UIVersionConfigurationEnabled=$true
$Web.update()
I will be posting further about this topic.
Thanks

To revert a specific site in a site collection to the previous UI, at the Windows PowerShell command prompt, type the following command:

Get-SPSite http://machinename/sites/V3UI | Get-SPWeb "webname" | Foreach{$_.UIVersionConfigurationEnabled=1;$_.UIVersion=3;$_.Update();}

To reverts all sites in a site collection to the previous user interface, at the Windows PowerShell command prompt, type the following command:

Get-SPSite http://machinename/sites/V3UI | Foreach{$_.UIVersionConfigurationEnabled=1;$_.UIVersion=3;$_.Update();}

To force with Visual Upgrade

$webapp = Get-SPWebApplication http://sitename
foreach ($s in $webapp.sites)
{$s.VisualUpgradeWebs() }



To view status of current user interface by using Windows PowerShell



$sc = Get-SPSite http://machinename/sites/collectionname; $sc.GetVisualReport() | Format-Table

Installing Moodel 2.1


Install MOODLE

1.       Go to http://moodle.org/downloadsand download Moodle packages for Windows.
2.       Extract the folder to a convenient hard drive location.  E.g. C:\MOODLE
3.       PHP has limited its maximum file upload to 8MB by default. First, we have to alter this value.
Go to location C:\MOODLE\server\php\php.ini
4.       Open it with any text editor and search for post_max_size
5.       Its value could be post_max_size = 8M, replace 8M with 128M. If it is already 128M, then no need to change.
Now we can upload files upto 128 Mega Bytes.
6.       Start the moodle by going to C:\Moodle\Start Moodle.exe
7.       If we need to stop moodle, we should click Stop Moodle.exe rather than hitting Ctrl + C
8.       If there is a problem starting XAMPP server, the most probable reason is port conflict. XAMPP server uses port 80by default and port 80 could be occupied by some other programs like IIS, Skype etc. So we need to change port number for the XAMPP.
For this, go to location C:\MOODLE\server\apache\confand edit file httpd.conf
9.       In http.conf file search for line #Listen 
You can see values like
#Listen 0.0.0.0:80
#Listen [::]:80
Listen 80
I replaced 80s by 9000.
This replaced my problem.
10.   Now start moodle.
11.   Go to browser and type http://localhostor http://127.0.0.1
12.   Installation page appears. Click next, next until you reach Database settings.
13.   Database user is root by default and we can enter password or leave it blank and continue.
14.   Moodle will check minimum requirements and then click Next and Continue.
15.   When admin settings page  come, deault user name is admin. Enter password, enter First Name, enter LastName, Email,  City and Country. These are require fields. While entering Email, email should be chosen which will not be used. Because if this setting page takes email, it can not be used later for another user. 
16.   Click Update Profile.
17.   Give Full site name, short name for stite and front page description in front page settings.
18.   Click Save Changes.
19.   The admin user is automaticall logged in and moodle site is running. J




MySQL Database
MySQL installation is located in following folder.
C:\MOODLE\server\mysql\bin>
To run mysql,
1.       Go to command prompt
2.       Set path in environment variable or go inside directory C:\MOODLE\server\mysql\bin>
3.       Type mysql takes us to mysql command without anyone user logged in
4.       Type exit;to exit through mysql command. Use of semicolon is mandatory.
5.       Type mysql –u root to login to mysql command line user as a root user. There are two root users in mysql database. Initially root user does not have any password, so it is possible to login without password.
6.       To set the password for root user, type
Update mysql.user  set password = PASSWORD(‘p@ssw0rd’) where user = ‘root’;

Here, mysql is the default database, user is the table, password is a column, PASSWORD() is a function that encrypts the password inputted.
7.       To refresh the changes in database i.e. reread the database, type
Flush privileges;
Exit;
8.       Login through root user with password ‘p@ssw0rd’, type
Mysql –u root –p (- means give option for password)
Enter the password
9.       Create two users
Create user web@localhost;[@localhost means they are valid in only local host and it will not work in network outside this localhost]
Create user admin@localhost;
10.   Users are created without priviledges. To grant privileges , type
Grant select, insert, update, delete, create, drop, file, index, alter, create temporary tables, create view, show view, create routine, alter routine, execute on *.* to web@localhost;
That means web user has these privileges on all database
11.   To grant all privilege to admin with grant option , type
Grant all on *.* to admin@localhost with grant option;
12.   Now, we need to set password for these two users, type
Update mysql.user set password = PASSWORD(‘p@ssw0rd’) where user  =  ‘web’;
Update mysql.user set password = PASSWORD(‘p@ssw0rd’) where user  = ‘admin’;
13.   Reread the database and exit
Flush privileges;
Exit;


Try to login using both users.
14.   Mysql –u web –p and give password.. then exit when done (for web user)
15.   Mysql  -u admin –p and give password.. then exit when done  (for admin user)

phpMyAdmin - Tutorial

The moddle  is installed in C drive inside folder C:\Moodle\Server.
To manage mysql database of moodle, we need to install phpMyAdmin tool.
1.       Download  phpMyAdmin from http://www.phpmyadmin.net/home_page/downloads.php
2.       Select  zipped verison of English only, it should automatically download.
3.       Then extract all the contents of the zipped folder to location
C:\MOODLE\server\moodle\admin\phpMyAdmin
4.       phpMyAdmin can be run only via web browser.  Enter following address
In our case, it is
6.       Try credential root and p@ssw0rd. It should give error, because phpMyAdmin still does not know these credentials of mysql.
So go to location C:\MOODLE\server\moodle\admin\phpMyAdmin
and edit the file config.inc.php
7.       Under  the section /*Authentication type and info*/
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';
This should clear the login error. We are ready to control database via phpmyadmin
8.       Try loggin again to phpmyadmin. J
9.       Point to remember: the root password in both moodle’s config.php and phpmyadmin’s confic.inc.php should be same. In our case, both passwords are p@ssw0rd









Thursday, June 6, 2013

How to Cusomize Alert Email for SharePoint 2013

Hi friends, sometimes we may want to customize email template that is sent out of the box by SharePoint  to the alert subscribers. You may want to apply some styling, add extra information about your company or add some extra URLs. One of the scenario when I required to do this was when I have Alternate Access Mapping configured for my SharePoint web application.

Lets us consider a scenario as shown below for my SharePoint 2013 web application.

Internal Web Application URLhttp://sp-realm/
Public Web Application URLhttps://access.sharepointrealm.com
Internal Website URL of Document Libraryhttp://sp-realm/Tutorials
Document LibraryResults
Internal URL of Document Libraryhttp://sp-realm/Tutorials/Results

Below is the snapshot of an email sent by Out Of the by SharePoint.




The email contains several information as shown below:

  1. Website Title: Tutorials
  2. Website URL: http://sp-realm/Tutorials
  3. Modify my alert settings URL: http://sp-realm/Tutorials/_layouts/15/mysubs.aspx
  4. Document Library Item Name: Hello World.txt
  5. View Item URL:  http://sp-realm/Tutorials/Results/helloworld.txt
  6. View Library URL:  http://sp-realm/Tutorials/Results
  7. Event Type: Added
All the URLs provided are internal URLs and will work fine when you are inside your company intranet. What if you want to access the same thing from the public internet provided that you have Alternate Access Mapping configured properly. You may want these URLs to be as under.

  1. Website Title: Tutorials
  2. Website URL: https://access.sharepointrealm.com/Tutorials
  3. Modify my alert settings URL:https://access.sharepointrealm.com/Tutorials/_layouts/15/mysubs.aspx
  4. Document Library Item Name: Hello World.txt
  5. View Item URL: https://access.sharepointrealm.com/Tutorials/Results/helloworld.txt
  6. View Library URL: https://access.sharepointrealm.com/Tutorials/Results
  7. Event Type: Added

The process goes as
  1. Create and deploy a SharePoint Project 
  2. Create an ALert Template
  3. Create a text file to store Public URL.
  4. Notify the SharePoint environment to use new Alert Template. 

1. Create and deploy a SharePoint Project

  1. Open Visual Studio 2012 and create a new project. The project template we select here is "SharePoint 2013 - Empty Project". Lets name the solution and project as "SampleAlertHandler" and click OK. 
  2. We will deploy this solution as a farm solution. So lets give internal URL of the SharePoint web application and choose "Deploy as a farm solution" and click Finish button.
  3. Lets add a new class file and name it "CustomAlertHandler.cs". Open the CustomAlertHandler.cs and replace the code with the following code snippet. using System;
    using System.Text;
    using System.Web;
    using System.Collections.Generic;
    using Microsoft.SharePoint;
    using Microsoft.SharePoint.Utilities;
    using System.IO;

    namespace SampleAlertHandler
    {
        public class CustomAlertHandler : IAlertNotifyHandler
        {
            public bool OnNotification(SPAlertHandlerParams ahp)
            {
                try
                {
                    using (SPSite site = new SPSite(ahp.siteUrl + ahp.webUrl))
                    {
                        //Open the Web
                        using (SPWeb web = site.OpenWeb())
                        {
                            //Get Current Site
                            string webName = web.Name;


                            //Get the list that has been modified
                            SPList list = web.Lists[ahp.a.ListID];

                            //get the item that has been changed
                            SPListItem item = list.GetItemById(ahp.eventData[0].itemId);

                            //path to reach to that item
                            //Lets try to make the Site URL Configurable
                            //We will read the site URL from a text file for external URL, otherwise it will be the interal URL
                            string siteURL = String.Empty;
                            
                            try
                            {
                                siteURL = File.ReadAllText(@"C:\SiteURL\SiteURL.txt");
                                if (siteURL == String.Empty)
                                    siteURL = SPHttpUtility.UrlPathEncode(ahp.siteUrl, true);
                            }
                            catch (Exception ex)
                            {
                               siteURL = SPHttpUtility.UrlPathEncode(ahp.siteUrl, true);
                            }

                            string FullPath = SPHttpUtility.UrlPathEncode(siteURL + ahp.webUrl + "/" + list.Title + "/" + item.Name, true);
                            //path to reach to that list
                            string ListPath = SPHttpUtility.UrlPathEncode(siteURL + "/" + ahp.webUrl + "/" + list.Title, true);
                            //path to reach to that web
                            string webPath = SPHttpUtility.UrlPathEncode(siteURL + "/" + ahp.webUrl, true);

                            
                                                    
                            string to = ahp.headers["to"].ToString();
                            string subject = list.Title.ToString();
                            string body = string.Empty;
                            
                            //Determine what type of event has occured
                            string EventType = "";

                            var eventType = (SPEventType)ahp.eventData[0].eventType;
                            if (eventType == SPEventType.Add)
                            {
                                EventType = "Added";
                            }
                            else if (eventType == SPEventType.Modify)
                            {
                                EventType = "Changed";
                            }
                            else if (eventType == SPEventType.Delete)
                            {
                                EventType = "Deleted";
                            }

                            //this way you can build your email body
                            //also you can apply the bussiness logic that which field to show
                            //change your highlighted words every thing you can do                                             

                            body =
                               "<style type=\"text/css\">" +
                               ".ItemTitle { font-size: larger; font-weight: bold; font-style: italic}" +
                               ".EventType { font-size: larger; }" +
                               ".SmallFonts {font-size: smaller; }" +
                               "</style>";

                            body +=
                                 "<div>" +
                                 "<a class=\"SmallFonts\" href=" + webPath + ">" + webName + "</a><br/>" +
                                 "<div><span class=\"ItemTitle\">" + item.Name.ToString() + "</span><span class=\"EventType\"> has been added</span></div>" +
                                 "<div>" +
                                 "<a class=\"SmallFonts\" href=" + webPath + "/_layouts/mysubs.aspx>Modify my alert settings</a> | " +
                                 "<a class=\"SmallFonts\" href=" + FullPath + ">View " + item.Name + "</a> | " +
                                 "<a class=\"SmallFonts\" href=" + ListPath + ">View " + list.Title + "</a>" +
                                 "</div>";

                           
                            SPUtility.SendEmail(web, true, false, to, subject, body);
                        }
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }
    }
  4. Deploy the solution in Release mode. 
  5. Verify that the assembly named SamplerAlertHandler.dll is successfully deployed in GAC for .NET Framework 4.5. Click here to know how to verify that your assembly has been deployed in GAC

2. Modify the Alert Template

The Alert Template used by SharePoint 2013 is located inside 15 hive folder. i.e. C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\XML

Inside XML folder, you will find several XML files and the one we require is alerttemplates.xml. Rather than modifying this file directly, we make a copy of this file.

  1. Copy alerttemplates.xml and name it custom_alerttemplates.xml.
  2. Open custom_alerttemplates.xml with any editor tool. Lets use Visual Studio 2012 as we are already using this IDE to build our solution. 
  3. Search for the key word "DocumentLibrary" using Ctrl + F. You will find an AlertTemplate tag as <AlertTemplate Type="List" Name="SPAlertTemplateType.DocumentLibrary">
  4. Find the <Properties> tag inside this AlertTemplate tag and copy following two lines of code inside <Properties> and </Properties> tag. <NotificationHandlerAssembly>SampleAlertHandler, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a3902394782e8e4e</NotificationHandlerAssembly> <NotificationHandlerClassName>SampleAlertHandler.CustomAlertHandler</NotificationHandlerClassName>
  5. You will require a public key token and version number of your assembly. Click here to find out public key token and version number of your assembly . In my case, the public key token is 33a1b5598fa37cf2 and it may be different for you. Also the version number is 1.0.0.0 for me. 


3. Create a text file to store Internet URL


We could have hardcoded the Internet URL or public URL for our web application. But in order to make it more configurable, our solution reads the Internet URL stored in a text file. 

Lets create a text file named SiteURL.txt inside C:\SiteURL folder and lets write the internet URL in one line as https://access.sharepointrealm.comThis is the public URL of the web application as we specified in Alternate Access Mapping. 

 You can change this Internet URL at any time. 

4. Notify the SharePoint environment to use new Alert Template. 


We will have to use stsadm command for updating the alert template. In SharePoint 2013 environment, the stsadm is available inside 15 hive folder. 
  1. First, open the dos command prompt in Run As Administrator mode. If stsadm is not configured as class path in Environmental Variable, you can navigate to following folder inside command prompt and type stsadm to see if it works. C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\bin
  2. Type following command and hit enter.stsadm -o updatealerttemplates -filename "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\XML\custom_alerttemplates.xml" -url "http://sp-realm"
    Include your internal web application URL in place of http://sp-realm
  3. Now reset IIS server and SharePoint Timer Service.
Now you can add new alert to your document library and existing alerts are also affected by this new change. Upload new documents, Delete some existing documents or modify some existing documents to test your functionality. 


Here is the new Alert Email with almost same look and feel except the URLs are changed.











Cheers
















What is a Public Key Token? How to find the public key token for a .NET DLL or Assembly ?

What is a Public Key Token?

A Public Key Token is something that makes your assembly unique. Every .NET assembly that you compile is signed by a private key. This private key is unique and used to identify the assembly. But this private key is  not publicly visible. Before it is publicly exposed, it is hashed into a 16 character (64-bit)  long string which is called as Public Key Token.

Snapshot below illustrates an assembly with its version and public key token.

An assembly version below .NET Framework 4.5

Where can you find globally deployed assemblies?
Assemblies are compiled in a bin directory of your application. But when they are deployed globally, they goes to a special location in your hard drive.

You can find assemblies of .NET Framework 4.0 and below at C:\Windows\assembly.
Assemblies of .NET Framework 4.5 goes to C:\Windows\Microsoft.NET\assembly.

You can find your assembly under any of these three folders depending upon the processor architecture you selected while compiling your assembly.

Snapshot below shows a .NET Framework 4.5 assembly named SampleAlertHandler.dll. Its public key token is visible in the address bar which is 33a1b5598fa37cf2.

An assembly version of  .NET Framework 4.5

There is also another way to find public key token of an assembly.
I will show an example for a .NET Framework 4.5 assembly. It can be obtained via a Visual Studio command prompt or via PowerShell. I will use command prompt for Visual Studio 2012.

If you don't know where Visual Studio 2012 command prompt is located, you can reach there my navigation menu as under.

  1. Start Menu
  2. Programs
  3. Microsoft Visual Studio 2012
  4. Visual Studio Tools
  5. Developer Command Prompt for VS2012
After reaching the command prompt screen you will have to use sn -T command followed by assembly location. For example

sn -T  "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\SampleAlertHandler\v4.0_1.0.0.0__33a1b5598fa37cf2\SampleAlertHandler.dll" 

The public key token will be revealed as shown below along with its version info. 
sn - T command