To get a list of Databases that were backed-up and do not currently exist

SELECT
  DISTINCT B.DATABASE_NAME
FROM
  MSDB.DBO.BACKUPSET B
WHERE
  DB_ID(B.DATABASE_NAME) IS NULL

To get the List of all Databases which are not backed up till date

SELECT
  D.NAME [DB_NAME]
FROM
  MASTER.SYS.DATABASES D
LEFT JOIN MSDB.DBO.BACKUPSET B
  ON B.DATABASE_NAME = D.NAME
WHERE
  D.DATABASE_ID IS NULL

To get the List/History/Log of all the Successful Backups

SELECT 
  B.MACHINE_NAME,
  B.SERVER_NAME,
  B.DATABASE_NAME AS DBNAME,
  B.BACKUP_START_DATE,
  B.BACKUP_FINISH_DATE,
  CASE 
    WHEN B.[TYPE] = 'D' THEN 'DATABASE'
    WHEN B.[TYPE] = 'I' THEN 'DIFFERENTIAL DATABASE'
    WHEN B.[TYPE] = 'L' THEN 'LOG'
    WHEN B.[TYPE] = 'F' THEN 'FILE OR FILEGROUP'
    WHEN B.[TYPE] = 'G' THEN 'DIFFERENTIAL FILE'
    WHEN B.[TYPE] = 'P' THEN 'PARTIAL'
    WHEN B.[TYPE] = 'Q' THEN 'DIFFERENTIAL PARTIAL'
    ELSE B.[TYPE]
  END BACKUP_TYPE,    
  B.EXPIRATION_DATE,
  B.[USER_NAME],
  DATEDIFF(MINUTE,B.BACKUP_START_DATE ,B.BACKUP_FINISH_DATE) AS TOTAL_TIME_IN_MINUTE,
  B.RECOVERY_MODEL,
  B.BACKUP_SIZE/(1024 * 1024 * 1024) AS TOTAL_SIZE_GB,
  BF.PHYSICAL_DEVICE_NAME AS LOCATION
FROM 
  MSDB.DBO.BACKUPSET AS B
INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY AS BF
  ON B.MEDIA_SET_ID=BF.MEDIA_SET_ID
ORDER BY 
  B.BACKUP_START_DATE DESC
GO

To get a list of all successful Backups taken till date for a particular Database

DECLARE @DBNAME AS VARCHAR(100) = 'AIRLINE'--DATABASE NAME
SELECT 
  B.MACHINE_NAME,
  B.SERVER_NAME,
 B.DATABASE_NAME AS DBNAME,
  B.BACKUP_START_DATE,
  B.BACKUP_FINISH_DATE,
  CASE 
    WHEN B.[TYPE] = 'D' THEN 'DATABASE'
    WHEN B.[TYPE] = 'I' THEN 'DIFFERENTIAL DATABASE'
    WHEN B.[TYPE] = 'L' THEN 'LOG'
    WHEN B.[TYPE] = 'F' THEN 'FILE OR FILEGROUP'
    WHEN B.[TYPE] = 'G' THEN 'DIFFERENTIAL FILE'
    WHEN B.[TYPE] = 'P' THEN 'PARTIAL'
    WHEN B.[TYPE] = 'Q' THEN 'DIFFERENTIAL PARTIAL'
    ELSE B.[TYPE]
  END BACKUP_TYPE,
  B.EXPIRATION_DATE,
  B.[USER_NAME],
  DATEDIFF(MINUTE,B.BACKUP_START_DATE ,B.BACKUP_FINISH_DATE) AS TOTAL_TIME_IN_MINUTE,
  B.RECOVERY_MODEL,
  B.BACKUP_SIZE/(1024 * 1024 * 1024) AS TOTAL_SIZE_GB,
  BF.PHYSICAL_DEVICE_NAME AS LOCATION
FROM 
  MSDB.DBO.BACKUPSET AS B
INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY AS BF
 ON B.MEDIA_SET_ID=BF.MEDIA_SET_ID
WHERE
  B.DATABASE_NAME = @DBNAME  
