locked
PDF format in SQL 2016 RRS feed

  • Question

  • User1510859543 posted

    Is there any way to render a PDF to a file rather than to the browser using SQL Server 2016?

    Wednesday, January 10, 2018 6:49 PM

All replies

  • User347430248 posted

    Hi dlchase,

    you had provide very less information regarding your issue.

    from that one line it looks like you want to extract PDF file stored in SQL Server database instead of displaying it in browser.

    for that purpose you can refer code example below may help you.

    string strQuery = "select Name, ContentType, Data from tblFiles where id=@id";
    SqlCommand cmd = new SqlCommand(strQuery);
    cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;
    DataTable dt = GetData(cmd);
    if (dt != null)
    {
        download(dt);
    }
    
    
    private DataTable GetData(SqlCommand cmd)
    {
        DataTable dt = new DataTable();
        String strConnString = System.Configuration.ConfigurationManager
        .ConnectionStrings["conString"].ConnectionString;
        SqlConnection con = new SqlConnection(strConnString);
        SqlDataAdapter sda = new SqlDataAdapter();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        try
        {
            con.Open();
            sda.SelectCommand = cmd;
            sda.Fill(dt);
            return dt;
        }
        catch 
        {
            return null;
        }
        finally
        {
            con.Close();
            sda.Dispose();
            con.Dispose();
        }
    }
    
    
    
    private void download (DataTable dt)
    {
        Byte[] bytes = (Byte[])dt.Rows[0]["Data"];
        Response.Buffer = true;
        Response.Charset = "";
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = dt.Rows[0]["ContentType"].ToString();
        Response.AddHeader("content-disposition", "attachment;filename="
        + dt.Rows[0]["Name"].ToString());
        Response.BinaryWrite(bytes);
        Response.Flush(); 
        Response.End();
    } 

    Reference:

    Save and Retrieve Files from SQL Server Database using ASP.Net

    Export Documents saved as Blob / Binary from SQL Server

    Save and Read PDF File Using SQL Server and C#

    Regards

    Deepak

    Thursday, January 11, 2018 1:59 AM
  • User1510859543 posted

    Sorry that I wasn't clear in my request.

    I want to be able to save a PDF file using SSRS and an existing report definition (.rdl report) so that the output is a PDF file much like when you choose PDF as an output format in SSRS and the report displays in the browser.  But instead of doing this manually I want to be able to do it via code so that when a user runs a report (via URL) I want to be able to create a PDF file and then email that file or save it in a network folder.  I hope this helps clarify my request.  Sorry again.

    Thursday, January 11, 2018 1:49 PM
  • User347430248 posted

    Hi dlchase,

    You had mentioned that,"I want to be able to save a PDF file using SSRS and an existing report definition (.rdl report) so that the output is a PDF file much like when you choose PDF as an output format in SSRS and the report displays in the browser."

    There is are no any options to add code to download PDF in .rdl file.

    You need to develop a Web Application Project that can do this for you.

    Example code:

    yReportViewer.ProcessingMode = ProcessingMode.Remote;
    MyReportViewer.ServerReport.ReportServerUrl = new Uri("http://10.88.141.76:8080/ReportServer2012"); // Report Server URL
    MyReportViewer.ServerReport.ReportPath = "/StartSSRS/PersonAddressDetails";                         // Report Name
    MyReportViewer.ServerReport.Refresh();
     
    Microsoft.Reporting.WebForms.ReportParameter[] reportParameterCollection = new Microsoft.Reporting.WebForms.ReportParameter[1];
    reportParameterCollection[0] = new Microsoft.Reporting.WebForms.ReportParameter();
    reportParameterCollection[0].Name = "City";                                                         //Parameter Name
    reportParameterCollection[0].Values.Add("Seattle");                                                 //Parameter Value
    MyReportViewer.ServerReport.SetParameters(reportParameterCollection);
     
    Warning[] warnings;
    string[] streamids;
    string mimeType, encoding, extension, deviceInfo;
     
    deviceInfo = "True";
     
    byte[] bytes = MyReportViewer.ServerReport.Render("PDF", null, out mimeType, out encoding, out extension, out streamids, out warnings);
     
    Response.Buffer = true;
    Response.Clear();
    Response.ContentType = mimeType;
     
    /* 
        This header is for saving it as an Attachment and popup window should display to to offer save as or open a PDF file 
        Response.AddHeader("Content-Disposition", "attachment; filename=" + extension);        
    */
     
    /*
        This header is use for open it in browser.
        Response.AddHeader("content-disposition", "inline; filename=myfile." + extension);
        Response.BinaryWrite(bytes);
    */
     
    //Creatr PDF file on disk
    string pdfPath = @"D:\TempReports\PersonAddressDetails." + extension;       // Path to export Report.
     
    System.IO.FileStream pdfFile = new System.IO.FileStream(pdfPath, System.IO.FileMode.Create);
    pdfFile.Write(bytes, 0, bytes.Length);
    pdfFile.Close();
    Response.Flush();
    Response.End();

    Reference:

    Export SSRS Report into PDF Format from ASP.NET Application

    Regards

    Deepak

    Friday, January 26, 2018 7:03 AM
  • User1510859543 posted

    Someone sent me the following code (in VB) and it works perfectly on IIS and SQL Server 2014 and below and does not use the ReportViewer control.  On box with 2016 It fails compiling on the line below with the error 

    Dim rs As New ReportExecutionService()

    and...

    Namespace or type specified in the Imports 'Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.

    Below is the code

    Imports System
    Imports System.Collections.Generic
    Imports Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution
    Imports System.IO
    
    Partial Class reports_PDFReport
        Inherits System.Web.UI.Page
    
        Private Function CreatePDFReport(ByVal strReportName As String) As String
            ' Output variables   
            Dim strReturn As String = ""
            Dim intReturnID As Int32 = 0
            Dim encoding As String = Nothing
            Dim mimeType As String = Nothing
            Dim extension As String = Nothing
            Dim warnings As Warning() = Nothing
            Dim streamIDs As String() = Nothing
            Dim strReturnID As String = ""
    
            Dim i As Integer = 0
            Dim filelist As New List(Of String)
    
            ' ReportExecution object prepare   
            Dim rs As New ReportExecutionService()
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials
            rs.Url = UtilClass.GetReportServerPath & "/ReportExecution2005.asmx"
    
            ' Render arguments   
            Dim result As Byte() = Nothing
            Dim strFileName As String = ""
            Dim strFullPath As String = ""
            Dim reportPath As String = "/BodyShopReports/"
            Dim reportName As String = strReportName
            Dim format As String = "PDF"
            Dim historyID As String = Nothing
            Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
            Dim strOutputPath As String = Server.MapPath("~/OfficeDocs/Export/")
            Dim strTime As String = Microsoft.VisualBasic.Format(System.DateTime.Now, "hhmmss")
            strTime = Replace(strTime, ":", "")
    
            ' Prepare render      
            Dim execInfo As New ExecutionInfo()
            Dim execHeader As New ExecutionHeader()
    
            'get count of parameters sent
            Dim intParams As Int32 = lstParameters.Items.Count
            Dim parameters As ParameterValue() = New ParameterValue(intParams - 1) {}
            Dim intParamNo As Int32 = 0
    
            If intParams > 0 Then
                ' Prepare report parameters   
                Dim strItem As String = ""
                Dim strParam As String = ""
                For Each Li As ListItem In lstParameters.Items
                    strParam = Left(Li.Value(), InStr(Li.Value, "=") - 1)
                    strItem = Right(Li.Value(), Len(Li.Value) - InStr(Li.Value, "="))
    
                    parameters(intParamNo) = New ParameterValue()
                    parameters(intParamNo).Name = strParam
                    parameters(intParamNo).Value = strItem
                    intParamNo += 1
                Next
    
            End If
    
            strFullPath = strOutputPath & strReportName & ".pdf"
    
            If File.Exists(strFullPath) Then
                strFileName &= strTime
                strFullPath = strOutputPath & strReportName & strTime & ".pdf"
                filelist.Add(strFullPath)
            Else
                filelist.Add(strFullPath)
            End If
    
            ' Prepare render      
            rs.ExecutionHeaderValue = execHeader
            execInfo = rs.LoadReport(reportPath & reportName, historyID)
            If intParams > 0 Then
                rs.SetExecutionParameters(parameters, "en-us")
            End If
            Dim SessionId As [String] = rs.ExecutionHeaderValue.ExecutionID
    
            ' Actual render   
            result = rs.Render(format, devInfo, extension, encoding, mimeType, warnings, streamIDs)
            execInfo = rs.GetExecutionInfo()
    
            ' Write to file   
            Using stream As FileStream = File.Create(strFullPath, result.Length)
                stream.Write(result, 0, result.Length)
                stream.Dispose()
            End Using
    
            filelist.TrimExcess()
            If filelist.Count > 0 Then
                strFileName = Microsoft.VisualBasic.Format(System.DateTime.Now, "yyyyNNddhhmmss") & "reportfile.pdf"
    
                'merge all files using public subroutine
                UtilClass.MergePDFs(filelist, strFileName)
            Else
                strReturn = "There was no report to print."
            End If
    
            Return strReturn
    
        End Function
    End Class
    

    p.s. I could not convert the C# code as it gave errors on the first (and other) lines.

    Friday, January 26, 2018 3:36 PM