SqlServer作业发送邮件附件

我写了个过程发送邮件，带附件单独运行过程，能正常运行，邮件及附件能正常发送但现在将该过程添加到作业里，邮件能正常发送，但没有附件，不知道为啥SqlServer版本:2000
if exists(select 1 from sysobjects where name  = 'spSendMail' and type = 'P')
drop procedure spSendMail
go
create procedure spSendMail
@from varchar(100), --send by
@to varchar(100), --send to
@bcc varchar(500), --bcc(blind carbon copy)/cc(carbon copy)
@subject varchar(400) = ' ', --mail subject
@htmlBody varchar(8000) = ' ', --mail body content
@addAttachment varchar(100) --attachment,such as 'd:/fileName.xls',if there is no attachment,just input ''
as
declare @object int
declare @hr int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)

declare @smtpServer varchar(50)
declare @smtpUsername varchar(50)
declare @smtpPassword varchar(50)
set @smtpServer = 'mail.qq.com'
set @smtpUsername = ''
set @smtpPassword = '' --@see http://msdn.microsoft.com/en-us/library/ms526227%28v=exchg.10%29.aspxd
--http://schemas.microsoft.com/cdo/configuration
exec @hr = sp_OACreate 'CDO.Message',@object out
set @htmlBody = '<body><h3><font col=Red>' + @htmlBody + '</font></h3></body>'
--change line
--set @htmlBody = replace(@htmlBody,char(10),'<br/>')
--exec @hr = sp_OASetProperty @object,'HTMLBodyPart.Charset','GBK' exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',@smtpServer
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value','25'
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value',@smtpUsername
exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@smtpPassword exec @hr = sp_OAMethod @object,'Configuration.Fields.Update',null
exec @hr = sp_OASetProperty @object,'To',@to
exec @hr = sp_OASetProperty @object,'Bcc',@bcc
exec @hr = sp_OASetProperty @object,'From',@from
exec @hr = sp_OASetProperty @object,'Subject',@subject
exec @hr = sp_OASetProperty @object,'HtmlBody',@htmlBody
--exec @hr = sp_OASetProperty @object,'TextBody',@htmlBody(String content)

if @addAttachment <> ''
exec @hr = sp_OAMethod @object,'AddAttachment',null,@addAttachment
if @hr <> 0
select @hr
begin
exec @hr = sp_OAGetErrorInfo null,@source out,@description out
if @hr = 0
begin
select @output = ' Source: '+@source
print @output
select @output = ' Description: '+@description
print @output
end
else
begin
print ' sp_OAGetErrorInfo failure!'
return
end
end
exec @hr = sp_OAMethod @object,'Send',null --check error
if @hr <> 0
select @hr
begin
exec @hr = sp_OAGetErrorInfo null,@source out,@description out
if @hr = 0
begin
select @output = ' Source: '+@source
print @output
select @output = ' Description: '+@description
print @output
end
else
begin
print ' sp_OAGetErrorInfo failure!'
return
end
end
print 'Send Mail Success!'
exec @hr = sp_OADestroy @object
作业分两步：
1：过程从数据库获取数据生成excel：D:\Install\Task\Rpt\Rpt.xls
2：过程发送邮件，附件为该excel
exec spSendMail '','','','subject','body','D:/Install/Task/Rpt/Rpt.xls'

