SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Monthly Archives: March 2012

Render and Email a Reporting Services Report using .Net

In an earlier post, I had described a customer scenario, where they would want to execute and render a report from a .Net application, and then send the rendered report as an email attachment.

In the previous post, I had talked about how to generate a Tree View of the Report Server Items. In this post, I would be talking mainly about the how to execute a report and render it in a required format. And then send the Report as an email attachment.

The Post, assumes the following

  1. The report which needs to be executed is selected on the Tree View and the report path is passed to the function.
  2. The Report runs with default parameters.
  3. The SMTP mail server is preconfigured.

In order to execute a Report, we need to use the ReportExecutionService() Class, defined in the ReportExecution2005 endpoint for Reporting Services.

I created a new class ExecuteAndRender for this functionality.

The ExecuteAndRender Class, defines and object for the ReportExecutionService() class, in order to execute the methods exposed by Reporting Services.

   1: public ExecuteAndRender()

   2:         {

   3:              rs = new ReportExecutionService();

   4:              rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

   5:              rs.Url = "http://localhost/reportserver/ReportExecution2005.asmx";

   6:         }

Next I created a function RenderReport(string ReportName), this function performance the following tasks

  1. Define the Report Rendering and execution settings.
  2. Calls the ReportExecutionService.LoadReport() to load the report definition.
  3. Defines the report parameters. In this case, since all default values would be used, we do not explicitly define the parameters.
  4. Calls the ReportExecutionService.Render() method to render the report in the desired format.
  5. One the report have been rendered, we create a PDF file for the report.
  6. Then call the AttachAndSendEmail() to send the email.
   1: public void RenderReport (string ReportName)

   2:         {

   3:             byte[] result = null;

   4:             //string reportPath = "/ReportFolder2";

   5:             string format = "PDF";

   6:             string historyID = null;

   7:             string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

   8:  

   9:             //DataSourceCredentials[] credentials = null;

  10:             //string showHideToggle = null;

  11:             string encoding;

  12:             string mimeType;

  13:             string extension;

  14:             Warning[] warnings = null;

  15:             

  16:             //ParameterValue[] reportHistoryParameters = null;

  17:             string[] streamIDs = null;

  18:  

  19:             ExecutionInfo execInfo = new ExecutionInfo();

  20:             ExecutionHeader execHeader = new ExecutionHeader();

  21:  

  22:             rs.ExecutionHeaderValue = execHeader;

  23:             execInfo = rs.LoadReport(ReportName, historyID);

  24:  

  25:             //ReportParameter[] param = execInfo.Parameters;

  26:  

  27:             // Prepare report parameter. Currently the report executes with Default paramerts.

  28:             // Here we retrieve the 

  29:             // But if required the Report Paramaeters can be added.

  30:             

  31:             ParameterValue[] parameters = new ParameterValue[execInfo.Parameters.Length];

  32:  

  33:             rs.SetExecutionParameters(parameters,"en-us");

  34:             String SessionId = rs.ExecutionHeaderValue.ExecutionID;

  35:  

  36:             try

  37:             {

  38:                 result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

  39:                 execInfo = rs.GetExecutionInfo();

  40:             }

  41:             catch (SoapException e)

  42:             {

  43:                 throw e;

  44:             }

  45:             // Write the contents of the report to an PDF file.

  46:             try

  47:             {

  48:                 FileStream stream = File.Create("report.pdf",result.Length);

  49:                 stream.Write(result, 0, result.Length);

  50:                 stream.Close();

  51:                 ///Now Send the email

  52:                 AttachAndSendEmail("report.pdf");

  53:             }

  54:             catch (Exception e)

  55:             {

  56:                 throw e;

  57:             }

  58:         }

Next, we have the AttachAndSendEmail() function to connect to the SMPT server and send the email. This function achieves the following tasks

  1. Create an SMTP client to connect to the SMTP server.
  2. Create the From and To Addresses for the email.
  3. Creates the MailMessage with the attachment.
  4. And Sends the email.
   1: public void AttachAndSendEmail(string ReportFileName)

   2:         {

   3:  

   4:             SmtpClient client = new SmtpClient("Address of the SMTP Server", 25);

   5:             client.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

   6:  

   7:             MailAddress from = new MailAddress("<from Address here>","<DisplayNamehere>",

   8:                             System.Text.Encoding.UTF8);

   9:  

  10:             MailAddress to = new MailAddress("<To Address here>","<DisplayNamehere>",

  11:                 System.Text.Encoding.UTF8);

  12:  

  13:  

  14:             MailMessage msg = new MailMessage(from,to);

  15:             msg.Subject = "Report Attached " + DateTime.Now.ToString();

  16:             msg.Body = "Report From Report Server";

  17:             msg.BodyEncoding = System.Text.Encoding.UTF8;

  18:             msg.Attachments.Add(new Attachment(ReportFileName));

  19:  

  20:             try

  21:             {

  22:                 client.Send(msg);

  23:             }

  24:             catch (System.Net.Mail.SmtpException e)

  25:             { throw e; }

  26:         }

 

