Friday, January 31, 2014

Get the data from all the tables

USE AIRLINE --DB NAME
GO
DECLARE @COLUMNNAME AS VARCHAR(50) = 'PNAME' --THE NAME OF THE COLUMN ON WHICH YOU NEED TO PUT THE CRITERIA
DECLARE @CRITERIA AS VARCHAR(50) = 'CONVERT(DATE,' + @COLUMNNAME + ') >= ''20130225''' -- THE ACTUAL CRITERIA/WHERE CLAUSE OF THE QUERY

PRINT @COLUMNNAME
PRINT @CRITERIA

SELECT 
  'SELECT ''' + T.NAME + ''' AS  TABLENAME, * FROM ' + T.NAME + ' WHERE ' + @CRITERIA 
FROM 
  SYS.COLUMNS C
INNER JOIN SYS.TABLES T
  ON T.OBJECT_ID = C.OBJECT_ID   
WHERE 
  C.NAME = @COLUMNNAME

Last Execution Date Time of a Stored Procedure

USE Airline
GO

SELECT 
  O.NAME,
  PS.LAST_EXECUTION_TIME
FROM 
  SYS.DM_EXEC_PROCEDURE_STATS PS 
INNER JOIN SYS.OBJECTS O 
  ON O.[OBJECT_ID] = PS.[OBJECT_ID] 

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

Thursday, January 30, 2014

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

Thursday, January 23, 2014

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