sql server2008数据库邮件天善智能一家之言

BI同仁

天善智能致BI同仁善智能专注于商业智能和数据库性能优化，2011年9月由（Robay）组织成立，前期识在2年9通络来善年商智数库的战。前智总经作了23份了5陆续增加中...期I在的家，最是，料完全是因为天善体谅一些大学生、初学者想学习BI所有成员也经历过一段这样痛苦的时间，因此想通过这些小小的行为帮助到那些BI为仅仅做到这一点是不够的，更好的方式应该是让大家掌握一种无形的本领。这种本领可以描设性的建议，助天善团队壮大，提升天善团队，使天善迈向成阶段。写于2012年12月22日重生后的第一如何找到天善博客：(订阅本博客随时掌握天善动态，文档工具。目前已经600人订阅，还不订阅更待何时？)QA：http://q (任何技术问题，只要您认真提，天善一定认真答。QQ群——5群： 6群前4群基本满员，多达2000人，加入 数据库邮SQLServer2008数据库邮件是从SLSrvr库应用程序可以向用户发送电子邮件。邮件中可以包含查询结果，还可以包含来自网络中任何资源的文件。无需uk（展MI准(STSLSrvrAPISQLMailXPs选项可在此服务器上启用SQLMail(该选项默认是关闭的)。sp_configuresp_configure'showadvancedoptions',sp_configure'SQLMailXPs',Execdbo.sp_send_dbmail@profile_name='Andy的邮件配件', 这是从Select*From MSSQL数据库邮件系列一(环境搭建一、前数据库邮件是一种通过SQLServer2005DatabaseEngine发送电子邮件的企业解 什么的.也不用担心上层开发系统的开发时间了,减少了开发成本,而且数据库邮件是基于队列概念的,所以未发送的邮件是在队列中,等待下一次的安排,ServiceBroker的概MSSQL2005中,不需要像SQLMail2000中的方案一样使用扩展MAPI来实现发送邮件,不需要安装MAPI客户端就能发送邮件,他使用SMTP协议来发送邮件.为了完整的演示数据库邮件,我就自己搭建POP3SMTP环境了SMTP简单邮件传输协议):Internet传送到目标服务器的方式。SMTP在服务也就是说,我们通过TCP/IP簇里面的SMTP来发送接受电子邮件,所以SMTP是我们邮件的传输载体POP3:邮局协议3是检索电子邮件的标准协议。POP3协议控制POP3电子邮件客户端和电子邮件的服务器之间的连接。POP3POP3POP3电子邮 邮件,ISP解析了邮件的域以后转到 Step6:POP3提供了3种验证方式让你登录自己的邮箱,比如AD(ActiveDirectory),,以及DB:MSSQLSERVER2005我使用的是企业版OS:Windows2003(两台机器,我使用的是虚拟机进行的桥接),POP3服务,记得放碟子,或者使ISO来安装服务.2008的话,Addfeatures了呵呵.SMTP服务,因为条件原因必须使用SMTP和POP3在一台服务器. (可以自己写)选 应该可以获取邮件呢.如果没成功,在留言里面发问吧.至于为什么不用Exchange主要是方便配置啦.果第一个SMTP服务器失败的话,他会推到下一个来执行.灵活可以直接使用sp_send_dbmail来发送数据库邮件 数据库邮件还是有很多帮助的,首先我们可以利用数据库邮件定期一些DBA外出人员查看其DBA.总之场景自己可以多多的设想.本文的话先说了说环境的配置,数据库邮MSSQL数据库邮件系列二(SSMS一、前配置成功Mail以后查看任务管理器中的进程.另外的,sp_send_dbmail是手动发送邮件的过程,须显示的赋值才能发送,等配置完以后二、配置首先,因为邮件是基于servicesbroker发送的,须确保msdb数据库打开了service功能USEUSESELECT*FROMRECONFIGUREWITHOVERRIDERECONFIGUREWITHOVERRIDE--我们使用启用这个数据库ServiceBrokerEXECUTEdbo.sysmail_start_sp;件我们先选择第一个Radio,说明可以故障转移,当我们的一个SMTP服务器宕机异常的时候,自动的转移到下一个SMTP账户.帐户名的话就是在上幅图中的帐户名的名称显示.然后看到SMTP这块,电子邮件地址也就是接受发送邮件的地址.而答复电子邮件可以设置相同的电子邮件,服务器名称制定自己的主机IP或者主机名记得一定要有msdb中的角色INNERJOINsys.database_principalsASouONou.principal_id=WHERE(rl.type= msdbDatabasUserRole数据库角色的所有成员都可用。它们允许DatabasUserRole角色的所有成员使用该配置文件发送电子邮件。配置文件为msdbsysadmin固定服务器角色的成员: SELECT*FROMSELECT*FROMSELECT*FROMSELECT*FROMsysmail_faileditemsAINNERJOINsysmail_event_logBONSELECT*FROM SELECT*FROMsysmail_event_log最后就是使用sp_send_dbmail来发送邮件可以查阅三、结时性，也不智能化。下面简单介绍一下如何设置SQLServer的邮件通知Job完成后，自动发SQLServer2005的设置如下SQLServerSurfaceAreaConfiguration->SurfaceAreaConfigurationforfeatures->选择DatabaseMail->EnableDatabasemailstoredproceduresSQLManagementStudio->Management->Databasemail->ConfigureSMTPSQLServerAgent->Operators->建立一个新的建立一个新Job->在通知(Notifications)中，选 和刚才新建的Operator->选择当失败/成功/完成时发 才建立的profile。最后，别忘记重新启动SQLSQLServer2008的设置同2005一样，只不过没上面的简要说明恐怕对于初学者来说，还是比较难操作，下面SQLServer2008为例，附图说明SQLManagementStudio->Management->Databasemail->ConfigureSMTP 后，发送一封测试mail，来测试SMTP设置是否正SQLServerAgent->Operators->建立一个新的建立一个新Job->在通知(Notifications)中，选 和刚才新建的Operator->选择当失败/成功/完成由于本节不是专门Job ，所以如何建立Job，本文将不附图，下面附设置Operator的择到上面步骤中建立的profile（上文中为TJVictor 最后千万别忘记重新启动最后千万别忘记重新启动Job/viewspace-SQLServer如下：打开SQLServer“配置工具”中的“应用配置器”，选择“功能的应用配置器”。在弹 其中“Netsend地址”为IP地址。netsend命令，如：netsendiamNetSend命令格式：Netsend{name|*| [:name]|/users}·*netsendnetsendip重要：由于在修改了警报系统配置之后要重启SQLServer后才能起效，所以在修改完毕后一定要重新启动SQLServer。EXECmsdb.dbo.sp_send_dbmail@recipients=' @body用msdb.dbo.sp_send_dbmail测试发邮件@subject='测试发邮件sql查询',@query='SELECT*fromtest.dbo.good',@attach_query_result_as_file=1,@query_attaent_filename='Results.txt';DATABASEMAIL详--DATABASEMAIL--开启DATABASEEXECsp_configure'showadvanced',1;EXECsp_configure'DatabaseMailXPs',1;EXECsp_configure'showadvanced',0;--建立邮件EXECUTEmsdb.dbo.sysmail_add_account_sp@account_name='OPDBA',--发送邮件的账号@description'mailaccount账号说明（可省） _address='OP_DB @disy_name='xxxxxx',--显示的标题@mailserver_namexxx.xxx.xxx.xxx--建立配置EXECUTEmsdb.dbo.sysmail_add_profile_sp@profile_name'monitor',--配置文件名称@description='邮件配置文件';--文件说明（可省--关联账号和配置文EXECUTEmsdb.dbo.sysmail_add_profileaccount_sp@profile_name'monitor',--上面建立的配置文件名称@account_name='OPDBA',--上面配置的账号名称@sequence_number=1;--配置文件中的帐户的序列号USE[msdb]EXECmsdb.dbo.sp_set_sqlagent_properties@ , , ,N'REG_DWORD',1, , ,N'REG_SZ',N'monitor'--建立以上步骤后要重启 SERVER，比且测试发--建立操作EXECmsdb.dbo.sp_add_operator@name=N'db_monitor', '收取邮件的地DATABASEMAIL不同邮箱的邮件发送功能，并删除数据库邮件配置功能如下【邮件服务器地址 端号：587，@enable_ssl的值设置为：1】kalllm错误：由于邮件服务器故障，无法将邮件发送给收件人使用帐户10(2009-04-21T15:09:37发送邮件。异常邮件:答复或连接的主机没有反应，连接尝试失败。))解决如下:总结问题1说465和587端口多能发但是465不能问题2sqlserver在向导里修改@enable_ssl的值设置为：1时候，有时候不起作用。配置启用数据库邮件功--EXECsys.sp_configureN'showadvancedoptions',1EXECsys.sp_configure'DatabaseMailXPs',--@account_name@profile_nameN'gmail--数据库邮件帐@account_name=@account_name, y_nameN'gmailtest'@mailserver_name=N' Gmailsmtp服务器地址@port=456,--端(对于Gmail,如果25不通则改用456)@username=N'support02@',--Gmail邮件地址@password=N'xxxxxx',--Gmail邮件帐号@use_default_credentials=0,@enable_ssl1启用SSL--数据库邮件配置文@profile_name=@profile_name;--把数据库邮件帐号添加到邮件配置文件@profile_name=@profile_name,@account_name=@account_name,@sequence_number=1;--授予任意用户使用数据库邮件配置文件的@principal_name=N'guest',@profile_name=@is_default=@profile_name='gmail',@recipients @subjectN'testmailfromdatabase邮件@bodyN'thisisatestmail'--select*from--select*from--select*from--deleteDECLARE@account_name @account_name= @profile_name=--从邮件配置文件中删除数据库邮件@profile_name=@profile_name,@account_name=--删除数据库邮件帐@account_name=@account_name;--删除数据库邮件配置文EXEC@profile_name=--禁用数据库邮件功EXECsys.sp_configure'DatabaseMailXPs',EXECsys.sp_configureN'showadvancedoptions',SQLMail配置，邮件服务器地址的/*SQLServer并没有内置邮件服务器（MailServer），它跟我们发送邮件一样，需要用户名和通过SMTP（SimpleMessageTransferProtocol）去连接邮件服务器。我们想让SQLServer来发送邮件，首先要告诉它用户名称，，服务--下面是具体的配置邮件--在sa系统帐户下运行--1启用SQLServer2005execsp_configure'showadvancedoptions',1execsp_configure'DatabasemailXPs',1--2在SQLServer2005中添加邮件帐户exec 邮件帐户名称（SQLServer使用