This code sends the messages in an synchronous manner. In case if required, SMTPClient.SendAsync method can also be used.

Advertisements

Target_Recovery_Time – Indirect Checkpoint SQL Server 2012

SQL Server 2012, introduces a new Database level option “Target_Recovery_Time” to better control and predict the time it takes during Recovery for the Database. The reason for this option was simple, the “Recovery Interval” option in sp_configure was really not reliable enough.

Before we talk about the new option, lets talk a bit about the older option.

With the Recovery interval option (set in minutes, default is 0), SQL Server would schedule the Database checkpoints in such a way that the time taken to recover the database is similar to what is specified in the Recovery Interval setting. What happens in the background is that SQL Server tries to schedule the checkpoint based on the number of DML operations that are taking place, but fails to take into consideration the number of DB Pages being modified by these operations.

As shown in the Video, SQL Server first calculates the Checkpoint interval for the Database number of DML operations and the value of “Recovery Interval” option.

When the first transaction completes (which modifies about 160 pages) and the subsequent fires, we flush all these 160 pages to the disk an move the Min_Recovery_LSN forward to the checkpoint LSN. This Min Recovery LSN is the what is used during the Database recovery, to Predict what operations are to be repeated in the Redo Phase of Recovery. During the Second Transaction (which is modifying close to 12K pages), if there were no problems, during checkpoint we would have flushed these 12K pages to the disk, which would result in a spike in the Disk Write Activity. Assuming that there was a crash, just before the Checkpoint happened (the transaction did commit), because of the large amount of pages which needs to be loaded and modified, the time to recover the database might vary and would be unpredictable.

The current Checkpoint algorithm induces the following problems, which are indeed the reason why the new Database was introduced.

  • Massive spikes in the Disk Write activity, during checkpoint.
  • Unpredictable recovery times for the Database.

The idea was to smooth out the disk write activity, so that we no longer see the massive spikes and to also have better predictability of the recovery time of a database.

The new database option can be set using the following Command.

   1: ALTER DATABASE [CheckpointDB] SET TARGET_RECOVERY_TIME = 10 SECONDS 

   2: GO

or it can be set using the Database Properties window in SSMS.

image

When the new database option is set, SQL Server performs two things,

  • First it  introduces a background task to continuously flush the Dirty buffers from the disk. What SQL actually does is it calculates a min number of Dirty buffers it would keep. Every time this threshold is exceeded, the background process (called the background Recovery writer) kicks in and flushes the dirty buffers to the disk.
  • Continously move the Min_Recovery_LSN forwards to the LSN, which was most recently flushed. Since the Min_Recovery LSN is continuously moving forward, in case of a crash happens, the amount of work done during the Recovery would be small.

The video, below illustrates how this is happens.

With the new setting, the writes are no longer in spikes (smoothens out because of the background recovery writer) and the amount of work which needs to be done during the Recovery Phase is also reduced, providing more accurate estimations of the Recovery Time for the Database.

Word of Caution

If you set the Target_Recovery_Time to a very small number (like 1 sec) on an OLTP environment, SQL Server might just throttle the disk with the massive amount of Write operations which needs to be done as part of the Background Recovery Writes. Set this option, only after carefully examining the workload pattern and your SLA commitments. Most often the older “Recovery Interval” option from sp_configure works well.

I would like to thank my friend Parikshit Savjani, for his help with the Videos.

What’ New SQL Server 2012: Reporting Services Office based Renderers

With SQL Server 2012, Reporting Services, both Excel and Word rendering now creates a Office 2007-2010 compatible files. These files are also compatible with Excel/Word 2003.

Excel based files have the extension .xlsx, while the word based files have .docx extensions.

In addition, there are several other enhancements with the excel rendering

  1. Max row limitation of excel 2003 (65K) rows is no longer applicable. Now the max number of rows per worksheet can be over 1 million.
  2. Maximum columns per worksheet is 16,384.
  3. Number of colors allowed in a worksheet is approximately 16 million (24-bit color).
  4. ZIP compression provides smaller files sizes.

