sp_send_dbmail 不会发送查询结果 [英] sp_send_dbmail will not send query results
问题描述
我在每个该死的论坛上尝试了所有建议的途径，但无济于事！需要将存储在表中的 SQLPERF(logspace) 的结果通过 sp_send_dbmail 发送给收件人.
I've tried every avenue on every damn forum suggested, but to no avail!
Need to send results of SQLPERF(logspace), that have been stored in a table, via sp_send_dbmail to recipient.
作业的第 2 步是发生故障的地方.请帮忙！
Step 2 of job is where failure occurs. Please help!
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'MyDBA',
@recipients= 'Mack@mydba.co.za',
@subject='Log Warning',
@query='SELECT * from #TempForLogSpace WHERE LogSpaceUsed >80
推荐答案
您无法使用数据库邮件从临时表中进行查询.您用于创建临时表的会话(我假设您的工作的第 1 步)已关闭，并在第 2 步开始时启动了一个新会话.由于会话已关闭，表已被删除(即使表没有被删除，因为它是一个新会话，您无权访问其他会话临时表).
You can't query from a temp table using database mail. The session that you used to create the temp table (step 1 of your job I assume) has been closed and a new session started when step 2 started. Because the session has been closed the table has been dropped (even if the table hasn't been dropped because it's a new session you don't have access to the other sessions temp table).
要么创建一个物理表并使用它(在 tempdb 数据库或您的数据库中)，要么将创建输出的代码放在 @query 中，最后使用 select * from #TempForLogSpace(存储过程将很多在这种情况下更容易处理).
Either create a physical table and use that (either in the tempdb database or your database) or put the code which creates the output in the @query with the select * from #TempForLogSpace at the end (a stored procedure will be much easier to deal with in this case).
这篇关于sp_send_dbmail 不会发送查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
