Skip to content
  • Home
  • Blog
    • Latest Posts
    • iOS
    • Mac OS
    • Java
    • SQL
    • Other
    • PC Other
    • Uncategorized
    • Windows Server 2003
  • About
  • Links
  • Twitter
  • Linkedin
  • Blog
Search
Close

Rob Bamforth's Blog

Java, Objective-c, iOS, WATCHOS, SQL, T-SQL, MySQL, SQL Server, DBA, PHP, ODBC, API, Telecoms

Tag: database mail

SQL Server 2005 Database Mail – sp_send_dbmail

October 21, 2008November 5, 2008 Rob BamforthLeave a comment

STEP 1 Create Email Account in SQL Server:

— CREATE ACCOUNT

EXECUTE msdb.dbo.sysmail_add_account_sp

      @account_name = ‘ACCOUNT1’,

      @description = ‘Mail account for ACCOUNT1 Database Mail’,

      @email_address = ‘EMAIL@ACCOUNT1.co.uk’,

      @display_name = ‘SQL EMAIL’,

@username=‘USER’,

      @password=‘PASS’,

      @mailserver_name = ‘mail.ACCOUNT1.co.uk ‘

 

STEP 2 Creat Email Profile in SQL Server:

— CREATE PROFILE

EXECUTE msdb.dbo.sysmail_add_profile_sp

       @profile_name = ‘ACCOUNT1_PROFILE’,

       @description = ‘Profile used for ACCOUNT1 database mail’

 
STEP 3 Link email account to email profile:

— LINK ACCOUNT TOPROFILE
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp   
@profile_name = ‘ACCOUNT1_PROFILE’,
@account_name = ‘ACCOUNT1’,
@sequence_number = 1

 

STEP 4 Send email:

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = ‘ACCOUNT1_PROFILE’,

    @recipients = ’email@test.co.uk’,

    @subject = ‘TEST’,

    @body = ‘THIS IS A TEST MESSAGE’,

    @body_format = ‘HTML’;

 

The possible sp_send_dbmail variables arelisted below:

 

sp_send_dbmail [ [ @profile_name = ] ‘profile_name’ ]    
[ , [ @recipients = ] ‘recipients [ ; …n ]’ ]
   
[ , [ @copy_recipients = ] ‘copy_recipient [ ; …n ]’ ]
   
[ , [ @blind_copy_recipients = ] ‘blind_copy_recipient [ ; …n ]’ ]
   
[ , [ @subject = ] ‘subject’ ]
   
 
[ , [ @body = ] ‘body’ ]
   
 
[ , [ @body_format = ] ‘body_format’ ]
   
[ , [ @importance = ] ‘importance’ ]
   
[ , [ @sensitivity = ] ‘sensitivity’ ]
   
[ , [ @file_attachments = ] ‘attachment [ ; …n ]’ ]
   
[ , [ @query = ] ‘query’ ]
   
[ , [ @execute_query_database = ] ‘execute_query_database’ ]
   
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
   
[ , [ @query_attachment_filename = ] query_attachment_filename ]
   
[ , [ @query_result_header = ] query_result_header ]
   
[ , [ @query_result_width = ] query_result_width ]
   
[ , [ @query_result_separator = ] ‘query_result_separator’ ]
   
[ , [ @exclude_query_output = ] exclude_query_output ]
   
[ , [ @append_query_error = ] append_query_error ]
   
[ , [ @query_no_truncate = ] query_no_truncate ]
   
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

 

 

December 2019
M T W T F S S
« Oct    
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Category Cloud

iOS Java Mac OS Other PC Other SQL Uncategorized Windows Windows Server 2003

Tags

2005 apple backup case charindex chart check collation command connect server connect sql server to mysql server convert data database database mail date directory file float folder getdate graph int ios iphone Java JAVA ReDirect System.out.Println() to file JAVA replace string within a string java substring example jframe jobs JOptionPane jscrollpane jtable linked server log logs look and feel mac mac osx maximise maximised MySQL mysql linked server objective-c ODBD osx output replace restore scroll SELECT Select column names and column data from SQL table send email from sql server server setup Shrink sp_send_dbmail SQL sql email SQL select data into a variable sql server SQL Server 2005 SSMS String Strings System.out.Println() t-sql table temptb text file tsql update variable windows

Links

  • cleverappco.com
  • Linkedin
  • robbamforth.com

Blog Stats

  • 496,302 hits
December 2019
M T W T F S S
« Oct    
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Connect

  • Twitter
  • Linkedin
  • Blog

Links

  • cleverappco.com
  • Linkedin
  • robbamforth.com

Blog Stats

  • 496,302 hits
Blog at WordPress.com.
Back to top
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy