1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SSMS SP Output to Excel attachment and Email

Discussion in 'HTML, Graphics & Programming' started by AHarvey, Sep 12, 2018.

  1. AHarvey

    Suspended

    Joined: Mar 6, 2008

    Posts: 8,861

    Location: Stoke area

    Hi,

    I'm rewriting a script that we run every wednesday and email to our clients, some 800+ rows.

    I've got another script to add in but I need to create an SP, then an agent job to run each week which I can figure out.

    My problem is, what's the best way of getting the results out of SSMS into a spreadsheet and then have it set as an attachment and email it out?

    Thanks
     
  2. balanceballs

    Associate

    Joined: Aug 26, 2011

    Posts: 89

    Write your SP or query. Then use Powershell to create a CSV and email it. Obviously using a CSV means you miss out on things like tabs and formatting. By far the easiest option though and you can run queries directly within Powershell.

    You should probably use SSIS/SSRS to generate a proper report, there is some functionality to create a spreadsheet.

    I did find this rather old post about exporting SQL query results directly to Excel format but it's by no means a good way of doing things and it's a maintenance nightmare: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

    And one more option is using C# to run your query/queries and then talk directly to Excel. Some boiler plate code here https://stackoverflow.com/questions/8580591/export-sql-to-excel
     
  3. Vince

    Soldato

    Joined: Oct 30, 2003

    Posts: 6,260

    Location: Essex

    I got around this by publishing the output into SSRS, SSRS will create a csv/excel/pdf and also allow you to schedule it out. Incidentally this is also imo the most pretty and manageable way, perhaps even what would be considered the "correct" way. SSRS is also bundled with SQL Server so it is win win.
     
    Last edited: Sep 13, 2018 at 12:33 AM
  4. AHarvey

    Suspended

    Joined: Mar 6, 2008

    Posts: 8,861

    Location: Stoke area

    thanks for the feedback, it gives me things to investigate.

    I know we've done this before on another call but the analyst has now left and in all honesty, i don't remember it being pretty or proper, a hack job.
     
  5. AHarvey

    Suspended

    Joined: Mar 6, 2008

    Posts: 8,861

    Location: Stoke area

    Come into work to have a play, no SSRS installed and after speaking to the MI/BI gues I need to put a service request in to get it..
     
  6. Throrik

    Wise Guy

    Joined: Sep 15, 2009

    Posts: 1,144

    Location: Manchester

    Do you have SSIS installed? You could use that too.
     
  7. Vince

    Soldato

    Joined: Oct 30, 2003

    Posts: 6,260

    Location: Essex

    I don't get why some IT functions have such issues with flexibility, why can't they let their devs have a god damn dev environment? even just a separate area on the san or if your feeling really stingy a lower teir of storage like a fast nas. I mean I understand for huge multinationals where rate of change can be quite slow but nothing stunts progress more than red tape. Whats wrong with letting one of your guys spin up a vm install sql server and all the tools and just having at it, if you do it right and segregate your dev, live and staging areas over more than one VM estate it's not even rocket science, I mean with the tools we have today we can move stuff between dev, staging and live environments without even turning servers off. We can detect failure of hardware and migrate on the fly without any real noticeable downtime. You can have hugely complex networks with all the fail over you could ever need... What we can't do though is have the software to do the job :)
     
  8. AHarvey

    Suspended

    Joined: Mar 6, 2008

    Posts: 8,861

    Location: Stoke area

    I'm not a dev, I'm a first line analyst, although in most other businesses I'd be second or third line. Our dev's do have dev environments and we've BI and MI teams that could do this. it's just been dumped on me because "Stu likes a challenge!"

    I've had SQL Server Data Tools 2015 installed and managed to use the report wizard to generate the initial report, just need to figure out how to automate it and have it send an email wth the report as a csv file.

    Never a dull day here :D
     
  9. Vince

    Soldato

    Joined: Oct 30, 2003

    Posts: 6,260

    Location: Essex

    Good that it's not like I was thinking then :) Plus it looks like you are most of the way there you could set up a trigger now that fires the report off, not really that pretty but perfectly doable :)