sp_send_dbmail

Question

Post reply

sp_send_dbmail

Welsh Corgi

SSC Guru

Points: 116520
More actions
December 9, 2016 at 9:46 am

#315795

How do I go about getting the results from a SQL Query into and file and e-mail it?
USE msdb;
GO
EXEC sp_send_dbmail
@recipients='mark@mark.com',
@subject='Client Report',
@body='Please find your latest report attached',
@file_attachments='D:\MyTable.csv';[/code]
[/code]
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 10 posts - 1 through 9 (of 9 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

The following will output to a CSV but I do not want a CSV Format.

What are my options?

bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Thom A SSC Guru Points: 99134 More actions · Answer 2

SQL itself isn't great for creating a dataset and putting it into an attachment to email. You could put it into an inline html table, which the msdn page on sp_send_dbmail (Transact-SQL) explains how to do quite well.

If you must do it as an attachment, have you instead considering using SSRS instead? You have a range of options on your export format then, for example xlsx, pdf, docx, csv, MHTML, and more. You can then create a subscription which can either be scheduled, or called from a stored procedure, to run whenever needed.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Welsh Corgi (12/9/2016)
The following will output to a CSV but I do not want a CSV Format.
What are my options?
bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S

what format do you want?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

joeroshan SSChampion Points: 10377 More actions · Answer 4

A simple SSIS package can do the trick for you.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Welsh Corgi (12/9/2016)
USE msdb; GO EXEC sp_send_dbmail @recipients='mark@mark.com', @subject='Client Report', @body='Please find your latest report attached',
@file_attachments='D:\MyTable.csv';[/code]
[/code]

If you're just sending the results of a query, you can use sp_send_dbmail directly to perform the query instead of running the query, saving it separately, then trying to attach it. The file format will be aligned text (padded with spaces), not CSV, you'd just need to add a couple of parameters to sp_send_dbmail instead of @file_attachments:

@profile_name = N'some dbmail profile',

@query = N'some query here',

@execute_query_database = N'your_db_name',

@attach_query_results_as_file = 1,

@query_attachment_filename =N'somefilename.txt'

DamianC SSCrazy Eights Points: 8023 More actions · Answer 6

I've used SSRS for this in the past as it's pretty straight forward

- Damian

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I need to output the Query results to a file and send as an attachment.

I guess that I need create a Script task to delete the existing if it exist?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 8

Declare @sqlCommand nvarchar(max)

, @recipients varchar(max) = '{your email address here}';

Declare @body nvarchar(max) = 'This File created on: ' + convert(char(10), @startDate, 121);

Set @sqlCommand = '

Set Nocount On;

Select a.Column1 As [Sep=,' + char(13) + char(10) + 'MyFirstColumn]

, a.Column2

, a.Column3

From dbo.MyTable a;'

Execute msdb.dbo.sp_send_dbmail

@profile_name = '{Your email profile here}'

, @reply_to = 'reply-to-some-address@somedomain.com'

, @from_address = 'some-from-address@somedomain.com'

, @recipients = @recipients

, @subject = 'My File'

, @body = @body

, @query = @sqlCommand

, @query_result_separator = ','

, @query_result_header = 1

, @query_result_width = 8000

, @attach_query_result_as_file = 1

, @execute_query_database = '{Your Database Here}'

, @query_attachment_filename = 'MyNewFile.csv';

This is a template - it will send a CSV file in email as an attachment that can be opened directly when double-clicked by Excel. The key is to make sure the first column defines the file for Excel to be a CSV file.

If you really need an Excel file you will have to use either SSIS or SSRS to create it - but for a simple CSV the above works quite well.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 9

Welsh Corgi (12/12/2016)
I need to output the Query results to a file and send as an attachment.

You still haven't answered the most important question, yet. What format should the file be in?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)