解决方案 »

  1.   

    是不是还没生成完excel就执行发送拉？
      

  2.   

    我是按顺序来的，应该不会错，通过excel报错应该是循序不对，但不知道怎么处理作业时这么调用的
    exec master.dbo.xp_cmdshell 'del D:\Install\Task\Rpt\Rpt.xls';
    go
    exec spJoinDqWeekRpt @sqlStr='select username as 姓名,
     from tbName where datepart(ww,getdate()) = 
    datepart(ww,auditTime) and year(getdate()) = year(creatTime)',@filePath='D:\Install\Task\Rpt\',
    @fileName='Rpt.xls',@sheetName='周报';
    go
    exec spSendMail '','','','subject','body','D:/Install/Task/Rpt/Rpt.xls'
      

  3.   

    你的sql agent服务，用的启动账户是什么，有读取附件的权限吗？
      

  4.   

    那你先别发邮件，光执行循环生成excel的，看看有没有问题。
      

  5.   

    那你先别发邮件，光执行循环生成excel的，看看有没有问题。只生成excel也显示正在使用文件，我查下sqlserver关闭文件，应该不是权限问题
      

  6.   

    那你先别发邮件，光执行循环生成excel的，看看有没有问题。只生成excel也显示正在使用文件，我查下sqlserver关闭文件，应该不是权限问题那你换个逻辑，先算出你的最终结果集，再插入excel中
      

  7.   

    那你先别发邮件，光执行循环生成excel的，看看有没有问题。只生成excel也显示正在使用文件，我查下sqlserver关闭文件，应该不是权限问题那你换个逻辑，先算出你的最终结果集，再插入excel中不知道咋写
      

  8.   

    把这段：'select username as 姓名,      from tbName where datepart(ww,getdate()) =      datepart(ww,auditTime) and year(getdate()) = year(creatTime)用BCP命令导出成excel
      

  9.   


    我之前用过这个现在在查询分析器能导出数据，一放到作业里就不行了文件夹配置了everyone完全控制权限
    exec sp_configure 'show advanced options', 1;   
    reconfigure;   
    exec sp_configure 'xp_cmdshell', 1;   
    reconfigure;
    EXEC master..xp_cmdshell 'bcp "select contact_info=''联系信息'' from schema.dbo.tb union all select contact_info from schema.dbo.tb "  queryout   D:/Install/Task/Rpt/Rpt.xls -c -q -S"" -U"" -P""';
    作业“aa”: 第 1 步，“aa”: 开始执行 2013-05-09 17:29:17已将配置选项 'show advanced options' 从 1 改为 1。请运行 RECONFIGURE 语句以安装。 [SQLSTATE 01000]
    消息 15123，严重度 16: 配置选项 'xp_cmdshell' 不存在，也可能是高级选项。 [SQLSTATE 42000]
    消息 0，严重度 16:  [SQLSTATE 01000]
    消息 15456，严重度 16: 有效的配置选项为: [SQLSTATE 01000]
    name                                minimum     maximum     config_value run_value  
    ----------------------------------- ----------- ----------- ------------ -----------
    recovery interval (min)             0           32767       0            0
    allow updates                       0           1           0            0
    user connections                    0           32767       0            0
    locks                               5000        2147483647  0            0
    open objects                        0           2147483647  0            0
    fill factor (%)                     0           100         0            0
    media retention                     0           365         0            0
    nested triggers                     0           1           1            1
    remote access                       0           1           1            1
    two digit year cutoff               1753        9999        2049         2049
    default full-text language          0           2147483647  2052         2052
    default language                    0           9999        30           30
    max worker threads                  32          32767       255          255
    remote proc trans                   0           1           0            0
    network packet size (B)             512         32767       4096         4096
    index create memory (KB)            704         2147483647  0            0
    priority boost                      0           1           0            0
    show advanced options               0           1           1            1
    remote login timeout (s)            0           2147483647  20           20
    remote query timeout (s)            0           2147483647  600          600
    cursor threshold                    -1          2147483647  -1           -1
    min memory per query (KB)           512         2147483647  1024         1024
    query wait (s)                      -1          2147483647  -1           -1
    set working set size                0           1           0            0
    user options                        0           32767       0            0
    affinity mask                       -2147483648 2147483647  0            0
    max text repl size (B)              0           2147483647  65536        65536
    cost threshold for parallelism      0           32767       5            5
    max degree of parallelism           0           32          0            0
    min server memory (MB)              0           2147483647  0            0
    max server memory (MB)              4           2147483647  2147483647   2147483647
    query governor cost limit           0           2147483647  0            0
    lightweight pooling                 0           1           0            0
    scan for startup procs              0           1           0            0
    c2 audit mode                       0           1           0            0
    awe enabled                         0           1           0            0
    Cross DB Ownership Chaining         0           1           1            1
    output                                                                                                                                                                                                                                                         
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]无法打开 BCP 主数据文件
    (null)哎
      

  10.   


    我看了这个，好像sqlserver 2000只有第一条能用我也去参考了http://blog.csdn.net/happyflystone/article/details/4798786
    切换上下文，赋权限，在作业上：我单独执行了bcp导出(老提示Error = [Microsoft][ODBC SQL Server Driver]无法打开 BCP 主数据文件)和发送邮件(附件发不过来)我在网上查了下，也没得到Microsoft][ODBC SQL Server Driver]无法打开 BCP 主数据文件的真正原因而用查询分析器都可以实现，各位有啥好指教的吗
      

  11.   

    那就是你的sql服务和sql 代理的账号权限不够。试一下改成管理员账号
      

  12.   


    我看不出来哦
    use master
    go
    exec sp_addlogin 'test', 'test', 'dbName'
    go
    use dbName
    go
    exec sp_adduser 'test', 'test', 'db_owner'
    go
    use master
    go
    exec sp_adduser 'test','test','db_ddladmin'
    go
    grant exec on xp_cmdshell to test
    go
    exec sp_addsrvrolemember 'test', 'sysadmin'
    go
    --setuser 'test'
    --select suser_sname()
    use dbName
    go
    exec master..xp_cmdshell 'bcp "select getdate()=''系统时间'',username=''姓名'' from dbName.dbo.tbName union select getdate,username from dbName.dbo.tbName " queryout c:\测试.xls -c -q -S"serverName" -U"userName" -P"passWord"';use dbName
    go
    sp_dropuser 'test'
    go
    use master
    go
    sp_dropuser 'test'
    go
    sp_droplogin 'test'
    go
    declare
    @userName sysname,
    @passWord sysname,
    @sql varchar(1000);
    select @userName = N'xpcmdshell',
           @passWord = N'@passWord',
           @sql = 'net user '+@userName+' /delete';
    select @sql
    exec xp_cmdshell @sql
    难道是test的sysadmin权限在master下，不在用户数据库下？
      

  13.   

    sysadmin的权限是整个实例级别的，有了这个权限，即使你在数据库级别设置任何权限都没必要了。
      

  14.   

    我是说在sql启动账号中换成Windows administrator
      

  15.   


    如果是实例权限，确实数据库权限被包含在里面，但为啥没权限执行？不知道咋换成windows域账户
      

  16.   


    我两个都试过了……都是无法打开bcp主数据文件，而且bcp导出的sql语句变化，会提示xp_cmdshell不存在，每次都要reconfig,这个跟参数化查询有关吗？我去官方找了个，不知道域指的是啥，ip? 机器名/计算机用户名?
    http://support.microsoft.com/kb/890775
      

  17.   


    弄了半天，测试环境可以测，但到生产环境，添加了个用户，添加作业时作为用户运行，没有添加的用户，按道理应该有个test测试环境是有的，而且能正常运行,use master
    go
    exec sp_addlogin 'test', 'test', 'dbName'
    go
    use dq
    go
    exec sp_adduser 'test', 'test', 'db_owner'
    go
    use master
    go
    exec sp_adduser 'test','test','db_ddladmin'
    go
    grant exec on xp_cmdshell to test
    go
    exec sp_addsrvrolemember 'test', 'sysadmin'
    go