Archive

Archive for October, 2011

SSRS – Calling Reporting Services using WCF and using the HTML output for email notification

October 27, 2011 6 comments

Recently we had a project that requires us to use Reporting Server for email notification. In short,  the HTML output of the Report is assigned to the body of the email notification.

1) First step is to create a proxy library utility containing reference to Reporting Server Web Service. To do this Click Add Service Reference and set url to:

 http://{ReportServerName}/ReportServer/ReportExecution2005.asmx, In the collection type set it to System.Collections.Generic.List

Note: Version of SSRS is 2005

Update!: Problem with Authorization:

“The HTTP request is unauthorized with client authentication scheme ‘Negotiate’. The authentication header received from the server was ‘NTLM’.”

To fix problem with Authorization issue in LoadReport method:

1. Added new method called GetDefaultBinding.

2.Also a new parameter in Export method named  useDefaultBinding with value set to false was added. If this value is set to true, it would use the binding from GetDefaultBinding and will use DefaultNetworkCredentials.

Note: Network credential is not being used when useDefaultBinding is set to true.

Updated code is as follows:

  /// <summary>
    /// Utility class that renders and exports a SQL Reporting Services report into the specified output format.
    /// </summary>
    public static class ReportExporter
    {
        private static string GetExportFormat(ExportFormat f)
        {
            switch (f)
            {
                case ExportFormat.XML: return "XML";
                case ExportFormat.CSV: return "CSV";
                case ExportFormat.Image: return "IMAGE";
                case ExportFormat.PDF: return "PDF";
                case ExportFormat.MHTML: return "MHTML";
                case ExportFormat.HTML4: return "HTML4.0";
                case ExportFormat.HTML32: return "HTML3.2";
                case ExportFormat.Excel: return "EXCEL";
                case ExportFormat.Word: return "WORD";

                default:
                    return "PDF";
            }
        }

        /// <summary>
        /// Exports a Reporting Service Report to the specified format using Windows Communication Foundation (WCF) endpoint configuration specified.
        /// </summary>
        /// <param name="url">Address of Report Web Service</param>
        /// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
        /// <param name="reportName">Friendly name of Reporting Services Report to execute</param>
        /// <param name="report">Reporting Services Report to execute</param>
        /// <param name="parameters">report parameters</param>
        /// <param name="format"></param>
        /// <param name="output">rendering output result in bytes</param>
        /// <param name="extension">output format file extension</param>
        /// <param name="mimeType">output MIME type</param>
        /// <param name="encoding">output encoding</param>
        /// <param name="warnings">warnings (if any)</param>
        /// <param name="streamIds">stream identifiers for external resources (images, etc) that are associated with a given report</param>
        /// <param name="dataSourceCredentials"></param>
        public static void Export(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters,
            ExportFormat format, out byte[] output, out string extension, out string mimeType, out string encoding, out Warning[] warnings, out string[] streamIds, DataSourceCredentials[] dataSourceCredentials=null, bool UseDefaultBinding=false)
        {

            Binding binding;
            if (UseDefaultBinding)
            {
                binding = GetDefaultBinding();
            }
            else
            {
                binding = GetBinding();
            }

            using (var webServiceProxy = new ReportExecutionServiceSoapClient(binding, new EndpointAddress(url)))
            {
                webServiceProxy.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

                if (UseDefaultBinding)
                {
                    webServiceProxy.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
                }
                else
                {
                    webServiceProxy.ClientCredentials.Windows.ClientCredential = clientCredentials;
                }

                // Init Report to execute
                ServerInfoHeader serverInfoHeader;
                ExecutionInfo executionInfo;

                try
                {

                    ExecutionHeader executionHeader = webServiceProxy.LoadReport(null, report, null, out serverInfoHeader, out executionInfo);

                    // Attach Report Parameters
                    webServiceProxy.SetExecutionParameters(executionHeader, null, parameters, null, out executionInfo);

                    //Set Credentials
                    if(dataSourceCredentials!=null)
                        webServiceProxy.SetExecutionCredentials(executionHeader, null, dataSourceCredentials, out executionInfo);

                    // Render
                    webServiceProxy.Render(executionHeader, null, GetExportFormat(format), null, out output, out extension, out mimeType, out encoding, out warnings, out streamIds);
                }
                catch (FaultException faultExc)
                {
                    throw new Exception(string.Format("Encountered error while generating the report: '{0}', Report Url: '{1}', Web Service Url: '{2}' Message: '{3}'",  reportName, report, url, faultExc.Message ), faultExc);
                }
            }
        }

        /// <summary>
        /// Export a Reporting Services to HTML
        /// </summary>
        /// <param name="url">Address of Report Web Service</param>
        /// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
        /// <param name="reportName">Friendly name of Reporting Services Report to execute</param>
        /// <param name="report">Reporting Services Report to execute</param>
        /// <param name="parameters">report parameters</param>
        /// <param name="dataSourceCredentials"></param>
        /// <returns></returns>
        public static string ExportToHTML(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters, DataSourceCredentials[] dataSourceCredentials = null, bool UseDefaultBinding = false)
        {
            Warning[] warn;
            string[] p;

            byte[] output;
            string extension, mimeType, encoding;

            //Get HTML
            ReportExporter.Export(url,
                                  clientCredentials,
                                  reportName,
                                  report,
                                  parameters.ToArray(),
                                  ExportFormat.HTML4,
                                  out output,
                                    out extension,
                                    out mimeType,
                                    out encoding, out warn, out p, dataSourceCredentials, UseDefaultBinding
                                    );

            System.Text.Encoding enc = System.Text.Encoding.GetEncoding("UTF-8");
            string html = enc.GetString(output);
            return html;
        }

        /// <summary>
        /// Returns the binding to use, eliminates the app.config
        /// </summary>
        /// <returns></returns>
        internal static Binding GetBinding()
        {
            BasicHttpBinding binding = new BasicHttpBinding();
            binding.MaxBufferPoolSize = 0;
            binding.MaxReceivedMessageSize = 5242880;
            binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            binding.TextEncoding = System.Text.Encoding.UTF8;
            binding.MessageEncoding = WSMessageEncoding.Text;
            binding.TransferMode = TransferMode.Buffered;
            binding.UseDefaultWebProxy = true;
            binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas
              {
                  MaxArrayLength = 2147483647,
                  MaxBytesPerRead = 2147483647,
                  MaxDepth = 2147483647,
                  MaxNameTableCharCount = 2147483647,
                  MaxStringContentLength = 2147483647
              };
            binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;
            return binding;
        }

        /// <summary>
        /// Returns the binding to use, eliminates the app.config
        /// </summary>
        /// <returns></returns>
        internal static Binding GetDefaultBinding()
        {
            BasicHttpBinding binding = new BasicHttpBinding();
            binding.MaxBufferPoolSize = 0;
            binding.MaxReceivedMessageSize = 5242880;
            binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            binding.TextEncoding = System.Text.Encoding.UTF8;
            binding.MessageEncoding = WSMessageEncoding.Text;
            binding.TransferMode = TransferMode.Buffered;
            binding.UseDefaultWebProxy = true;
            binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas
            {
                MaxArrayLength = 2147483647,
                MaxBytesPerRead = 2147483647,
                MaxDepth = 2147483647,
                MaxNameTableCharCount = 2147483647,
                MaxStringContentLength = 2147483647
            };
            binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;
            return binding;
        }
        #endregion
    }

