Post reply

How to export Output of stored procedure into excel file.

  • April 30, 2008 at 2:15 am

    #181751

    Hello friends,

    i need some help regarding exporting outpout of stored procedure into excel file. There are different ways for it. I need suggestion for best of them.

    First of all let me explain my condition. I am working for health services. I need to send email to different doctors who is working in hospital and it contains one excel file which is having list of patients with all details treated by that doctor. Now I have list of 50 doctors and email goes to those doctors with different attachment according to treated patient. It should go every week with the list of previous week patient list. If I design one package then it can have one excel file with list of patient for that doctor. What about other 49 doctors? Now if we see that way then I need to design 50 different packages and need to schedule those package which is not a good idea.

    There should be some alternative so that one package or one stored procedure take the parameter as name of doctor and run query accordingly and create excel file. And mail it to appropriate doctor. In this case to design package is not the best idea.

    In short, i need to design something which can be scheduled as a job and send email every week with appropriate attachement. It might be possible in reporting services but just wondering how to achieve this and how to schedule report.

    I will be thankful if any one can give your suggestion to achieve this functionality.

    thank you very much,

    vijay

  • April 30, 2008 at 5:46 am

    #808951

    I am assuming that the format of the spreadsheet is the same regardless of the recipient.

    This can be done through SSIS by looping through all of the doctors, exporting the patient information for the current doctor, and then sending an email with the spreadsheet attached.

    This could also be done with reporting services using a data-driven subscription (so you would have to be running the Enterprise Edition).

    Either would work. If you have a question as to how to get a specific portion of these options working, feel free to post it.

  • May 1, 2008 at 4:55 am

    #809478

    Hi,

    thank you very much for your reply. I got the solution to schedule it using reporting services subscription. It is very easy and quick job using reporting services.

    If anyone scheduling it using subscription then it is worth to use shared subscription as you can make changes to only one subscription and scheduling of all job will be affected.

    thanks,

    vijay

  • May 2, 2008 at 10:16 am

    #810215

    Everything you ever wanted to know about working with Excel using T-SQL can be found at this link.

    www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

    Thanks for the answer Jack:Will someone PLEASE tell me how to properly post a link!

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

  • May 2, 2008 at 10:23 am

    #810221

    Tom Garth (5/2/2008)

    Everything you ever wanted to know about working with Excel using T-SQL can be found at this link.

    http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

    Will someone PLEASE tell me how to properly post a link!

    Tom use the url IFCode. In the following code replace the "{}" with "[]":

    {url}www.espn.com{/url} to get www.espn.com or

    {url=www.espn.com}Espn{/url} to get ESPN

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 2, 2008 at 10:39 am

    #810232

    Jack Corbett (5/2/2008)

    Tom Garth (5/2/2008)

    Everything you ever wanted to know about working with Excel using T-SQL can be found at this link.

    http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

    Will someone PLEASE tell me how to properly post a link!

    Tom use the url IFCode. In the following code replace the "{}" with "[]":

    {url}www.espn.com{/url} to get www.espn.com or

    {url=www.espn.com}Espn{/url} to get ESPN

    Thank you Jack. I guess including the http:// was the problem.

    Visit Us

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

  • May 2, 2008 at 11:59 am

    #810268

    Yeah that would be it. Wrapping in the URL tag adds the http://.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply