December 16, 2016

Automated SQL Servers Error Logs to single HTML file

Normally, we check SQL Server error log through SQL Server Management Studio or any text editor. By default, error log is located at ~\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ ERRORLOG.n1 (Here, n is instance name and n1 is nth number - dynamically appended). But, if you’re managing thousands or even just hundreds of SQL Server systems, you might spend most of the day manually checking each server.

So, what is better way to automate error log process and get error log results for each instance as an output to a single HTML file?

Solution:
One possible solution to achieve this is by using PowerShell script. Below is a PowerShell script to automate error log and generating a report for daily monitoring of SQL Server error logs.  We need to add all SQL servers' instance name in one text file. This script will read all instances mentioned in text file one by one, and generate a error log in HTML file.

Prerequisites:
Module invokesqlquery needs to be installed. Invokesqlquery can be downloaded from http://powershell4sql.codeplex.com

Powershell script:
<#
This script displays the error log entries for all listed servers for the last one day.
The script can take a few minutes to run if the error logs are large and you are looking back over several days.
#>
cls # This command will clear the screen of PowerShell window.
import-module invokesqlquery #This will import the module named "invokesqlquery" which we have downloaded and placed in module folder
$today    = (get-date).toString() #This will store today's date in $today variable
$all      = @() #This is a declaration of an array type variable to store the output in $all variable
$lookback = ((get-date).adddays(-1)).ToString()  # This will store previous date in $lookback variable
$serversList = "{your serverlist's text file path}\serverlist.txt" #This will store a full path of text file in which you have listed down the sql server's name
foreach ($server in get-content $serversList | select-object @{Name = 'serverName'; Expression = {$_}})  # This foreach loop will read the text file from given path and execute the code for each server name in that text file
{
"$((get-date).toString()) - Checking SQL Error Logs on $($server.servername)..."   #This line is for just displaying the name of server on the screen for which the process of checking SQL Error Logs is running
try  {
<# Explanation of the below code :
invoke-sqlquery -query : This is a command which executes the sql query
master..xp_readerrorlog : This is a stored procedure of in sql server to get the logs. We have passed some parameters to filter the logs.
select-object : This command is for specifying the columns which should be shown in the output.    
where-object : This command will filter the output and display only those data which contains the specific word like "error" in the Text. Other records will be discarded and will not be there in the output.
` : This symbol is to concat the line
 #>
#invoke-sqlquery -query :
$all += invoke-sqlquery -query "EXEC master..xp_readerrorlog 0, 1, null, null, '$lookback', '$today' " -server $server.servername | select-object @{Name="Server"; Expression={$server.servername}}, LogDate, ProcessInfo, Text | `
where-object {$_.Text -match 'error'}| `
where-object {$_.Text -notmatch 'setting database option recovery to'}
}
catch {"Unable to read SQL error log from server $server"}}
$all | ConvertTo-Html | Out-File "{Full Path of the output}\HTMLReport.html" #This line will export the output in the html file at given path.
 
This PowerShell script can be added as a Task Scheduler to run daily basis. Hope this is useful !

If you have any questions you can reach out our SharePoint Consulting team here.

SSRS: How to open a linked SSRS Report with parameter in popup

Last month working with SSRS related project, requirement was to open another report on click of date field. Basically, when user clicks on date field in main SSRS report, it should open another report in pop up.

Approach: To achieve the requirement, I've used JavaScript function. Here are the steps , how I have done it !
1. Click on Textbox Property of Date field.
2. Click on Action , Select Go to URL.
3. Click on Expression and apply a code as below.

 ="javascript:void(window.open('" & Cstr(Parameters!BaseUrl.Value) & "?ScheduleId=" & Cstr(Fields!BS_ID.Value) & "','blank','toolbar=no, menubar=no,scrollbars=no, resizable=no, location=no, addressbar=no, left=100,top=100,height=350,width=500'))"  
 
blog16.png

blog17.png

Now, I'll explain you how we can pass BaseURL and other fields as a parameter to generate other SSRS Report. I have a Page named "AdminReport.aspx" which has following code.

 <rsweb:ReportViewer ID="rptViewer" runat="server" Height="500px" Style="-ms-overflow-y: scroll" Width="1100px" ShowToolBar="False" ShowParameterPrompts="False" ShowCredentialPrompts="False"></rsweb:ReportViewer>   

In Code behind of AdminReport Page, I've used below code to bind a second report:
 private void ShowReport()  
     {  
       try  
       {  
         string urlReportServer = ConfigurationManager.AppSettings["ReportViewerUrl"]; //It is a URL Of Report Server.
         rptViewer.ProcessingMode = ProcessingMode.Remote; // Processing Mode will be either Remote or Local.
         rptViewer.ServerReport.ReportServerUrl = new Uri(urlReportServer); //Set the Report Server URL.
         rptViewer.ServerReport.ReportPath = "/AdminReport"; //Set path of Report Page.
         //Create an Array List to combine the Parameters which are to be passed into SSRS Report.  
         ArrayList reportParam = new ArrayList();  
         reportParam = ReportDefaultParam();  
         ReportParameter[] param = new ReportParameter[reportParam.Count];  
         for (int k = 0; k < reportParam.Count; k++)  
         {  
           param[k] = (ReportParameter)reportParam[k];  
         }  
         rptViewer.ServerReport.ReportServerCredentials = new CustomReportServerCredentials();  // Set Credentials.
         rptViewer.ServerReport.SetParameters(param); //Set Report Parameters  
         rptViewer.ServerReport.Refresh();  
       }  
       catch (Exception ex)  
       {  
         CommonUtilities.WriteEventToLogFile(CommonUtilities.GetCurrentTime() + ":AdminViewReport : ShowReport : " + ex.ToString(), "", "", "");  
         // throw ex;  
       }  
     }  
     /// <summary>  
     /// Pass default parameters to the report  
     /// </summary>  
     /// <returns></returns>  
     private ArrayList ReportDefaultParam()  
     {  
       ArrayList arrLstDefaultParam = new ArrayList();  
       string strPathAndQuery = HttpContext.Current.Request.Url.PathAndQuery;  
       string strUrl = HttpContext.Current.Request.Url.AbsoluteUri.Replace(strPathAndQuery, "/");  
       string baseUrl = strUrl + "ScheduleMaterialReport.aspx";  
       arrLstDefaultParam.Add(CreateReportParameter("BaseUrl", baseUrl));  
       return arrLstDefaultParam;  
     }  
     /// <summary>  
     /// Create report using parameters  
     /// </summary>  
     /// <param name="paramName"></param>  
     /// <param name="pramValue"></param>  
     /// <returns></returns>  
     private ReportParameter CreateReportParameter(string paramName, string pramValue)  
     {  
       ReportParameter aParam = new ReportParameter(paramName, pramValue);  
       return aParam;  
     }  
  • BaseURL - is URL of the second report page, "/SubReport.aspx").
  • BS_ID.value - is the ID (value) of selected record in main Report.
  • ScheduledId - is the field passed as query string parameter to second SSRS report.
We pass this value from main report to other report as query string parameter. E.g.: http://siteurl/SubReport.aspx?ScheduleId=6
 int ScheduleId =Convert.ToInt32(Request.QueryString["ScheduleId"]))  

Now, when user clicks on Date field in Main Report ("AdminReport.aspx" page), other report will open in "SubReport.aspx" page as pop up.

Hope this would be helpful!! If you have any questions you can reach out our SharePoint Consulting team here.

December 12, 2016

Upload multiple files with Single Element and Remove Option Without Using Jquery

Recently working on C# ASP.NET project, one requirement was to provide an option to users to upload multiple files with single element and also with delete option in Entry Form. All files should be saved on Add/Save click. It was a bit tricky as I'd to achieve this functionality WITHOUT using JavaScript and jQuery.

Solution: To achieve this requirement, I've used very popular concept of View State in C#. Bind a data table in View State and display it in Grid. Add or remove rows dynamically from data table.

Source Code:

//Fetch a data table from View State.
DataTable dtCurrentTable = (DataTable)ViewState["EditMaterial"];
DataRow drCurrentRow = null;
drCurrentRow = dtCurrentTable.NewRow();

//Add row in View State when new file is added.
if (flMaterialUpload1.HasFile)
{
     drCurrentRow["FileName"] = objProperty.fileName;
     drCurrentRow["FilePath"] = objProperty.materialaPath;
     dtCurrentTable.Rows.Add(drCurrentRow);
}
     gveditmaterial.DataSource = dtCurrentTable;
     gveditmaterial.DataBind();
     ViewState["EditMaterial"] = dtCurrentTable;

//Write row command event of grid to delete file.
DataTable dtCurrentTable = (DataTable)ViewState["EditMaterial"];
DataRow[] rows;
rows = dtCurrentTable.Select("BPD_MaterialPath ='" + hdnmaterialpath.Value + "'");
foreach (DataRow row in rows)
{
    row.Delete();
    dtCurrentTable.AcceptChanges();
}
    gveditmaterial.DataSource = dtCurrentTable;
    gveditmaterial.DataBind();
    ViewState["EditMaterial"] = dtCurrentTable;

Multiple File Upload Screen will look like below.
blog18.png

If you have any questions you can reach out our SharePoint Consulting team here.

December 8, 2016

How to send an Email having HTML formatted Body through SharePoint Add-in

Scenario: 
While working with SharePoint Add-ins (SharePoint Hosted App), there was requirement to send an Email to users. We can do it through REST API with AJAX call in SharePoint but my concern was how I can send an email with HTML formatted body. 

Remember, The recipients users should be valid SharePoint Users. Emails cannot be sent to non-SharePoint users and external users.

Solution: Here is how I achieved it !
We have to include "AdditionalHeaders" in the mailObject which is required to specify the content type of the email, here is HTML. Without using this header, the email will be sent as a plain text, and the HTML formatting will be ignored.

function sendEMail(toList, subject, mailContent) {
    appweburl = decodeURIComponent(getQueryStringParameter('SPAppWebUrl'));
var restUrl = appweburl + "/_api/SP.Utilities.Utility.SendEmail",
restHeaders = { "Accept": "application/json;odata=verbose", "X-RequestDigest": $("#__REQUESTDIGEST").val(), "Content-Type": "application/json;odata=verbose" }, mailObject = { 'properties': { '__metadata': { 'type': 'SP.Utilities.EmailProperties' }, 'To': { 'results': toList }, 'Subject': subject, 'Body': mailContent, "AdditionalHeaders": { "__metadata":{ "type": "Collection(SP.KeyValue)" }, "results": [ { "__metadata": { "type": 'SP.KeyValue' }, "Key": "content-type", "Value": 'text/html', "ValueType": "Edm.String" } ] }
} }; return $.ajax({ contentType: "application/json", url: restUrl, type: "POST", data: JSON.stringify(mailObject), headers: restHeaders }); }
The above method can be called from SharePoint Add-in or Client Side code as shown below.
//An array of valid SharePoint Users, External users & emails are not supported.
var toUserList = [user1@email.com,
user2@email.com]
//Subject of the Email.
var mailSubject = "Download documents";
//HTML formatted Email Body.
var mailContent = "<h3>Here are all documents</h3><p>Links</p><div>document 1</div>";
sendEMail(toUserList, mailSubject , mailContent).done(function (response) {
    console.log("E-Mail Sent successfully.");
}).fail(function () {
    console.error("Error while sending an E-Mail.");
});
 
Hope this will help!

If you have any questions you can reach out our SharePoint Consulting team here.