RSS

How to use Database Mail for sending emails with MSSQL

23 Apr

In the first you must read this Article { https://mostafaelmasry.wordpress.com/2012/01/31/how-to-configure-database-mail-in-sql-server/ }

Sending a test email

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘Main’,

@body = ‘Artsi Fartsi…’,

@subject = ‘Blah Blah’,

@recipients = ‘junk@sqlhacks.com’

go

Email sent verification

SELECT CAST(subject AS VARCHAR(20)) AS ‘Subject’, CAST(body AS VARCHAR(20)) AS ‘Body’, send_request_date, CAST(send_request_user AS VARCHAR(20)) AS ‘Sent by’, sent_account_id, sent_status, sent_date FROM msdb.dbo.sysmail_allitems;. go

Emailing a query result

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘Main’,

@body = ‘Here it is…’,

@subject = ‘SQL query’,

@recipients = ‘junk@sqlhacks.com’,

@execute_query_database = ‘sql911’,

@attach_query_result_as_file = 0,

@query = ‘select sales_date,sales_qty,sales_price from sales;’

go

SELECT CAST(subject AS VARCHAR(17)) AS ‘Subject’,

send_request_date,

CAST(send_request_user AS VARCHAR(20)) AS ‘Sent by’,

CAST(query AS VARCHAR(55)) AS ‘Query’,

sent_account_id, sent_status, sent_date FROM msdb.dbo.sysmail_allitems;

go

 
Leave a comment

Posted by on April 23, 2012 in Database mail

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s