ORDER BY 
  B.BACKUP_START_DATE DESC
GO

Export to Excel with child grid in .Net

DataSet dsParent = new System.Data.DataSet();

            //export to excel with allow paging =false

            Response.Clear();

            //parent grid

            grvFetchJobPosting.AllowPaging = false;

            Response.Buffer = true;

            GridView gvrChild = new GridView();

            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

            dsParent = (DataSet)Session["FetchVMSData"];

            grvFetchJobPosting.AllowPaging = false;

            grvFetchJobPosting.DataSource = dsParent.Tables[0];

            grvFetchJobPosting.DataBind();

            HtmlForm frm = new HtmlForm();

            foreach (GridViewRow gvr in grvFetchJobPosting.Rows)

            {

                if (gvr.RowType == DataControlRowType.DataRow)

                {

                    GridView GridView2 = (GridView)gvr.FindControl("grvVendorData");//child grid view

                    GridView2.AllowPaging = false;

                }

            }

            Response.ContentType = "application/vnd.ms-excel";

            Response.AddHeader("Content-Disposition", "attachment;filename=SalesReport.xls");

            //Response.Headers["Content-Disposition"] = "attachment;filename=DetailedRRFReport.xls";

            Response.Charset = "";

            this.EnableViewState = false;

            this.ClearControls(grvFetchJobPosting);

            grvFetchJobPosting.RenderControl(oHtmlTextWriter);

            grvFetchJobPosting.Parent.Controls.Add(frm);

            frm.Attributes["runat"] = "server";

            //frm.Controls.Add(FetchRRFDataGrid);

            frm.RenderControl(oHtmlTextWriter);

            Response.Write(oStringWriter.ToString());

            Response.End();

           

Create Dynamic Calender from SQL Query

DECLARE @Month AS INT 
 SET @Month=DatePart(Month,GETDATE()) --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT 
 SET @Year=DatePart(YEAR,GETDATE()) --Set the YEAR for which you want to generate the Calendar.

DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'
DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate));
WITH Dates AS (
  SELECT @StartDate Dt
  UNION ALL
  SELECT DATEADD(DAY,1,Dt) 
FROM  Dates 
  WHERE DATEADD(DAY,1,Dt) <= @EndDate),Details AS ( 
 SELECT 
DAY(Dt) CDay,
DATEPART(WK,Dt) CWeek,
MONTH(Dt) CMonth,
YEAR(Dt) CYear,
DATENAME(WEEKDAY,Dt) DOW,
Dt 
 FROM 
Dates
)

SELECT
  Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday
FROM
  (SELECT CWeek,DOW,CDay FROM Details) D
PIVOT
(
 MIN(CDay)
 FOR DOW IN (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)
) AS PVT
ORDER BY CWeek

JavaScript references in Microsoft Dynamics CRM 2013

CRM 2013 fully supports the JavaScript object model from the previous version; however, it does not support the JavaScript object model from Microsoft Dynamics CRM 4.0.

Check out following reference documentation for client-side events and object models that can be used with JavaScript libraries. * marked is new in CRM 2013.

Xrm.Utility: Xrm.Utility object provides a container for useful functions not directly related to the current page. The following table lists the functions of Xrm.Utility.

Xrm.Utility
alertDialog	Displays   a dialog box with a message.
confirmDialog	Displays   a confirmation dialog box that contains a message as well as OK and Cancel   buttons.
isActivityType	Determine   if an entity is an activity entity.
openEntityForm	Opens   an entity form.
openWebResource	Opens   an HTML web resource.

Xrm.Page.data: Xrm.Page.data provides an entity object that provides collections and methods to manage data within the entity form. The following tables lists the functions of Xrm.Page.data and Xrm.Page.data.entity

