Tuesday, February 13, 2007

Code Warning - SQL 2005 mail

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Infinity Public Account',
@description = 'Mail account for use by all database users.',
@email_address = 'db_users@Infinity.co.nz',
@replyto_address = 'Andrew.Dixon@Infinity.co.nz',
@display_name = 'Infinity Automated Mailer',
@mailserver_name = 'SMTP.Infinity.Co.NZ' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Infinity Public Profile',
@description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Infinity Public Profile',
@account_name = 'Infinity Public Account',
@sequence_number = 1 ;

-- Grant access to the profile to all users in the msdb database

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Infinity Public Profile',
@principal_name = 'public',
@is_default = 1 ;

-- Send the mail

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Infinity Public Profile'
, @recipients = 'Andrew.Dixon@Infinity.co.nz'
, @body = 'The stored procedure finished successfully.'
, @subject = 'Automated Success Message'
, @Query = "SELECT * FROM Customers"
, @attach_query_result_as_file = 1
, @query_attachment_filename = 'Customers.txt'
;

-- Check the mail

select * from sysmail_faileditems
select * from sysmail_sentitems
select * from sysmail_unsentitems

No comments:

Post a Comment