Database not found error / Remove reference to unused database in SharePoint


When you create new service applications (Example Search application) to fix issues or for some other reasons , SharePoint has a tendency to retain list of  old databases reference in configuration database . These databases are no longer in use or are available on your database server but you will see errors in event logs which refer to database which no longer exist

Example of error message

Event ID: 3760

Event Description: SQL Database ‘Search_Application_AnalyticsReportingStoreDB_56910d48c8754b409912657ac35181fa’ on SQL Server instance “server.xyz.com” not found. Additional error information from SQL Server is included below.

 Cannot open database “Search_Application_AnalyticsReportingStoreDB_56910d48c8754b409912657ac35181fa” requested by the login. The login failed.

Login failed for user “xyz\farmaccount”.

 

Resolution :

To get rid of these errors , we need to remove the reference of the databases from configuration database by running below commands in SharePoint management shell from any server in the farm:

Get-SPDatabase | fl name,id

This will give you list of all the databases and their IDs. Note the ID of the database which you need to remove and run below command to delete it:

$db = Get-SPDatabase ID

$db.Delete()

 

 

SharePoint 2013 Task List – “Assign to Email” option missing


With Sharepoint server 2013 , “Send e-mail when ownership is assigned” is not available in SharePoint 2013 (enterprise) task list, this feature is now included the ‘issue tracking’ list though.

Below are some workaroud to enable this feature  on a task list:

1. Using Sharepoint Manager 2013 :  This software explores SP Object model , you can browse every site on a SharePoint farm and view/edit every property.The software is available on Codeplex.

Using SP Manager , you can browse to the list where you want to enable the email feature and you will notice that “EnableAssigntoEmail” property is set t false by default. You can simply set it to true to enable it from the dropdown button and save your changes.

Capture0

Capture

2. Using Powershell cmdlet :

Add-PSSnapin Microsoft.SharePoint.Powershell
$web = Get-SPWeb “YourSiteURL
$list = $web.Lists.TryGetList(“YourTaskListName“)
$list.EnableAssignToEmail = $true
$list.Update()

Tip : Enable email option on Task List using any of the above method and save that List as template , example , “Task List with email” and update the users . This way you dont need to run the powershell script everytime .

Color Coded Vacation Calendar


Requirement :

  1. SharePoint Calendar to track vacation for team.
  2. The new event form should only display Start date ,end date, category , resource name only.  i.e , we need to enable “All day default” field as default in the new event form and hide it. Hide “reccurence” field and Hide Title and location column as well from newevent form.
  3.  Each resource will have separate colors on the calendar view
  4. Resource Name should be displayed on the calendar view
  5. The Calendar should displayPublic Holidays  as well (US, UK etc) in colors.

Capture3

Steps:

Hide Title and Location fileds: 

  1. Click on “event” under content types on List settings and  click title, under column settings choose “Hidden”. Repeat same steps for “location” field .
  2. You will notice that “All day Event” and “Recurrence” are greyed out and you cannot edit the properties (you cant hide them )

Hide AllDayEvent and Recurrence fileds

  1. Copy the below script  in a text file and save it as hideallevents.txt (jquery) in any folder (site assets)on your site

 <script src=”http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js” type=”text/javascript”></script>
<script type=”text/javascript”>
$(function() {
  $(‘td.ms-dttimeinput’).hide(); //hides the times on Start Time
  $(‘span[title=All Day Event] > input’).attr(“checked”,”checked”); // checks All Day Event
  //hide all of the check-boxes I don�t need
  $(‘tr:has(span[title=Recurrence])’).not(‘tr:has(tr)’).hide();
  $(‘tr:has(span[title=All Day Event])’).not(‘tr:has(tr)’).hide();
  $(‘tr:has(span[title=Workspace])’).not(‘tr:has(tr)’).hide();
});
</script>

  1. On the calendar list open, open newform.aspx in edit mode i.e append ?PageView=Shared&ToolPaneView=2 on your newform.aspx page example:-  http://amrita/Lists/Vacation%20Calendar/newform.aspx?PageView=Shared&ToolPaneView=2
  2. Now add a Content Editor WebPart. In the CEWP settings, set the content link to the url of the  hideallevents.txt file, click the test link to ensure that the url is correct (i.e. it downloads the file)
  3. Click apply and ok and save the Page.
  4. You will notice that “all day events” and “reoccurence” will disappear

Capture1

Create Column for resource name

I created choice column for resource name and added names for the employess in the choiuce column. You may create text or lookup as well

So,have 2 columns in the list already:Resource Name = column (choice) for Resource names and Category = category (choice) for vacation type (default)

Color coding Calendar based on resource names

  1. Copy the below script in a text file as vacationcalendar.txt (jquery) script to any folder on the site (for example site assets”)

 <script type=”text/javascript” src=”http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.5.1.js”></script>
<script type=”text/javascript”>

LoadSodByKey(“SP.UI.ApplicationPages.Calendar.js”, function () {
        WaitForCalendarToLoad();
    });