==

= --邮件服务器地

邮件协议（SQL2005 --邮件服务器端 = -,@use_default_credentials= = =--3在SQLServer2005中添加execmsdb..sysmail_add_profile_sp@profile_name='dba_profile' profile名,@description='dbamailprofileprofile =在SQLServer2005中映射account和execmsdb..sysmail_add_profileaccount_sp@profile_name 'dba_profileprofile名称profile中顺

account,@sequence_number account--5利用SQLServer2005DatabaseMailexecmsdb..sp_send_dbmail@profile_name= profile = '-- ='SQLServer2005Mail测试 = oMail!测试 ,@body_format= --6.查看邮件发送情况select*fromsysmail_allitemsselect*fromsysmail_mailitemsselect*from--如果不是以sa帐户发送邮件，则可能会出现错--Msg229,Level14,State5,Proceduresp_send_dbmail,Line这是因为，当前SQLServer登陆帐户（login），在msdb数据库中没有发送数据库邮件的权限，需要加入msdb数据库用户，并通过加入sp_addrolemember角色赋予权限。假设该SQLServer登陆帐户名字为--use--createuserdbaforlogin--execdbo.sp_addrolemember = @membername=--此时，再次发送数据库邮件，仍可能有错--Msg14607,Level16,State1,Proceduresp_send_dbmail,Line--profilenameisnot--虽然，数据库用户“dba”已经在 中拥有发送邮件的权限了，但这还不够，他还需要有使用“dba_profile”的权限--use--execsysmail_add_principalprofile_sp@principal_name= = =--从上面的参数@is_default=1可以看出，一个数据库用户可以在多个mailprofile拥有发送权看里面有没有。如果有就说明邮件已经发出检查是不是你的邮件服务器被列为了邮件服务器select*fromsysmail_allitemsselect*fromsysmail_mailitemsselect*fromsysmail_event_logselect*fromsysmail_sentitems--已发送的邮件select*fromsysmail_faileditems--失败的邮件select*fromsysmail_unsentitems未发送的邮件l ere展一、启动 @user和@password都是可选 二、停止 xp_sendmail{[@recipients =]'copy_recipients =]'attach_value'] 参 说 收件人，中间用逗号分 要发送的信 确定执行并依附邮件的有效查询，除触发器中的插入表及删除表外，此查询能任何对 @copy_recipients抄送 @attach_results指定查询结果做为附件发送 四、阅读邮件收件箱中的xp_readmail[[@msg_id =]'suppress_attach'] [;...n]'output] 参数说 邮件信息时跳过的字节数，用于顺序获取邮件信息段h ks =]'message_number' 六、删除邮 执行过程msdb.dbo.sysmail_add_profile_sp以创建配置文件，要指定以下内容：配置文件的说明（可选）对每个帐户执 msdb.dbo.sysmail_add_profileaccount_sp，以将帐户添加到配置文件中使用数据库邮件配置向导或数据库邮件过程可以创建的数据库邮件配置文件，用于发送简单邮件传输议)使用Transact-SQL创建数据库邮件配置文执行过程msdb.dbo.sysmail_add_profile_sp以创建配置文件，要指定以下内容：配置文件的说明（可选）对每个帐户执 msdb.dbo.sysmail_add_profileaccount_sp，以将帐户添加到配置文件中示以下示例将创建一个数据库邮件帐户和数据库邮件配置文件。然后，该示例将帐户添加到配置文件msdb数据库中的DBMailUsers数据库角色授予对该配置文件的权限--CreateaDatabaseMailaccount@account_name='AdventureWorksAdministrator',@description='Mailaccountforadministrativee-mail.', _address='dba@A @replyto_address='danw@A @disy_name='AdventureWorksAutomatedMailer',@mailserver_name='smtp.A ';--CreateaDatabaseMailprofile@profile_name='AdventureWorksAdministratorProfile',@description='Profileusedforadministrativemail.';--AddtheaccounttotheEXECUTEmsdb.dbo.sysmail_add_profileaccount_sp@profile_name='AdventureWorksAdministratorProfile',@account_name='AdventureWorksAdministrator',@sequence_number=1;--GrantaccesstotheprofiletotheDBMailUsersroleEXECUTEmsdb.dbo.sysmail_add_principalprofile_sp@profile_name='AdventureWorksAdministratorProfile',@principal_name='ApplicationUser',@is_default=1早在早在sqlserver2000中可以通过sqlmail给指定的邮件地址发邮件。SQLSERVER2005中我们仍外可以用sqlSQLSERVER2005中又提供一个新数据库邮件功能也可以发送和接收邮件。这里以数据库邮件为例进行说 配置器将这两个功能组件启用。启用之后进入SQLSERVER2005管理界面(SQLSERVER2005MANGEMENT我们可以在SQL中通过刚才的配置来收发邮件。在sqlserver2000中，通过xp_snedmail以及以xp_开头的 sp_send_dbmail发送电子邮件年，用法如下spsenddbmail[[@profilename=]'profilename'][,[@recipients=]'recipients[;...n]'][,[@copyrecipients=]'copyrecipient[;...n]'[,[@blind_copy_recipients=]'blind_copy_recipient[;...n]'][,[@subject=]'subject'][,[@body=]'body'[,[@bodyformat=]'bodyformat'][,[@importance=]'importance'][,[@sensitivity=]'sensitivity'[,[@file_atta ents=]'atta ent[;...n]'][,[@query=]'query'][,[@executequerydatabase=]'executequerydatabase'[,[@attachqueryresultasfile=]attachqueryresultasfile][,[@query_attaent_filename=]query_attaent_filename][,[@query_result_header=]query_result_header][,[@queryresultwidth=]queryresultwidth[,[@queryresultseparator=]'queryresultseparator'][,[@excludequeryoutput=]excludequeryoutput][,[@append_query_error=]append_query_error][,[@query_no_truncate=]query_no_truncate]@mailitem_idmailitem_idOUTPUTsp_send_mail返回1表示成功，0表示失败sp_send_dbmail参数解释[@profile_name=]发送邮件的配置文件的名称。profile_namesysnameNULL。profile_name必须是现有数据库邮件未指定profile_name，则sp_send_dbmail使用当前用户的默 msdb数据库的默认公共配置文件。如果用户没有默认的 定@profile_name。[@recipients=][@copyrecipients=]'copy@recipients、@copy_recipients@blind_copy_recipients中的一sp_send_dbmail将返回错误[@blindcopyrecipients=]'blindcopy@recipients、@copy_recipients@blind_copy_recipientssp_send_dbmail[@subject=] 的类型为nvarchar(255)。如果未指 ，则默认为SQLServerMessage[@body=]nvarchar(max)NULL[@body_format=]可能包含下列值之一(TEXT)：[@importance=][@sensitivity=][ ents=] [@query=] 中的局部变量不可用于查询(注意：发邮件的用户必须有执行@query的权限[@execute_query_database=][@attach_query_result_as_file=]指定查询结果集是否作为附件返回。attach_query_result_as_filebit00@body1，结果作为附件返回。只有在指定@query时，[@query entfilename=]query ent ent_filename的数据类型为nvarchar(255)，默认值为NULL。如果0attach_query_result1NULL[@query_result_header=]指定查询结果是否包含列标题。query_result_headerbit1，则查询结果包含列标题。如果该值为0，则查询1@query[@query_result_width=]32767@query[@queryresultseparator=]'queryresultchar(1)（空格[@excludequeryoutput=]excludequery指定是否使用电子邮件返回查询执行的输出。exclude_query_outputbit00sp_send_dbmail储过程的执行将在控制台上打印作为查询执行结果而返回的消息。当此参数为1时，sp_send_dbmail [@append_query_error=]@query参数中指定的查询返回错误时发送电子邮件。exclude_query_outputbit0。[@query_no_truncate=]数据类型）的选项执行查询。设置该选项后，查询结果将不包含列标题。query_no_truncatebit。当该值为0或未指定时，25610[@mailitemid=]mailitemid[OUTPUTmailitem_id。mailitem_idint以sp开头的一些与数据邮件相关的过程 在msdb数据库中，msdb数据库中的Databas User数据库角色的所有成员对sp_send_dbmail1、 配置器中启用数据库邮3、建立账号，使他成为msdb中Databas User数据库角色所成员4、修改据库管理节点下配置数据库邮件配置设置，使刚建立的账 使用刚才建立的配置文5、以刚建立的用户登陆use--基本的发送语EXECspsenddbmail@profilename='xxadam',@recipients=' ='@body= --包含查EXECspsenddbmail@profilename='xxadam',@recipients='xx @query='select*fromdemo--包含EXECspsenddbmail@profilename='xxadam',@recipients=' @body='有附件，请查收', --将查询作为附EXECspsenddbmail@profilename='xxadam',@recipients='xx @body查询结果在附件中@query='select*from@attach@attachqueryresultasfile=1, SQL(1)xp_instance_regwrite是好用的，其作用就是改表,不过奇怪的是为什么帮助里找不到相关说(2)表 所以执行完xp_instance_regwrite过程，其最终结果可用RegEdit看表项立即改变了同时如果已经登录了，可以在Server上右键“属性”中“安全性”看到“验证”为"SQL Windows"!同时其实此选项还不起作用，只有重起机器(注销没用)或将"SQLServer"服务关闭后再启 另外我是想一旦启动EnterPriseManager后，任何人点击我"SQLServer组"下的"Server"时我不希望他能看到我的“server”下的展开内容但因为是混合认证模式，所以他如果已经通过了WindowsNT认证，就可以看见了，那非我所欲也！所以我希望他"SQLServer验证"登录，因为我都加了了！所以我可手动更改"编辑SQLServer属性.."将“使用Windows验证”改为"使用SQLServer认证"，那样他即使已经通过了WindowsNT认证，但还是进不了我的数据库!!一些愚见，与大家共勉g用或!EXECmaster.dbo.sp_MSsetalertinfo@pagersendsubjectonly=0 EXECmaster.dbo.xp_instance_regwriteN'HKEY_LOCAL_MACHINE启用警报系统【启用邮件 邮件配置文件【邮件配置里面作用如下:通过表启用以下配语 sp_configure[[@configname=]'option_name'[,[@configvalue=]'value']]参[@configname=]配置选项的名称。option_name的数据类型为varchar(35)，默认值为NULL。SQLServer数据库引擎能够有关可用配置选项及其设置的信息，请参阅设置服务器配置选[@configvalue=]新的配置设置。value的数据类型为int，默认值为NULL。最大值取决于各个选若要查看每个选项的最大值，请参阅 视图 um列实例如下功能说明 使用介修改说明：CreatebyLYon2011-010-说明 扩展过程将命令字符串作为操作系统命令 执行 所以一旦 Server管理员帐号（如sa）被攻破，那么者就可以利用在 Server中执行操作系统命令，如：创建系统管理员，也就意味着系统的最高权已在别人的掌控之中。由于存在安全隐患，所以在SQLServer2005中，xp_cmds .打开应用配置器—>功能的应用配置器—>实例名Database 启功能说明：允许配置高级修改说明：CreatebyLYon2011-010-EXECsp_configure'showadvancedoptions',1RECONFIGURE--EXECsp_configure'xp_cmds 1启用xp_cmdsRECONFIGURE--重新配置Exec 'queryuser'--执行想要的 语 修改说明：CreatebyLYon2011-010-08EXECsp_configureshowadvancedoptions'1--RECONFIGURE--EXECsp_configure'xp_cmds ',0 --重新配 生成Windows命令s并以字符串的形式传递以便执行。任何输出都作为文本的行返回xp_cmds扩展过程将命令字符串作为操作系统命令s执行，并以文本行的形式返回所有输出。xp_cmdsSQLServer管理员帐号（sa）被攻破，那么xp_cmdsSQLServer中执行操作系统命令，如：创建系统管理员，也就意味着SQLServer2005xp_cmds默功能说明：重新启动SQLSERVERAgent令修改说明：CreatebyLYon2011-010-08EXECsp_configure'showadvancedoptions',1RECONFIGURE--EXECsp_configure'xp_cmds 1启用xp_cmdsRECONFIGURE--重新配置 --1.SQLSERVERAGENT服务的当前状态--2.启动或SQLSERVERAGENT服务EXECmaster.dbo.xp_servicecontrolN'START',EXECmaster.dbo.xp_servicecontrolN'STOP',--3.SQLSERVERAGENT服务的启动类型还可以使用 的方功能说明：重新启动SQLSERVERAgent令修改说明：CreatebyLYon2011-010-08EXECsp_configure'showadvancedoptions',1RECONFIGURE--EXECsp_configure'xp_cmds 1启用xp_cmdsRECONFIGURE--重新配置 SQLAgent$SQL2008' SQLSQL实功能说明：启用SQLSERVER邮件修改说明：CreatebyLYon2011-010-USEEXECSP_CONFIGURE'SHOWADVANCEDOPTIONS',1 EXECSP_CONFIGURE'DATABASEMAILXPS',1 --启用DATABASEMail功能修改说明：CreatebyLYon2011-010-08IFEXISTS(SELECTnameFROM[msdb].[dbo].[sysmail_account]WHEREname=N'P.C.W.L')EXEC@ACCOUNT_NAME 邮件帐户名称（SQLSERVER使用EXEC 邮件帐户名称（SQLSERVER使用 '-- ='系统管理员 = = --邮件服务器地 邮件协议（SQL2005只支持 --邮件服务器端 ' = -,@USE_DEFAULT_CREDENTIALS= = =--EXEC --发件人邮件地

==

发件人 ='SQLTEST=--邮件服务器地=--邮件协议（SQL2005只支持=--邮件服务器端==- = =(MAPI)，因此数据库邮件配置文件不是MAPI邮件配置文件。数据库邮件不使用SQLMail配置文件。数据库邮件配置文件可以保存任意数目的数据库邮件帐户。数据库邮件过程可以通过配置文件名称或此过程生成的配置文件ID来配置文件】修改说明：CreatebyLYon2011-010-EXEC@profile_name 邮件帐户名称（SQLSERVER使用exec@profile_name profile,@description='dbamailprofileprofile =功能说明：在SQLServer中映射account和profile修改说明：CreatebyLYon2011-010-08execmsdb..sysmail_add_profileaccount_sp 'dba_profileprofile名称 account,@sequence_number account在profile功能说明：利用SQLServer2005DatabaseMail功能发送邮件。修改说明：CreatebyLYon2011-010-08execmsdb..sp_send_dbmail@profile_name='dba_profile' =' ='SQLServer2005MailTest ='直接用代码测试如下 ,@body_format= 功能说明：查看邮件发送情况修改说明：CreatebyLYon2011-010-select*fromsysmail_allitemsselect*fromsysmail_mailitemsselect*from--如果不是以sa帐户发送邮件，则可能会出现错onobject'sp_send_dbmail',database'msdb',schema'dbo'.--这是因为，当前SQLServer登陆帐户（login），在msdb数据库中没有发送数据库邮件的权限，需要加入msdb数据库用户，并通过加入sp_addrolemember角色赋予权限。假设该SQLServer登陆帐户名字为--use--createuserdbaforlogin--exec = --@membername=--此时，再次发送数据库邮件，仍可能有错--Msg14607,Level16,State1,Proceduresp_send_dbmail,Line119profilenameisnotvalid--虽然，数据库用户“dba”已经在msdb中拥有发送邮件的权限了，但这还不够，他还需要有使用“dba_profile”的权--use--execsysmail_add_principalprofile_sp@principal_name= = =--从上面的参数@is_default=1可以看出，一个数据库用户可以在多个mailprofile拥有发送权自动发送邮件功能SQL修改说明：CreatebyLYon2011-010-08EXECmaster.dbo.sp_MSsetalertinfo@pagersendsubjectonly=0 EXECmaster.dbo.xp_instance_regwriteN'HKEY_LOCAL_MACHINE启用警报系统【启用邮件

邮件配置文件【邮件配置里面功能说明：重新启动SQLSERVERAgent令修改说明：CreatebyLYon2011-010-08EXECsp_configure'showadvancedoptions',1RECONFIGURE--EXECsp_configure'xp_cmds 1启用xp_cmdsRECONFIGURE--重新配置 SQLAgent$SQL2008' 功能说明：在SQLSERVER中操作员修改说明：CreatebyLYon2011-010-EXECMSDB..sp_delete_operator@name EXECmsdb.dbo.sp_add_operator@name=N'liangyongOperateR', Job中添加操作员的操作里面所使用的操作员必须是前面步骤所创建功能说明参数说明 指定何时将该作业的项放 Windows应用程序日志eventlog_level的数据类型为int【从不1成功时2失败时3始终修改说明：CreatebyLYon2011-010-EXECmsdb.dbo.sp_update_job@job_name=N'CDC 在客户联网请况下，ETL出错后,自系统管理员指定的邮箱发送错误日志邮功能说明：启用SQLSERVER邮件功修改说明：CreatebyLYon2011-010-10EXECSP_CONFIGURE'SHOWADVANCEDOPTIONS',1 EXECSP_CONFIGURE'DATABASEMAILXPS',1 功能说明：创建新的邮件帐户参数说明:SYSMAIL_DELETE_ACCOUNT_SP删除一个新的数据库邮件帐 创建一个新的数据库邮件帐户，用于保存有关 帐户信息修改说明：CreatebyLYon2011-010-IFEXISTS(SELECTFROM[msdb].[dbo].[sysmail_account]EXECmsdb..sysmail_delete_account_sp@ACCOUNT_NAME='BSERPRDC_ETLErrorMail'-邮件帐户名EXEC 使用 _ADDRESS='bser Y_NAME='系统管理员', --发件人@REPLYTO_ADDRESS=NULL,@DESCRIPTION=@MAILSERVER_NAME --邮件服务器地@MAILSERVER_TYPE @PORT --邮件服务器端@USERNAME='bser @PASSWORD='rdcmail', --@USE_DEFAULT_CREDENTIALS=0,@ENABLE_SSL=0,@ACCOUNT_ID=NULL功能说明：创建新的数据库邮件配置文件参数说明:sysmail_delete_profile_sp删除一个新的数据库邮件配置 修改说明：CreatebyLYon2011-010-10IFEXISTS(SELECTFROMWHEREEXECmsdb..sysmail_delete_profile_sp@profile_name='BSERP_RDC_ETLErrorProfile'-邮件配置文EXEC@profile_name='BSERP_RDC_ETLErrorProfile', --profile名称@description='BSERPRDC产品数据库邮件配置文件', @profile_id=null功能说明：映射account和修改说明：CreatebyLYon2011-010-EXEC@profile_name='BSERP_RDC_ETLErrorProfile',--profile名称@account_name='BSERP_RDC_ETLErrorMail',--account名称@sequence_number=1 --account在profile中顺序功能说明：启用警报系统里面的邮件参数说明:xp_instance_regwrite修改表修改说明：CreatebyLYon2011-010-10 EXECmaster.dbo.sp_MSsetalertinfo@pagersendsubjectonly=0 Profile', , ,N'REG_DWORD',EXECmaster.dbo.xp_instance_regwriteN'HKEY_LOCAL_MACHINE启用警报系统【启用邮件置里面的SQLMailProfile

邮件配置文件【邮功能说明：重新启动SQLSERVERAgent令改说明：CreatebyLYon2011-010-EXECSP_CONFIGURE'SHOWADVANCEDOPTIONS',1 RECONFIGURE--EXECSP_CONFIGURE'xp_cmds ',1 RECONFIGURE--重新配置EXECmaster..xp_cmds'netstopSQLAgent$SQL2008'EXECmaster..xp_cmds'netstartSQLAgent$SQL2008'功能说明：添加操作员功pager_days的数据类型为tinyint，默认值为0，表示操作员无法接收寻呼。有效值介于0和127之间。将所需日期的对应值相加即可计算出pager_days的值。例如，从星期一到星期五是2+4+8+16+32=62修改说明：CreatebyLYon2011-010-IFEXISTS(SELECTFROMmsdb.dbo.sysoperatorsEXECmsdb..sp_delete_operator@name EXECmsdb.dbo.sp_add_operator@name=N'BSERPRDCOperate', 仅可以对SQLMail使用电子邮件别名。必 功能说明：在Job中添加操作员的参数说明 指定何时将该作业的项放 Windows应用程序日志eventlog_level的数据类型为int【从不1成功时2失败时3始终】修改说明：CreatebyLYon2011-010-10EXEC 对应的操作 功能说明：启用ETL错误日志邮件功修改说明 CreatebyLYon2011-10-IFEXISTS(SELECTFROMWHEREID=OBJECT_ID(N'P_InvocationETLErrorLogMail')ANDOBJECTPROPERTY(ID,N'IsProcedure')=1)DROPPROCEDURESETANSI_NULLSSETQUOTED_IDENTIFIERONCREATEPROCEDURE[dbo].[P_InvocationETLErrorLogMail]@Advanced_OptionsVARCHAR(35)='SHOWADVANCED--sp_configure允许配置高级选@Database_MailVARCHAR(35)='DATABASEMAIL--sp_configure启用DATABASEMail VARCHAR(35)= --sp_configure启用 功@SYSMail_AccountVARCHAR(50)='BSERP_RDC_ETLErrorMail', _AddressVARCHAR(50)='bser y_NameVARCHAR(50)='系统管理员 --发件人@MailServer_NameVARCHAR(50 --邮件服务地@MailServer_TypeVARCHAR(50)='SMTP', @UserNameVARCHAR(50)='bser @PassWordVARCHAR(50)='rdcmail', --@SYSMail_ProfileVARCHAR(50)='BSERP_RDC_ETLErrorProfile', 置文件名@Profile_DescriptionVARCHAR(50'BSERPRDC产品数据库邮件配置文件',--邮件配置文@Net_Stop_AgentVARCHAR(50)='netstopSQLAgent$SQL2008', 启动服@Sys_OperatorsNameVARCHAR(50 --操作员称 --操作员邮@Job_NameVARCHAR(50 --Job的名 INT --Job在什功能说明：启用SQLSERVER邮件功修改说明：CreatebyLYon2011-010-10EXECmaster..sp_configure@Advanced_Options,1 EXECmaster..sp_configure@Database_Mail,1 功能说明：创建新的邮件帐户参数说明:SYSMAIL_DELETE_ACCOUNT_SP删除一个新的数据库邮件帐 创建一个新的数据库邮件帐户，用于保存有关 帐户信息修改说明：CreatebyLYon2011-010-IFEXISTS(SELECTWHEREname=@SYSMail_Account)EXECmsdb..sysmail_delete_account_sp@ACCOUNT_NAME@SYSMail_Account--邮件帐户名EXEC@ACCOUNT_NAME 邮件帐户名称（SQLSERVER使用 _ADDRESS Y_NAME @REPLYTO_ADDRESS=NULL,@DESCRIPTION=NULL,@MAILSERVER_NAME=@MAILSERVER_TYPE=@PORT=@USERNAME @PASSWORD=@USE_DEFAULT_CREDENTIALS=-@ENABLE_SSL=@ACCOUNT_ID=功能说明：创建新的数据库邮件配置文件参数说明:sysmail_delete_profile_sp删除一个新的数据库邮件配置 修改说明：CreatebyLYon2011-010-10IFEXISTS(SELECTWHEREname=@SYSMail_Profile)EXECmsdb..sysmail_delete_profile_sp@profile_name@SYSMail_Profile邮件配置EXEC@profile_name --邮件配置文件名@description --邮件配置文件描@profile_id=功能说明：映射account和修改说明：CreatebyLYon2011-010-EXECmsdb..sysmail_add_profileaccount_sp@profile_name@SYSMail_Profile,--profile名称@account_name account@sequence_number account在profile功能说明：启用警报系统里面的邮件参数说明:xp_instance_regwrite修改表修改说明：CreatebyLYon2011-010-10 EXECmaster.dbo.sp_MSsetalertinfo@pagersendsubjectonly=0 Profile', , ,N'REG_DWORD',EXECmaster.dbo.xp_instance_regwriteN'HKEY_LOCAL_MACHINE启用警报系统【启用邮件

邮件配置文件【邮件配置里面功能说明：重新启动SQLSERVERAgent令修改说明：CreatebyLYon2011-010-08EXECmaster..sp_configure@Advanced_Options,1 RECONFIGURE--重新配置EXECmaster..sp_configure@XP_CMDS ,1 RECONFIGURE--重新配置EXECmaster..xp_cmds EXECmaster..xp_cmds 功能说明：添加操作员功pager_days的数据类型为tinyint，默认值为0，表示操作员无法接收寻呼。有效值介于0和127之间。将所需日期的对应值相加即可计算出pager_days的值。例如，从星期一到星期五是2+4+8+16+32=62修改说明：CreatebyLYon2011-010-IFEXISTS(SELECTFROMmsdb.dbo.sysoperatorsWHEREname=@Sys_OperatorsName)EXECmsdb..sp_delete_operator@name EXECmsdb.dbo.sp_add_operator@name=@Sys_OperatorsName,@enabled=1,@weekday_pager_start_time=@weekday_pager_end_time=@saturday_pager_start_time=@saturday_pager_end_time=@sunday_pager_start_time=@sunday_pager_end_time=@pager_days= _address=@Operator_ @pager_address=N'', d_address=N''功能说明：在Job中添加操作员的操参数说明 指定何时将该作业的项放 Windows应用程序日志eventlog_level的数据类型为int【从不1成功时2失败时3始终】修改说明：CreatebyLYon2011-010-10IFEXISTS(SELECTFROMmsdb.dbo.sysjobsWHEREname=@Job_Name)EXEC@job_name=@Job_Name, =@Job_Notify_Level_ d=2,@notify_level_page= _operator_name=@Sys_OperatorsName exec整理出一套邮件发送设定的，供系统调功能说明：启用SQLSERVER邮件功修改说明：CreatebyLYon2011-010-10EXECSP_CONFIGURESHOWADVANCED 允许配置高级选EXECSP_CONFIGURE'DATABASEMAILXPS',1 功能说明：创建新的邮件帐户参数说明:SYSMAIL_DELETE_ACCOUNT_SP删除一个新的数据库邮件帐 创建一个新的数据库邮件帐户，用于保存有关 帐户信息修改说明：CreatebyLYon2011-010-IFEXISTS(SELECTFROM[msdb].[dbo].[sysmail_account]EXECmsdb..sysmail_delete_account_sp@ACCOUNT_NAME='BSERP_RDC_ETLErrorMail'-邮件帐户名EXEC 使用 _ADDRESS='bser Y_NAME='系统管理员', --发件人@REPLYTO_ADDRESS=NULL,@DESCRIPTION=@MAILSERVER_NAME --邮件服务器地@MAILSERVER_TYPE @PORT --邮件服务器端@USERNAME='bser @PASSWORD='rdcmail', --@USE_DEFAULT_CREDENTIALS=0,@ENABLE_SSL=0,@ACCOUNT_ID=NULL功能说明：创建新的数据库邮件配置文件参数说明:sysmail_delete_profile_sp删除一个新的数据库邮件配置 修改说明：CreatebyLYon2011-010-10IFEXISTS(SELECTFROMWHEREEXECmsdb..sysmail_delete_profile_sp@profile_name='BSERP_RDC_ETLErrorProfile'-邮件配置文EXEC@profile_name='BSERP_RDC_ETLErrorProfile', --profile名称@description='BSERPRDC产品数据库邮件配置文件', @profile_id=null功能说明：映射account和修改说明：CreatebyLYon2011-010-EXEC@profile_name='BSERP_RDC_ETLErrorProfile',--profile名称@account_name='BSERP_RDC_ETLErrorMail',--account名称@sequence_number=1 --account在profile中顺序 系统日志【sysjobhistorySELECT,history.server,CONVERT(VARCHAR,jobs.date_created,120)ASdate_created,CONVERT(VARCHAR,jobs.date_modified,120)ASFROMmsdb.dbo.sysjobhistoryhistoryINNERJOINmsdb.dbo.sysjobsjobsONWHERE='PTest'ANDstep_id<>0ANDCONVERT(DATE,date_created)= sysssislog日志【SSIS中的日志SELECTsource,starttimeendtime,messageFROM[dbo].[sysssislog]WHERECONVERT(DATE,starttime)=ANDevent='OnError'AND ode= 功能说明：系统的日修改说明：CreatebyLYon2011-010-DECLARE@TableHTMLNVARCHAR(MAX);--SET@TableHTMLN'<tableborder="1"cellspacing="0"cellPadding="5"style="line-height:25px;font-size:13px;">'+N'<thnowrap>作业开始时间</th><thnowrap>作业结束日期</th><thnowrap>日志详情+N'</tr>'CAST((SELECTtd= --td=history.server,td=CONVERT(VARCHAR,jobs.date_created,120),'',td=CONVERT(VARCHAR,jobs.date_modified,120),'',td=history.message,''FROMmsdb.dbo.sysjobhistoryhistoryINNERJOINmsdb.dbo.sysjobsjobsONhistory.job_id=jobs.job_idWHERE='PTest'ANDstep_id<>ANDCONVERT(DATE,date_created)=CONVERT(DATE,GETDATE())FORXMLPATH('tr'),TYPE)ASNVARCHAR(MAX))N'</table>'EXEC@profile_name='BSERP_RDC_ETLErrorProfile',@recipients=' @subject'SystemDemo错误日志列表',@body=@tableHTML,@body_format='HTML'功能说明SSIS中的日修改说明：CreatebyLYon2011-010-DECLARE@TableSSISHTMLNVARCHAR(MAX);--存放日志列表SET@TableSSISHTML=N'<tableborder="1"cellspacing="0"cellPadding="5"style="line-height:25px;font-size:13px;">'+N'<thnowrap>作业开始时间</th><thnowrap>作业结束日期</th><thnowrap>日志详情+N'</tr>'CAST((SELECTtd= --td=history.server,td=CONVERT(VARCHAR(8),starttime,108),'',td=CONVERT(VARCHAR(8),endtime,108),'',td=message,''WHERECONVERT(DATE,starttime)=ANDevent='OnError'ANDd ode='- FORXMLPATH('tr'),TYPE)ASNVARCHAR(MAX))N'</table>'EXEC@profile_name='BSERP_RDC_ETLErrorProfile',@recipients=' @subject'SSISDemo错误日志列表',@body=@TableSSISHTML,@body_format='HTML'; 功能说明 启用ETL错误日志详情邮件功修改说明 CreatebyLYon2011-10-IFEXISTS(SELECTFROMWHEREID=OBJECT_ID(N'P_InvocationETLErrorLogDetailMail')ANDOBJECTPROPERTY(ID,N'IsProcedure')=1)DROPPROCEDURESETANSI_NULLSSETQUOTED_IDENTIFIERONCREATEPROCEDURE[dbo].[P_InvocationETLErrorLogDetailMail]@Advanced_OptionsVARCHAR(35)='SHOWADVANCED--sp_configure允许配置高级选@Database_MailVARCHAR(35)='DATABASEMAIL--sp_configure启用DATABASEMail VARCHAR(35)= --sp_configure启用 功@SYSMail_AccountVARCHAR(50)='BSERP_RDC_ETLErrorMail', _AddressVARCHAR(50)='bser y_NameVARCHAR(50)='系统管理员 --发件人@MailServer_NameVARCHAR(50 --邮件服务地@MailServer_TypeVARCHAR(50)='SMTP', @UserNameVARCHAR(50)='bser @PassWordVARCHAR(50)='rdcmail', --@SYSMail_ProfileVARCHAR(50)='BSERP_RDC_ETLErrorProfile', @Profile_DescriptionVARCHAR(50'BSERPRDC产品数据库邮件配置文件',--邮件配置文@Job_NameVARCHAR(50 --Job的名@System_SubjectVARCHAR(50)='系统邮件' 件 VARCHAR(50)='lia @Log_DateDATETIME=NULL, @SSIS_EventVARCHAR(50)= --SSISLog类 odeVARCHAR(50 编IF@Log_DateISNULL如果@Log_Date为空的话，赋给默认值为当天SET@Log_Date=

功能说明：启用SQLSERVER邮件功修改说明：CreatebyLYon2011-010-10EXECmaster..sp_configure@Advanced_Options,1 EXECmaster..sp_configure@Database_Mail,1 功能说明：创建新的邮件帐户参数说明:SYSMAIL_DELETE_ACCOUNT_SP删除一个新的数据库邮件帐 创建一个新的数据库邮件帐户，用于保存有关 帐户信息修改说明：CreatebyLYon2011-010-IFEXISTS(SELECTWHEREname=@SYSMail_Account)EXECmsdb..sysmail_delete_account_sp@ACCOUNT_NAME@SYSMail_Account--邮件帐户名EXEC@ACCOUNT_NAME 邮件帐户名称（SQLSERVER使用 _ADDRESS Y_NAME=@Dis @REPLYTO_ADDRESS=NULL,@DESCRIPTION=NULL,@MAILSERVER_NAME=@MAILSERVER_TYPE=@PORT=@USERNAME @PASSWORD=@USE_DEFAULT_CREDENTIALS=-@ENABLE_SSL=@ACCOUNT_ID=功能说明：创建新的数据库邮件配置文件参数说明:sysmail_d