PCB SQL SERVER 邮箱配置与发邮件

<span style="color: #008080">--</span><span style="color: #008080">开启发邮件功能</span>
<span style="color: #0000ff">exec</span> sp_configure <span style="color: #ff0000">‘</span><span style="color: #ff0000">show advanced options</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">1</span>
<span style="color: #0000ff">reconfigure</span> <span style="color: #0000ff">with</span><span style="color: #000000"> override 
</span><span style="color: #0000ff">go</span>
<span style="color: #0000ff">exec</span> sp_configure <span style="color: #ff0000">‘</span><span style="color: #ff0000">database mail xps</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">1</span>
<span style="color: #0000ff">reconfigure</span> <span style="color: #0000ff">with</span><span style="color: #000000"> override 
</span><span style="color: #0000ff">go</span>

  二.邮箱配置

      1.代码创建邮箱配置

<span style="color: #008080">--</span><span style="color: #008080">创建邮件帐户信息</span>
<span style="color: #0000ff">exec</span><span style="color: #000000"> msdb.dbo.sysmail_add_account_sp
  </span><span style="color: #008000">@account_name</span> <span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">pcbren阿里云邮箱帐户</span><span style="color: #ff0000">‘</span>,             <span style="color: #008080">--</span><span style="color: #008080"> 邮件帐户名称  </span>
  <span style="color: #008000">@description</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">阿里云邮箱</span><span style="color: #ff0000">‘</span>,                      <span style="color: #008080">--</span><span style="color: #008080"> 描述</span>
  <span style="color: #008000">@email_address</span> <span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">pcbren@aliyun.com</span><span style="color: #ff0000">‘</span>,            <span style="color: #008080">--</span><span style="color: #008080"> 发件人邮件地址</span>
  <span style="color: #008000">@replyto_address</span> <span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">pcbren@aliyun.com</span><span style="color: #ff0000">‘</span>,          <span style="color: #008080">--</span><span style="color: #008080"> 回复邮件地址</span>
  <span style="color: #008000">@display_name</span> <span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">pcbren</span><span style="color: #ff0000">‘</span>,                        <span style="color: #008080">--</span><span style="color: #008080"> 发件人显示名称</span>
  <span style="color: #008000">@MAILSERVER_NAME</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">smtp.aliyun.com</span><span style="color: #ff0000">‘</span>,           <span style="color: #008080">--</span><span style="color: #008080"> 邮件服务器地址</span>
  <span style="color: #008000">@PORT</span> <span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">25</span>,                                      <span style="color: #008080">--</span><span style="color: #008080"> 邮件服务器端口 </span>
  <span style="color: #008000">@USERNAME</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">pcbren@aliyun.com</span><span style="color: #ff0000">‘</span>,                <span style="color: #008080">--</span><span style="color: #008080"> 用户名 </span>
  <span style="color: #008000">@PASSWORD</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">**************</span><span style="color: #ff0000">‘</span>,                   <span style="color: #008080">--</span><span style="color: #008080"> 密码  </span>
  <span style="color: #008000">@enable_ssl</span> <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">0</span>                                 <span style="color: #008080">--</span><span style="color: #008080">SSL   如果是QQ邮箱需设置为1</span>
<span style="color: #0000ff">GO</span>
<span style="color: #008080">--</span><span style="color: #008080">数据库配置文件</span>
<span style="color: #0000ff">exec</span><span style="color: #000000"> msdb.dbo.sysmail_add_profile_sp
  </span><span style="color: #008000">@profile_name</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">pcbren邮箱</span><span style="color: #ff0000">‘</span>,                  <span style="color: #008080">--</span><span style="color: #008080"> 配置名称 </span>
  <span style="color: #008000">@description</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">数据库邮件配置文件</span><span style="color: #ff0000">‘</span>              <span style="color: #008080">--</span><span style="color: #008080"> 配置描述</span>
<span style="color: #0000ff">go</span>
 
<span style="color: #008080">--</span><span style="color: #008080">用户和邮件配置文件相关联</span>
<span style="color: #0000ff">exec</span><span style="color: #000000"> msdb.dbo.sysmail_add_profileaccount_sp
  </span><span style="color: #008000">@profile_name</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">pcbren邮箱</span><span style="color: #ff0000">‘</span>,                  <span style="color: #008080">--</span><span style="color: #008080"> 配置名称</span>
  <span style="color: #008000">@account_name</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">pcbren阿里云邮箱帐户</span><span style="color: #ff0000">‘</span>,         <span style="color: #008080">--</span><span style="color: #008080"> 邮件帐户名称    </span>
  <span style="color: #008000">@sequence_number</span> <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span>                          <span style="color: #008080">--</span><span style="color: #008080"> account 在 profile 中顺序（默认是1）</span>
<span style="color: #0000ff">go</span>

      2.界面创建邮箱配置效果

  三.测试发送邮件

     1.代码实现:

<span style="color: #0000ff">exec</span><span style="color: #000000"> msdb.dbo.sp_send_dbmail
</span><span style="color: #008000">@profile_name</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘pcbren邮箱</span><span style="color: #ff0000">‘</span>, <span style="color: #008080">--</span><span style="color: #008080">配置名称</span>
<span style="color: #008000">@recipients</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">yadnfku@foxmail.com</span><span style="color: #ff0000">‘</span>, <span style="color: #008080">--</span><span style="color: #008080">收件名称</span>
<span style="color: #008000">@body_format</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">HTML</span><span style="color: #ff0000">‘</span>, <span style="color: #008080">--</span><span style="color: #008080">内容格式</span>
<span style="color: #008000">@subject</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">文章标题</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
</span><span style="color: #008000">@body</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘pcbren致力于PCB工程自动化研究</span><span style="color: #ff0000">‘</span>

      2.界面实现

 四.发送邮件尺寸设置

 五.发送邮件记录相关表

<span style="color: #008080">--</span><span style="color: #008080">发送邮件记录相关表</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> msdb.dbo.sysmail_allitems    <span style="color: #008080">--</span><span style="color: #008080">查看所有邮件状态</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> msdb.dbo.sysmail_faileditems <span style="color: #008080">--</span><span style="color: #008080">失败状态的消息</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> msdb.dbo.sysmail_unsentitems <span style="color: #008080">--</span><span style="color: #008080">看未发送的消息</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> msdb.dbo.sysmail_sentitems <span style="color: #008080">--</span><span style="color: #008080">查看已发送的消息</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> msdb.dbo.sysmail_event_log  <span style="color: #008080">--</span><span style="color: #008080">记录日志</span>

PCB SQL SERVER 邮箱配置与发邮件

标签：src   pre   reply   效果   user   color   form   ESS   exe