var SEPARATOR = “|||”;
function WaitForCalendarToLoad() {       

SP.UI.ApplicationPages.SummaryCalendarView.prototype.renderGrids = function SP_UI_ApplicationPages_SummaryCalendarView$renderGrids($p0) {
        var $v_0 = new Sys.StringBuilder();
        var $v_1 = $p0.length;

        for (var $v_2 = 0; $v_2 < $v_1; $v_2++) {
            this.$7V_2($v_2, $p0[$v_2]);
        }
        for (var $v_3 = 0; $v_3 < $v_1; $v_3++) {
            $v_0.append(‘<div>’);
            this.$I_2.$7Q_1($v_0, $p0[$v_3], $v_3);
            $v_0.append(this.emptY_DIV);
            $v_0.append(‘</div>’);
        }
        this.setInnerHtml($v_0.toString());
  ColourCalendar();
    }

 SP.UI.ApplicationPages.CalendarStateHandler.prototype.onItemsSucceed = function($p0, $p1) {
  if (this.$K_1.$8G_0()) {
            this.$28_1();
            return;
        }
        this.$41_1 = false;
        if (this.$3G_1) {
            this.$D_1.$4T_1();
            this.$3G_1 = false;
        }
        if (SP.UI.ApplicationPages.SU.$2(this.$39_1[this.$j_1])) {
            this.$39_1[this.$j_1] = [];
        }
        Array.addRange(this.$39_1[this.$j_1], $p0);
        this.$D_1.$7S_1(this.$j_1, this.$v_1, $p1, this.$39_1[this.$j_1]);
        this.$j_1++;
        this.$1h_1();
  ColourCalendar();
 }
}

function ColourCalendar() {
        if(jQuery(‘a:contains(‘ + SEPARATOR + ‘)’) != null)
        {            
   jQuery(‘a:contains(‘ + SEPARATOR + ‘)’).each(function (i) {
   $box = jQuery(this).parents(‘div[title]’);
   var colour = GetColourCodeFromCategory(GetCategory(this.innerHTML));
   this.innerHTML = GetActualText(this.innerHTML);
   jQuery($box).attr(“title”, GetActualText(jQuery($box).attr(“title”)));
   $box.css(‘background-color’, colour);
   });       
 }  
}  

function GetActualText(originalText) {    
 var parts = originalText.split(SEPARATOR);
 return parts[0] + parts[2];  
}

function GetCategory(originalText) {
 var parts = originalText.split(SEPARATOR);
 return parts[1];  
}

function GetColourCodeFromCategory(category) {
 var colour = null;    
 switch (category.trim()) {
  case ‘Amrita’:        
   colour = ‘#90EE90’;
   break;
  case ‘Amrita2’:        
   colour = ‘#FF6600’;
   break;   
  case ‘Amrita3’:        
   colour = ‘#FFB6C1’;
   break;    
     
      
 }    
 return colour;  
}

 </script>

  1. You can modify the ‘function GetColourCodeFromCategory’ and change/append  resource names and colorcode accordingly. You do not need to modify anything else in the above script. Make sure that Resource Name in column (choice) is exactly same in script else the color coding wont work.
  2. Pick colors (codes) from http://www.w3schools.com/tags/ref_colorpicker.asp?colorhex=D3D3D3
  3. Create a calculated column “Name” with the formula =UPPER([Resource Name])
  4. Add a another calculated field called CategoryTitle with the formula: ="|||"&[Resource Name]&"|||"&Name&"|||"&Category
  5. Now edit the calendar view  and in view settings-> calendar columns change the Month View, Week View and Day View under the “Calendar” view settings to be the new CategoryTitle field instead of  default Title
  6. If you create additional view , you need to repeat the above step for each view else the color will not display for resource names
  7. On the calendar page (calendar.aspx), edit the page and add a Content Editor WebPart. In the CEWP settings, set the content link to the url of the newly uploaded text file, click the test link to ensure that the url is correct (i.e. it downloads the file). You can “hide” the CEWP webpart if you want from webpart properties..
  8. Click apply, Ok and save the page .

Adding Public Holidays (using Calendar Overlay) feature

  1. We need to first publish Public holiday (US /UK or corporate calendar)  on a separate calendar list. if you alreday have your corporate calendar , you can publish it directly.
  2. I used a  tool SharePoint Holiday Loader (codeplex) to load the holiday calendar. You can easily find hol file in your MS Outlook folder or download outlook.hol file which is a MS holiday calendar file.
  3. You can load all the holidays or country wise. I wanted just UK and US holidays , that too in seperate colors so i created 2 calendars/event  list – US public holidays and UK Public holidays separately
  4. Now Go back to your Vacation calendar , in the calendar tab click “calendar overlay”
  5. Click “Add new calendar”, Add title ,choose appropriate color and in web url type the URL of the site where US and UK calendars were created
  6. Click resolve and choose the list, choose always show and click OK
  7. Repeat the same steps to add UK calendar
  8. The Public Holiday calendar can be used by any site collection/subsite using the Calendar overlays within the same web application.

References:

i used the jquery scripts from below blogs,

http://www.planetwilson.co.uk/sharepoint-2013-colour-calendar/ 

http://www.planetwilson.co.uk/sharepoint-2013-colour-calendar/