Open report from stored procedure

Posted on 2011-10-08
Last Modified: 2013-11-10
I was asked to provide a solution to launch an SSRS report from SQL Server 2008, without using SSIS.  I need to build the report string dynamically (that part I can handle) because I pass in parameters.  The Link is supposed to export the report to looks something like this once the string is built:


Can I use xp_cmdshell or something to navigate to this link?   If so, what is the syntax?
Question by:PsychoDazey
    LVL 25

    Expert Comment

    In case if I got what you want to say, you can open any of the installed browser with the address passing to the exe. E.g.

    "c:\Program Files\Mozilla Firefox\firefox.exe"
    LVL 21

    Expert Comment

    How you'll be generating that link and the dynamic paramters?
    LVL 6

    Author Comment

    I have parameters I will be passing in from a table variable.  It will grab the first row, pass in the 2 columns as parameters, and build the URL.
    LVL 21

    Accepted Solution

    Well, I wouldn't recommend using CMDSHELL for security issues in any production environment,I would use a temporary table and build my report over that table, using the 2 parameters in the report, create a scheduled rendered to pdf job for that report, let the SP truncate that table, enter the 2 parameters, and run the agent job.

    but anyway...will give you the solution using xp_cmdshell too:
    1-create a .bat file , enter that in the file

    @echo off
    start /d "C:\Program Files\Internet Explorer" IEXPLORE.EXE "http://Servername/ReportServer?/MyReports/rpt17&rs:Command=Render&rs:Format=PDF&letterid=17&appid=5431"

    Open in new window

    2-then let the SP run  
    EXECUTE xp_cmdshell 'C:\test.bat'

    Open in new window


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now