1 / 26

INT415: Sending E-Mail from within ASE Stored Procedures

INT415: Sending E-Mail from within ASE Stored Procedures. Steve Bradley Sr. Database Administrator Nielsen Media Research August 15-19, 2004. Who I am. Sr. Database Administrator Information Technology Professional since 1978 Employment History Nielsen Media Research The Capital Group

yair
Download Presentation

INT415: Sending E-Mail from within ASE Stored Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. INT415: Sending E-Mail from within ASE Stored Procedures Steve Bradley Sr. Database Administrator Nielsen Media Research August 15-19, 2004

  2. Who I am. • Sr. Database Administrator • Information Technology Professional since 1978 • Employment History • Nielsen Media Research • The Capital Group • Software AG • Weyerhaeuser • Bigelow-Sanford

  3. E-Mail History • 1st Internet use of communication between computers? • 1st programmer to develop a program to send electronic messages? • 1st use of satellites for transferring data packets?

  4. Objective • To identify ways that Sybase applications written on a UNIX platform can send E-Mails

  5. Command xp_sendmail Windows NT only - Sends a message to the specified recipients. The message is either text or the results of a Transact-SQL query.

  6. Command xp_sendmail recipient [; recipient] . . . [, subject] [, cc_recipient] [, bcc_recipient] . [, {query | message}] [, attachname] [, attach_result = {true | false}] [, echo_error = {true | false}] [, include_file [, include_file] . . .] [, no_column_header = {true | false}] [, no_output = {true | false}] [, width] [, separator] [, dbuser] [, dbname] [, type] [, include_query = {true | false}]

  7. Command Sequence • xp_startmail • xp_sendmail • xp_stopmail

  8. Problem Statement Since the xp_sendmail feature of Sybase ASE on UNIX is not operational, how can E-Mails be sent from an application written on this platform?

  9. Goal To identify methods that can be used to simulate the active use of the xp_sendmail function.

  10. Methods • Issuing a UNIX mail command via xp_cmdshell • Calling a Stored Procedure that calls xp_cmdshell to issue the UNIX mail command • Calling a Stored Procedure that issues xp_sendmail on a ASA Server running on NT • Remotely executing xp_sendmail on ASA on NT machine

  11. Method 1: Issuing a UNIX mail command via xp_cmdshell

  12. Method 1: Issuing a UNIX mail command via xp_cmdshell declare @mail_string varchar(255) Select @subject = '/var/tmp/errormsg_01‘ , @recipient = 'Steve.Bradley@NielsenMedia.com‘ select @mail_string = '/usr/bin/mailx -s "D2DDM" ' + @recipient + ' < "' + @subject + '"' print @mail_string exec xp_cmdshell @mail_string 

  13. Method 1: Issuing a UNIX mail command via xp_cmdshell • Pros • Simplistic • Cons • Least security • Minimal message

  14. Method 2: Calling a Stored Procedure that calls xp_cmdshell to issue the UNIX mail command

  15. Method 2: Calling a Stored Procedure that calls xp_cmdshell to issue the UNIX mail command createproc sp_sendmail  (@recipient varchar(255) = NULL,      @subject varchar(255)    = NULL ) as declare @mail_string varchar(255) Select @subject = '/var/tmp/errormsg_01‘ , @recipient = 'Steve.Bradley@NielsenMedia.com‘ select @mail_string = '/usr/bin/mailx -s "D2DDM" ' + @recipient + ' < "' + @subject + '"' print @mail_string exec xp_cmdshell @mail_string  return

  16. Method 2: Calling a Stored Procedure that calls xp_cmdshell to issue the UNIX mail command • Pros • Security can be provided via execute authority • Variables can be used to pass message, address, and subject information • Use of distribution lists • Cons • Called stored procedure would need to be in another database with limited access

  17. Method 3: Calling a Stored Procedure that issues xp_sendmail on a ASA Server running on NT

  18. Method 3: Calling a Stored Procedure that issues xp_sendmail on a ASA Server running on NT • Pros • Security can be provided via remote server • Variables can be used to pass message, address, and subject information • Use of distribution lists • Cons • Called stored procedure would need to be maintained in sybsystemprocs

  19. Method 4: Remotely executing xp_sendmail on ASA on NT machine

  20. Method 4: Remotely executing xp_sendmail on ASA on NT machine • Pros • Security can be provided via remote server login that has limited authorization • Variables can be used to pass message, address, and subject information • Use of distribution lists • Cons • User/applications must have ability to make calls to remote server • Limited security

  21. Comparison of Methods • Issuing a UNIX mail command via xp_cmdshell • Calling a Stored Procedure that calls xp_cmdshell to issue the UNIX mail command • Calling a Stored Procedure that issues xp_sendmail on a ASE Server running on NT • Remotely executing xp_sendmail on SQL Anywhere on NT machine

  22. Bottom Line • Security • Costs • Environment

  23. Business Cases • Database Administration Event Notification • Client Event Notification • Application Event Notification

  24. Expand to Web Services Create a Web Service that can be utilized to handle E-Mail processing that is callable from within ASE.

  25. Real-Time vs. Right Time E-Mail • Real-Time implies that E-Mail should be sentat the time of the event • Right-Time means that the appropriate time for sending the E-Mail must be determined by the event

  26. Questions

More Related