Sending Query Results in SMTP Mail

Question

Post reply

Sending Query Results in SMTP Mail

Denis Wilkinson

Mr or Mrs. 500

Points: 569
More actions
November 13, 2008 at 6:11 am

#195793

Hi,
I want to send the results of a query as the message of an SMTP mail i.e.
@message = SELECT * FROM #temp
of course this doesn't work. I have tried BOL but can't seem to find anything relevant.
Is there a simple way that I am missing?

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

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

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

Use sp_send_dbmail. There is the following parameter.

Per Bol:

@query = ] 'query'

Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

Denis Wilkinson Mr or Mrs. 500 Points: 569 More actions · Answer 2

Thanks,

Iam still mentally upgrading from 2000 - 2005 and was still trying to use SMTP mail. Configured the query with sp_send_dbmail and it works perfectly.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Denis Wilkinson (11/14/2008)
Thanks,
Iam still mentally upgrading from 2000 - 2005 and was still trying to use SMTP mail. Configured the query with sp_send_dbmail and it works perfectly.

Any chance of seeing that fine query... I'm getting ready to do something similar. Thanks.

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

Denis Wilkinson Mr or Mrs. 500 Points: 569 More actions · Answer 4

Fairly simple Jeff, nothing special, the only issue was finding the @profile_name, I found this link which explained it all http://msdn.microsoft.com/en-us/library/ms174410.aspx

Best of luck.

--Send file embedded in the body of the email

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Newlands Auto Email',

@recipients = 'denis.wilkinson@za.sabmiller.com; constant.witbooi@za.sabmiller.com; stuart.yates@za.sabmiller.com',

@body = 'Please maximise width of the email to see all the columns correctly',

@query = 'select * from NEWSS160.eQMSDiagnostics.dbo.Tagtemp' ,

@query_result_width = '250',

@subject = 'InSQL EQMS Tags in Pipe-Reconnect',

@attach_query_result_as_file = 0 ;

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 5

--Any chance of seeing that fine query... I'm getting ready to do something similar. Thanks.

--Jeff Moden.

Hi Jeff

I implemented smtp mail (SQL 2000) and dbmail (SQL 2005) a few months ago,

below is a sample of 2 methods you can use after configuring db mail in SQL 2005:

1. using a stored procedure (usp_Create_Entry):

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'sqlservacct',

@recipients = 'kevin@hackers.com',

@query = 'EXEC mydatabase.dbo.usp_Create_Entry' ,

@subject = 'File FTP notification' ;

2. using a select statement:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'sqlservacct',

@recipients = 'kevin@hackers.com',

@query = 'SELECT COUNT(*) FROM AdventureWorks.Production.WorkOrder

WHERE DueDate > ''2008-08-08'' ',

@subject = 'Production Work Order' ;

Regards

Kevin

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

Cool... thanks, guys.

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

dbajunior SSCrazy Points: 2307 More actions · Answer 7

Jeff,

You can also specify a proc/view name for your query. Such as:

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'yourname@yourcompany.com',

@subject = 'Proc Test',

@body = 'Proc Test Results:',

@query = 'Yourdbname..yourProcOrViewName',

@importance = 'High'

Or a Proc With Parameter(s) if needed:

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'yourname@yourcompany.com',

@subject = 'Proc Test With Parameter Specified',

@body = 'Proc With Parameter Test Results:',

@query = 'Yourdbname..yourprocname ''yourparameter''',

@importance = 'High'

Sorry! Already mentioned above!

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

James A. Lawrence (11/18/2008)
Sorry! Already mentioned above!

Still, thanks for the thought.

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

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 9

I do send the attachment all the time using the following script-

----(you need to configure the db mail and create a profile)

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBA Notification',

@recipients = 'yourname@yourcompany.com',

@subject = 'whatever Report',

@body = Report name:',

@query = 'Yourdbname..yourprocname ''yourparameter''',

@body_format = 'TEXT',

@attach_query_result_as_file = 1,

@query_attachment_filename ='Report.CSV',

@query_result_header = 1,

@query_result_width = 32767,

@query_result_separator = " "