Xrm.Page.data
getIsValid*	Do a validation check for the data in   the form.
refresh*	Asynchronously refresh all the data of the form without reloading the page.
save*	Saves the record asynchronously with the option to set callback functions.
Xrm.Page.data.entity
addOnSave	Adds a function to be called when the record is saved.
getDataXml	Returns a string representing the xml that will be sent to the server when the record is saved.
getEntityName	Returns a string representing the  logical name of the entity for the record.
getId	Returns a string representing the GUID   id value for the record.
getIsDirty	Returns a Boolean value that indicates if any fields in the form have been modified.
getPrimaryAttributeValue*	Gets a string for the value of the primary attribute of the entity.
removeOnSave	Removes a function to be called when the record is saved.
save	Saves the record with the options to close or new.

Xrm.Page.context: Xrm.Page.context provides methods to retrieve information specific to an organization, a user, or parameters that were passed to the form in a query string. The following table lists the functions of Xrm.Page.context.

Xrm.Page.context
client.getClient*	Returns a value to indicate which client the script is executing in.
client.getClientState*	Returns a value to indicate the state of the client.
getClientUrl	Returns the base URL that was used to access the application.
getCurrentTheme	Returns a string representing the current Microsoft Office Outlook theme chosen by the user.
getOrgLcid	Returns the LCID value that represents   the base language for the organization.
getOrgUniqueName	Returns the unique text value of the   organization’s name.
getQueryStringParameters	Returns a dictionary object of key   value pairs that represent the query string arguments that were passed to the   page.
getUserId	Returns the GUID of the SystemUser.Id value for the current user.
getUserLcid	Returns the LCID value that represents the provisioned language that the user selected as their preferred language.
getUserName*	Returns the name of the current user.
getUserRoles	Returns an array of strings that represent the GUID values of each of the security roles that the user is  associated with.
isOutlookClient	(Deprecated) Returns a Boolean value indicating if the user is using Microsoft Dynamics CRM for Outlook.
isOutlookOnline	(Deprecated) Returns a Boolean value that indicates whether   the user is connected to the CRM server.
prependOrgName	Prepends the organization name to the   specified path.

Xrm.Page.ui:  Xrm.Page.ui provides collections and methods to manage the user interface of the form. The following table lists the functions of Xrm.Page.ui

Xrm.Page.ui
clearFormNotification*	Remove form level notifications.
close	Method to close the form.
formSelector.getCurrentItem	Method to return a reference to the   form currently being shown.
formSelector.items	A collection of all the form items accessible to the current user.
getViewPortHeight	Method to get the height of the viewport in pixels.
getViewPortWidth	Method to get the width of the viewport   in pixels.
getCurrentControl	Get the control object that currently has focus.
getFormType	Get the form context for the record.
navigation.items	A collection of all the navigation items on the page.
setFormNotification*	Display form level notifications.
refreshRibbon	Re-evaluate the ribbon data that controls what is displayed in it.
Collections
Xrm.Page.data.entity.attributes	All attributes on the page.
Xrm.Page.ui.controls	All controls on the page.
Xrm.Page.ui.formSelector.items	All the forms available to the user.
Xrm.Page.ui.navigation.items	All the items in the form navigation   area.
Xrm.Page.ui.tabs	All the tabs on the page.
Xrm.Page Attribute.controls	All the controls for the attribute.
Xrm.Page.ui Section.controls	All the controls in the section.
Xrm.Page.ui Tab.sections	All the sections in the tab.
Collections Methods
forEach	Apply an action in a delegate function to each object in the collection.
get	Get one or more object from the collection depending on the arguments passed.
getLength	Get the number of items in the collection.

Attributes: Attributes store the data available in the record. Attributes are available from the Xrm.Page.data.entity.attributes collection. To access an attribute you can use the Xrm.Page.data.entity.attributes.get method or the shortcut version Xrm.Page.getAttribute. Following table shows how you can query attribute properties to understand what kind of attribute it is or change the behavior of the attribute.