With word rendering, the .docx files are much smaller than the previously created .doc files.

What’s new SQL Server 2012–Server Level Auditing For all Editions

During my customer visits and over other offline interactions I have had with customer, one thing which they have always complained about is the fact that Server Level auditing in not available for other editions of SQL Server, except for the Enterprise, Developer and Evaluation Edition.

Now there is good news. With SQL Server 2012, Server level Auditing is now available for all editions of SQL Server. Database level auditing still remains limited to Enterprise, Developer and Evaluation Editions.

In addition to extending the Server level auditing to all editions, there are have been certain enhancements to the Auditing options.

  1. The logging is more resilient to failures. For example, if the target log is on a network share, and if the network connection fails, SQL Server audit would restart when the network connectivity is restored.
  2. A new Fail_Operations option has been added to On_Failure clause. With this new operations the operations which invoke the Audit, would be failed if the Audit cannot be written to be log. As mentioned above, if the write failure condition is resolved, the operations would continue to work without problems.
  3. A new MAX_Files options has been added to the Audits. Earlier either the no of log written were unlimited (limited by the max size option or the space on the destination directory) or the number of logs were regulated by the Max_Rollover_Files option. Now, we can define a limit on the maximum number of files to be created.
  4. A new option Predicate Expression has been added to the Create Server Audit command. These predicate expressions are used to determine if the Audit needs to be invoked or not. The Predicate Expressions can be specified in a where clause in the Create Server Audit command.

Generating a Tree view of Report Server Items using .Net

A couple of weeks back, while taking a SSRS workshop for a customer, I was presented with an interesting problem. Customer wanted a functionality wherein, Report Users would be allowed to view the list of the reports existing on the Report Server using an application (and not Report Manager), and they could only execute the selected reports using a button on the .Net/Web application and can only receive he rendered report using Email delivery.

One important aspect which really helped in this scenario was that the reports were not dependent on User parameters. All the reports had to be executed with Default parameters.

We tried to break down the requirements into steps

  1. Forms based/Web Application to list all the available reports on the Report Server. Obviously, only the reports on which the user has access would be visible.
  2. Provide the User with a Text Box for entering their email address.
  3. Provide a button, upon clicking which the reports would be executed and send to the User as an email.

While creating the list of all the available reports was pretty simple since ReportService2010 has ListChildren method which can be used. Executing the report and emailing it to the user is where the major challenge is.

We pondered over multiple ways of doing it, for example

  • We could create a run time subscription for the Report and send the results to the user using Email Delivery. Once the email is sent, the subscription can be deleted.
  • We could execute the reports with the required parameters (parameters are default) and then use the Email delivery method to send the reports to the users.
  • We could create a on Demand subscription for the report and execute it when the user clicks on the button.

In the customer environment, we ended up implementing the second option, since the other two options had issues with concurrency. If multiple users were accessing the Web Application, we would need to create multiple subscription run time, which was not a very scalable approach. If we used a pre-created subscription, we would not be able to run it for multiple users.

In this post, I will be talking mainly about how to generate a tree view of the Report Server Items. In the next parts I will be talking about the other options which we tried to implement at the customer end.

In order to generate a Tree View of the Report Server Catalog, the first thing I did was bind the Report Server to a Report Server endpoint(ReportService2010 in this case). I created a class ListRSItems, which implements three functions.

  • Constructor : To initialize the ReportService2010 Proxy class and dynamically bind the Report Server instance to it.
  • ReturnRSChildList(string path) : To return a list of Report Server Catalog Items, for a given report server path. This function only returns the immediate children of the Report Server item, specified as input to the function.
  • GetReportServerName(): This function is used to return the name of the Report Server instance. If it’s a default instance of Reporting Services, it would return the Machine name, otherwise it would return the report server name in the format <machinename/instancename>.

Included below is the code for the Class.

   1: public class ListRSITems

   2:     {

   3:         private ReportingService2010 rs;

   4:  

   5:         public ListRSITems()

   6:         {

   7:             try

   8:             {

   9:                 // Connect to Reporting Services

  10:                 rs = new ReportingService2010();

  11:                 rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

  12:                 rs.Url = "http://localhost/reportserver/ReportService2010.asmx";

  13:             }

  14:             catch (System.Exception ex)

  15:             {

  16:                 Console.WriteLine(ex.Message);

  17:             }

  18:         }

  19:         public CatalogItem[] ReturnRSChildList(string path)

  20:         {

  21:              return this.rs.ListChildren(path, false);                    

  22:         }

  23:         public string GetReportServerName()

  24:         {

  25:             string RSName = "";

  26:             //RSName = this.rs.GetReportServerConfigInfo(false);

  27:             System.Xml.XmlDocument doc = new System.Xml.XmlDocument();

  28:             doc.LoadXml(this.rs.GetReportServerConfigInfo(false));

  29:  

  30:             RSName = doc.SelectSingleNode("ServerConfigInfo/Server/MachineName").InnerText;

  31:             string temp = doc.SelectSingleNode("ServerConfigInfo/Server/InstanceName").InnerText;

  32:             if ( temp == "MSSQLSERVER")

  33:                 return RSName;

  34:             else

  35:                 return RSName + "/" + temp;            

  36:         }

  37:     }

