SSMS SP Output to Excel attachment and Email

[AHarvey]

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

 

[balanceballs]

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

 

[Vince]

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

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.

 

[AHarvey]

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.

 

[AHarvey]

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..

 

[Throrik]

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

 

[Vince]

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

 

[AHarvey]

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

 

[Vince]

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