Xrm.Page.getAttribute(“…”)
getAttributeType	Get the type of attribute.
getFormat	Get the attribute format.
getIsDirty	Determine whether the value of an   attribute has changed since it was last saved.
getIsPartyList	Determine whether a lookup attribute   represents a partylist lookup.
getMaxLength	Get the maximum length of string which   an attribute that stores string data can have.
getName	Get the name of the attribute.
getParent	Get a reference to the Xrm.Page.data.entity object that is the parent to all attributes.
getRequiredLevel	Returns a string value indicating   whether a value for the attribute is required or recommended.
getSubmitMode	Sets whether data from the attribute   will be submitted when the record is saved. always / never / dirty
getUserPrivilege	Determine what privileges a user has   for fields using Field Level Security.
getValue / setValue	Gets or Sets the data value for an   attribute.
setRequiredLevel	Sets whether data is required or   recommended for the attribute before the record can be saved. none / required  / recommended
setSubmitMode	Returns a string indicating when data   from the attribute will be submitted when the record is saved.
Number Attribute Methods
getMax / getMin		Returns a number indicating the maximum   or minimum allowed value for an attribute.
getPrecision		Returns the number of digits allowed to   the right of the decimal point.
setPrecision*		Override the precision set for a number attribute.
DateTime Attribute Methods
setIsAllDay*			Specify whether a date control should  set a value including the entire day.
setShowTime*			Specify whether a date control should  show the time portion of the date.

Controls: Controls represent the user interface elements in the form. Each attribute in the form will have at least one control associated with it. Not every control is associated with an attribute. IFRAME, web resource, and subgrids are controls that do not have attributes. Controls are available from the Xrm.Page.ui.controls collection. To access a control you can use the Xrm.Page.ui.controls.get method or the shortcut version Xrm.Page.getControl. The following table lists the functions of Controls.

Xrm.Page.getControl(“…”)
clearNotification*	Remove a message already displayed for   a control.
getAttribute	Get the attribute that the control is   bound to.
getControlType	Get information about the type of   control.
getDisabled / setDisabled	Get or Set whether the control is   disabled.
getLabel / setLabel	Get or Set the label for the control.
getName	Get the name of the control.
getParent	Get the section object that the control   is in.
getVisible / setVisible	Get or Set a value that indicates   whether the control is currently visible.
setFocus	Sets the focus on the control.
setNotification*	Display a message near the control to   indicate that data is not valid.

Lookup Controls: The following table lists the functions of Lookup Control.

addCustomFilter*	Use fetchXml to add additional filters   to the results displayed in the lookup. Each filter will be combined with an   ‘AND’ condition.
addCustomView	Adds a new view for the lookup dialog   box.
addPreSearch*	Use this method to apply changes to   lookups based on values current just as the user is about to view results for   the lookup.
getDefaultView / setDefaultView	Get or Set Id value of the default   lookup dialog view.
removePreSearch*	Use this method to remove event handler

OptionSet: The following table lists the functions of OptionSet Control.

getInitialValue	Returns a value that represents the   value set for an optionset or boolean when the form opened.
getOption[s]	Returns an option object with the value   matching the argument passed to the method.
getSelectedOption	Returns the option object that is selected.
getText	Returns a string value of the text for   the currently selected option for an optionset attribute.
adoption / removeOption	Adds or remove an option to an option   set control.
clearOptions	Clears all options from an Option Set   control.

IFRAME and Web Resource Controls:  An IFRAME control allows you to include a page within a form by providing a URL. An HTML web resource added to a form is presented using an IFRAME element. Silverlight and image web resources are embedded directly within the page. The following table lists the functions of IFrame or Web Resource controls.

getData / setData	Get or Set the value of the data query   string parameter passed to a Silverlight web resource.
getInitialUrl	Returns the default URL that an I-frame   control is configured to display. This method is not available for web   resources.
getObject	Returns the object in the form that   represents an I-frame or web resource.
getSrc / setSrc	Get or Set the current URL being   displayed in an IFrame or web resource.

Sub-Grid Control: Sub-Grid control has refresh method. We can use this method to refresh data displayed in a Sub-Grid.

refresh

Refreshes the data displayed in a Sub-Grid.

OnChange Event: There are three methods you can use to work with the OnChange event for an attribute.

addOnChange / removeOnChange	Sets or remove a function to be called   when the attribute value is changed.
fireOnChange	Causes the OnChange event