Next, we need to Add a TreeView control to the windows Forms (I am using a Forms Application, but this can be used in a Web Application also) and bind the Report Server Items as Tree Nodes. For this I added two more functions to the Form class.

  • GenerateTreeView() : This is the parent function, where we create the Root Node for the Tree. The Root node name is derived from the function ListRSItems.GetReportServerName(). After the root node has been initialized, we call the ListRSItems.ReturnRSChildList, to return all first level (Child Items for the root level). For all the child items returned we call a function AddTreeNodes(), to add the specified item and all its child items to the TreeView.
  • AddTreeNodes(TreeNode parent, CatalogItem c) : In this function we add all child nodes and subsequent child nodes to the TreeView. This function calls itself recursively to find all the sub child items and add them to the TreeNode.

Code Included below:

   1: public partial class Form1 : Form

   2:     {

   3:         public Form1()

   4:         {

   5:             LRT = new ListRSITems();

   6:             InitializeComponent();

   7:             GenerateTreeView();

   8:         }

   9:  

  10:         public void GenerateTreeView()

  11:         {

  12:             string path = "/";

  13:             CatalogItem[] items = this.LRT.ReturnRSChildList(path);

  14:  

  15:             TreeNode root = new TreeNode();

  16:             root.Text = LRT.GetReportServerName();

  17:             root.ToolTipText = "Report Server Name";

  18:             root.ImageIndex = root.SelectedImageIndex = 0;

  19:             TreeView1.Nodes.Add(root);

  20:             TreeView1.BeginUpdate();

  21:             foreach(CatalogItem c in items)

  22:             {

  23:                 AddTreeNodes(root, c);

  24:             }

  25:             TreeView1.EndUpdate();

  26:         }

  27:         public void AddTreeNodes(TreeNode parent, CatalogItem c)

  28:         {

  29:             TreeNode newnode = new TreeNode();

  30:             if (c.TypeName == "Folder")

  31:             {

  32:                 CatalogItem[] items = this.LRT.ReturnRSChildList(c.Path);

  33:                 newnode.Text = c.Name;

  34:                 newnode.ToolTipText = "Report Server Folder";

  35:                 newnode.ImageIndex = newnode.SelectedImageIndex = 0;

  36:                 parent.Nodes.Add(newnode);

  37:                 foreach (CatalogItem x in items)

  38:                 {

  39:                     AddTreeNodes(newnode, x);

  40:                 }

  41:             }

  42:             if (c.TypeName == "Report")

  43:             {

  44:                 newnode.Text = c.Name;

  45:                 newnode.ToolTipText = "Report Server Report";

  46:                 newnode.ImageIndex = newnode.SelectedImageIndex = 2;

  47:                 parent.Nodes.Add(newnode);

  48:             }

  49:             if (c.TypeName == "DataSource")

  50:             {

  51:                 newnode.Text = c.Name;

  52:                 newnode.ToolTipText = "DataSource";

  53:                 newnode.ImageIndex = newnode.SelectedImageIndex = 1;

  54:                 parent.Nodes.Add(newnode);

  55:             }

  56:             if (c.TypeName == "LinkedReport")

  57:             {

  58:                 newnode.Text = c.Name;

  59:                 newnode.ToolTipText = "Linked Report";

  60:                 newnode.ImageIndex = newnode.SelectedImageIndex = 4;

  61:                 parent.Nodes.Add(newnode);

  62:             }

  63:             if (c.TypeName == "Model")

  64:             {

  65:                 newnode.Text = c.Name;

  66:                 newnode.ToolTipText = "Report Model";

  67:                 newnode.ImageIndex = newnode.SelectedImageIndex = 3;

  68:                 parent.Nodes.Add(newnode);

  69:             }

  70:         }

  71:     }

The Code generates the following TreeView Structure for my Report Server instance.

image

In another post, I will talk about how to programmatically, execute a Report and send the rendered report (in any format) as an email to user.