每次查询1行的结果 [英] Email Results of a Query 1 row at a time

问题描述

我需要创建一个每5分钟运行一次的SQL作业。

I have a need to create a SQL job that will run every 5 minutes.

它将检查从特定客户输入的新作业，这些作业将从他们的Web服务器到我们的MIS系统。

It will check for new jobs being entered from a specific customer that will push jobs from their Web Server to our MIS system.

我已经使SQL作业在一个定时循环上运行并发送一个HTML，如果有任何结果要发送。

I have made SQL Jobs that run on a timed cycle and send a HTML if there are any results to send.

在这种情况下我遇到的问题是他们可以一次性发送5个不同的工作，产生5个新工作。

The problem I have in this case they could send 5 different jobs at one shot generating 5 new jobs.

该任务是为每一行（工作）生成并发送电子邮件结果集。

That task is to generate and email for each row (job) in the results set.

我不知道如何将其转储到临时表中，转到该表的顶部，扫描每一行，加载变量

I do not know how to dump it into a temp table, go to the top of that table, scan each row, load the variables

，结束电子邮件，转到下一行，清除并重新加载变量，发送下一封电子邮件等。

for each row, end an email out, go to the next row, clear and reload the variables, send out the next email etc.

完成后，从内存中删除临时表并等待下一个循环。

When done, remove the temp table from memory and wait to the next cycle.

获取数据但可返回多行的当前代码。

Current code that gets the data but could return more than one row.

DECLARE @recordCount INT;

DECLARE @query NVARCHAR（MAX）= N'';

DECLARE @Subject NVARCHAR（300）= N'';

--DECLARE @recipients NVARCHAR（ 50）= N'';

DECLARE @body_format NVARCHAR（50）= N'';
$
DECLARE @email_to NVARCHAR（50）= N'';

DECLARE @JobNumber NVARCHAR（50）= N'';
$


SELECT @recordCount = isnull（count（*），0）
来自OrderHeader的


其中CustAccount ='000403'和UserDefined5 =''


$
SELECT @JobNumber = Orderheader.jobnumber
来自OrderHeader的


其中CustAccount ='000403'和UserDefined5 =''¥b $ b $
SET @query = N'< H4> Jenny Pick Ticket< / H4> '
$
+ N'< table border =" 1">'$
+ N'< tr>< th>作业编号< / th>'

+ N'< th>职位描述< / th> < / tr>'+ CAST（（$
       select

       ; JobNumber

      ，''¥b $ b       Jobdescription

      ，''$
      来自OrderHeader

其中CustAccount ='000403'

和UserDefined5 =''¥b $ b          FOR XML PATH（'tr '）

            ，TYPE

          ）AS NVARCHAR（MAX））+ N'< / table>';



set @Subject ='仅限测试 - 珍妮挑选门票  '+ @ JobNumber

DECLARE @recordCount INT;
DECLARE @query NVARCHAR(MAX) = N'';
DECLARE @Subject NVARCHAR(300) = N'';
--DECLARE @recipients NVARCHAR(50) = N'';
DECLARE @body_format NVARCHAR(50) = N'';
DECLARE @email_to NVARCHAR(50) = N'';
DECLARE @JobNumber NVARCHAR(50) = N'';

SELECT @recordCount = isnull(count(*), 0)
from OrderHeader
where CustAccount='000403' and UserDefined5=''


SELECT @JobNumber = Orderheader.jobnumber
from OrderHeader
where CustAccount='000403' and UserDefined5=''

SET @query = N'<H4>Jenny Pick Ticket </H4> '
+ N'<table border="1">'
+ N'<tr><th>Job Number</th>'
+ N'<th>Job Description</th> </tr>' + CAST((
        select
        JobNumber
        ,''
        Jobdescription
        ,''
        from OrderHeader
where CustAccount='000403'
and UserDefined5=''
            FOR XML PATH('tr')
                ,TYPE
            ) AS NVARCHAR(MAX)) + N'</table>';

set @Subject = 'TESTING ONLY - Jenny Pick Ticket  '+@JobNumber

IF @recordCount> 0

BEGIN

     EXEC msdb.dbo.sp_send_dbmail @profile_name ='SQL2012Mail'

   &NBSP;&NBSP;&NBSP;  ，@ recipients =  '[e-mail]'
$
   &NBSP;&NBSP;&NBSP;  ，@ body_format ='HTML'

   &NBSP;&NBSP;&NBSP;  ，@ body = @query

   &NBSP;&NBSP;&NBSP;  ，@ Subject = @Subject

END

GO



$
在此先感谢.Andy

IF @recordCount > 0
BEGIN
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL2012Mail'
        ,@recipients =  '[e-mail]'
        ,@body_format = 'HTML'
        ,@body = @query
        ,@Subject = @Subject
END
GO


Thanks in advance.Andy

推荐答案

您可以循环以下查询的输出

You can loop the output from the following query

SELECT [job_id]，[name]

  FROM [msdb]。[dbo]。[sysjobs]

WHERE [enabled] = 1 AND [date_created]> = DATEADD（分钟，-5，GETDATE（））;

然后逐个运行新的工作。

and then run the new jobs one by one.

这篇关于每次查询1行的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！