Thursday, November 15, 2012

SQL DB mail


EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Default',
        @from_address = 'palexander@csod.com',
        @recipients= 'palexander@csod.com',
        @subject = 'test',
        @Body_Format = 'HTML',
        @body = 'test reply to me if you get this email'



use msdb
go
SELECT * FROM sysmail_allitems order by 1 desc
SELECT * FROM sysmail_faileditems order by 1 desc
SELECT * FROM sysmail_sentitems order by 1 desc


EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' --- check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; --- check status of Database Mail

EXECUTE dbo.sysmail_start_sp ---start Database Mail in a mail host database
EXECUTE dbo.sysmail_stop_sp ---start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'

EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
    @principal_name = 'guest',
    @profile_name = 'Default' ;
   

EXECUTE msdb.dbo.sysmail_delete_profile_sp
    @profile_name = 'Default' ;