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 :)

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.

 Go to Start menu, click All Programs.


Click Microsoft SharePoint 2010 Products.


Click SharePoint 2010 Management Shell.


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

5.       http://server-name:port-number/admin/phpmyadmin/index.php

In our case, it is

http://st-dev-edu:9000/admin/phpmyadmin/index.php

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

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 URL	http://sp-realm/
Public Web Application URL	https://access.sharepointrealm.com
Internal Website URL of Document Library	http://sp-realm/Tutorials
Document Library	Results
Internal URL of Document Library	http://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.com. This 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 

Hide "Shared With" option for a list or library in SharePoint 2013

Hi Friends,

There is a feature in SharePoint 2013 that allows users to view "To whom the item has been shared with" for a list or library. If you want this information to be secret, you will need to configure some settings in user permissions.

Lets say that you have a document in document library as shown below.

If you click the ellipsis button with three dots (...)  as shown in above figure, you can also see information like with whom it has been shared with. If you want to hide this information with some user groups, for example lets say Read Only users, then you can follow these simple steps.

1. Log in to the site with a user in Site Owner group.
2. Go to Site Settings.
3. Go to Site Permissions.
4. Click Permission Levels on the ribbon.
     
5. Click the Read Link.
6. Uncheck Browse User Information option and  Submit.

Now, if you log in to the site with any user with Read Only permission and click the ellipsis for that particular item, you won't be able to see with whom it has been shared.

Cheers