Updated Unit Test to use default bindings:

[TestMethod]
        public void GetHTMLReportTest()
        {

            //Set network credentials
            string userName = "NotUsed";
            string password = "NotUsed";
            string domain = "NotUsed";
            System.Net.NetworkCredential credentials = new System.Net.NetworkCredential(userName, password, domain);
            string outputPath = @"C:\TestFolder";

            //Url
            string path = "/MyReport/Invoice";

            //Parameters
            List<ParameterValue> parameters = new List<ParameterValue>();
            parameters.Add(new ParameterValue { Name = "AcctId", Value = "0001" });

            //Get HTML
            string html = ReportExporter.ExportToHTML("http://HypV-Rpaulo/ReportServer/ReportExecution2005.asmx",
                                  credentials,
                                  "My invoice report",
                                  path ,
                                  parameters.ToArray(),UseDefaultBinding:true
                                  );

            Assert.AreEqual(true, html.Length > 0, "No HTML generated");
            Console.WriteLine(html);
            System.IO.File.WriteAllText(outputPath + "result.html", html);

        }

The library can be used in BizTalk (deployed in GAC) , SSIS 2005 or 2008(deployed in GAC, .NET version is <=3.5 and is copied to %Windir%\Microsoft.NET\Framework\v2.0.50727 folder to be able to referenced in a SSIS Package..

Entity Framework – The version of SQL Server in use does not support datatype ‘datetime2’

October 26, 2011 Leave a comment

I came across this error when I’ve generated the EDMX model (Entity Framework) using a database source that is SQL Server 2008R2 and ran it in different environment that has different SQL Version (2005).

To fix this issue:

Manually open the EDMX file using XML Editor or notepad, replace the value of ProviderManifestToken from 2008 to 2005.

Passed Microsoft BizTalk Server 2010 (70-595) Exam

October 10, 2011 2 comments

I’ve recently passed the exam Microsoft BizTalk Server 2010 (70-595) exam with flying colors and I must say, they really did a good job on the questions.  Most of it can be answered by sheer experience in BizTalk. So what are you waiting for, if you’re an experienced BizTalk developer i think you can easily ace the exam.

Categories: BizTalk Server Tags:
%d bloggers like this: