使用sql中存储的send-dbmail进程从一个表发送多封电子邮件

Question

我正试图使用sp_send_dbmail从表中发送多封电子邮件，但在运行存储过程时遇到此错误。这是我得到的错误：

Parameter @attach_query_result_as_file cannot be 1 (true) when no value is specified for parameter @query. A query must be specified to attach the results of the query.

这是我的代码

  SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER proc [dbo].[myProc] as   
    declare rscursor cursor read_only
    for 
     select Email, FullName from myTable
      where userActive =1
        declare @Emails            nvarchar (100)
        declare @FullName  nvarchar (100)

    open rscursor
    fetch next from rscursor into @Emails, @FullName
    while @@fetch_status=0
        begin
             EXEC msdb.dbo.sp_send_dbmail
            @recipients = @Emails, 
            @subject = 'Sleep Diary Reminder',
            @body = 'this is just test',
            @profile_name = 'myProfile',

            @attach_query_result_as_file = 1        
        fetch next from rscursor into @Emails, @FullName
    end
    close rscursor
    deallocate rscursor

运行我的sp

EXEC dbo.myProc

Answer 1

由于您没有使用过程sp_send_dbmail的@query变量将查询附加到邮件，请设置@attach_query_result_as_file = 0或完全删除该变量。或者，如果你想这样做，可以附加一个查询——你可以尝试添加@query = 'SELECT GETDATE()'来查看它是否有效。