Home > Developer > SQL Server
/* SQL Server 2000 is used. The system cdosys is used to send an HTML-type email. This script uses cdosys to send a mail. no mapi profile required. */use northwindset quoted_identifier on goset ansi_nulls on gocreate procedure [DBO]. [sp_send_mailhtml] @ smtpserver varchar (128), @ from varchar (128), @ to varchar (128), @ CC varchar (128), @ BCC varchar (128 ), @ subject varchar (124) = "", @ query varchar (4000) = ""/***** date: March 2008 * Author: d Aniel.eyer@free.fr * Project: Just for fun! * Location: Any database * permissions: Public execute ** description: send query result as HTML mail *****/as -- mail declaration declare @ imsg int declare @ HR int declare @ source varchar (255) Declare @ description varchar (500) declare @ output varchar (1000) -- HTML declaration declare @ columns varchar (8000) Declare @ colheader varchar (8000) Declare @ sqlcmd varchar (8000) Declare @ htmlbody varchar (80 00) -- ************** create the CDO. message object *********************** exec @ hR = sp_oacreate 'cdo. message ', @ imsg out -- **************** refreshing the message object ***************** * -- this is to configure a remote SMTP server. -- http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/cdosys/html/_ cdosys_schema_configuration_sendusing.asp exec @ hR = sp_oasetproperty @ imsg, 'configuration. fields ("http://schemas.microsoft.com/cdo/configuration/sendusing "). value', '2' -- this is to configure the server name or IP address. -- replace mailservername by the name or IP of your SMTP server. exec @ hR = sp_oasetproperty @ imsg, 'configuration. fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver "). value', @ smtpserver -- save the invocations to the message object. exec @ hR = sp_oamethod @ imsg, 'configuration. fields. update', null -- set the e-mail parameters. exec @ hR = sp_oasetproperty @ imsg, 'to', @ to Exec @ hR = sp_oasetproperty @ imsg, 'from', @ from if (@ CC is not null) exec @ hR = sp_oasetproperty @ imsg, 'cc', @ CC if (@ BCC is not null) exec @ hR = sp_oasetproperty @ imsg, 'bcc ', @ BCC exec @ hR = sp_oasetproperty @ imsg, 'subobject', @ subject -- if you are using HTML e-mail, use 'htmlbody' instead of 'textbody '. -- exec @ hR = sp_oasetproperty @ imsg, 'htmlbody ', @ body /************************************* * **********************************/-- drop temporary tables used. if exists (select * From tempdb. DBO. sysobjects where name = '## temphtml1') Drop table # temphtml1 if exists (select * From tempdb. DBO. sysobjects where name = '## temphtml2') Drop table # temphtml2 -- prepare query set @ sqlcmd = 'select * into # temphtml1 from ('+ @ query + ') as T1 'execute (@ sqlcmd) -- prepare columns details select @ columns = coalesce (@ columns + '+ ''</TD> <TD>'' + ','') + 'rtrim (convert (varchar (100), isnull ('+ column_name +', ''') 'from tempdb. information_schema.columns where table_name = '# temphtml1' -- prepare column header set @ colheader = '<tr bgcolor = # edfedf align = left> 'select @ colheader = @ colheader +' <TD> <B> '+ column_name +' </B> </TD> 'from tempdb. information_schema.columns where table_name = '# temphtml1 'set @ colheader = @ colheader +' </tr> '-- prepare final output set @ sqlcmd = 'select' <tr> <TD> ''+ '+ @ columns + ''' </TD> </tr> ''into ## temphtml2 from ## temphtml1 'execute (@ sqlcmd) -- set @ finalhtmlout = set @ htmlbody = '<HTML> <body> <style type = "text/CSS" Media = "all">' + 'table {margin-bottom: 2em; border-collapse: Collapse} '+ 'td, Th {border = 1 solid #999; padding: 0.2em 0.2em; font-size: 12 ;} '+' </style> <Table width = "100%"> '+ @ colheader select @ htmlbody = @ htmlbody + [</TD> </tr>] From # temphtml2 set @ htmlbody = @ htmlbody + '</table> </body>
In addition, the built-in system stored procedures of sqlserver2005Sp_send_dbmail
However, you must use the database mail Configuration Wizard, the SQL Server peripheral application configurator tool orSp_configureEnable database mail.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
