locked
Calling a report from T-SQL RRS feed

  • Question

  • Currently we have a C# application that generates an SSRS report and saves the report as a PDF. 

    We are redoing our process and now want to do everything with TSQL.  How do we call SSRS from T-SQL and specify the location where the pdf will be saved (..\directory\filename.pdf)
    Jim
    • Moved by KJian_ Tuesday, July 13, 2010 1:51 AM (From:Transact-SQL)
    Monday, July 12, 2010 10:08 PM

Answers

  • Hi Jim,

    From your descritipon, I get your idea that you want to render a SQL Server Reporting Services report to PDF using Transaction-SQL(T-SQL). If I have misunderstood, please don't hesitate to let me know.

    As you known, in SQL Server Reporting Services, we can use the Reporting Services web services to render a report to PDF format. And in Microsoft SQL Server 2005 or later, we are able to use CLR stroed procedure to call a web services.

    So,  in this case, we can use CLR stored procedure to call Reporting Services web services to render the report to PDF format.

    Alternative, SQL Server Reporting Services can deliver a report to a specified folder in specified format(e.g. PDF). Once we create a subscription in SQL Server Reporting Services, SQL Server Reporting Services will create a SQL Server job with the similar step as below to fire the event to deliver report to a specified location:
            exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
    Now, we can use this theory to work around the issue we have:
            1. Create a 'Once' subscription in SQL Server Reporting Services. In this subscription, render the report to a specified folder with PDF format.
            2. Open the Report Server database with SQL Server Managerment Studio(SSMS)
            3. Go to "SQL Server Agent" >> "Jobs", find the job for the subscription we created in step1.
            4. Double-click the job
            5. In "Job Properties" dialog, select tab "Steps"
            6. Click "Edit" to edit the step
            7. Copy the comand from the "Command" textbox.
            8. Now, create new stored procedure with the command we copied in step7.

    After finishing these steps, we can call the user stored procedure to render the report to a specified location with PDF format. We can also able to change the subscription type to be Data-Driven subscription, and save the locations in the delivered table. This will help us to deliver reports to different locations.

    For more information, please see:
    Using Webservices and Xml Serialization in CLR Integration: http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by Challen Fu Tuesday, July 20, 2010 12:10 PM
    Tuesday, July 13, 2010 9:38 AM

All replies

    • Deploy a report. Create a subscription for that report. Specify share for a report to be saved. Find subscription job in SQL Server agent jobs. Create a new job to call report subscription job by executing sp_start_job 'Subscription Name' (recommended)
    • Create a script to run a particular report using SSRS command line. Use xp_cmdshell 'rs.exe -i'
    • Create a SSIS package. Create a script task executing rs.exe. Deploy SSIS package. Create a job executing that package. Use sp_start_job to execute a job that runs SSIS package

    Sergei
    Monday, July 12, 2010 11:09 PM
  • Hello,

    Below is the link, which describes about File Share Subscriptions.
    http://msdn.microsoft.com/en-us/library/ms159264.aspx

    Hope its helpful....


    Pavan Kokkula Infosys Technologies Limited.
    Tuesday, July 13, 2010 4:17 AM
  • Hi Jim,

    From your descritipon, I get your idea that you want to render a SQL Server Reporting Services report to PDF using Transaction-SQL(T-SQL). If I have misunderstood, please don't hesitate to let me know.

    As you known, in SQL Server Reporting Services, we can use the Reporting Services web services to render a report to PDF format. And in Microsoft SQL Server 2005 or later, we are able to use CLR stroed procedure to call a web services.

    So,  in this case, we can use CLR stored procedure to call Reporting Services web services to render the report to PDF format.

    Alternative, SQL Server Reporting Services can deliver a report to a specified folder in specified format(e.g. PDF). Once we create a subscription in SQL Server Reporting Services, SQL Server Reporting Services will create a SQL Server job with the similar step as below to fire the event to deliver report to a specified location:
            exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
    Now, we can use this theory to work around the issue we have:
            1. Create a 'Once' subscription in SQL Server Reporting Services. In this subscription, render the report to a specified folder with PDF format.
            2. Open the Report Server database with SQL Server Managerment Studio(SSMS)
            3. Go to "SQL Server Agent" >> "Jobs", find the job for the subscription we created in step1.
            4. Double-click the job
            5. In "Job Properties" dialog, select tab "Steps"
            6. Click "Edit" to edit the step
            7. Copy the comand from the "Command" textbox.
            8. Now, create new stored procedure with the command we copied in step7.

    After finishing these steps, we can call the user stored procedure to render the report to a specified location with PDF format. We can also able to change the subscription type to be Data-Driven subscription, and save the locations in the delivered table. This will help us to deliver reports to different locations.

    For more information, please see:
    Using Webservices and Xml Serialization in CLR Integration: http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by Challen Fu Tuesday, July 20, 2010 12:10 PM
    Tuesday, July 13, 2010 9:38 AM
  • we are able to use CLR stroed procedure to call a web services.

    So,  in this case, we can use CLR stored procedure to call Reporting Services web services to render the report to PDF format.

    ...yes!!! but how?
    Monday, August 16, 2010 12:44 PM