使用计划作业 SQL Agent Management Studio 导出到 CSV

我正在尝试创建一个 SQL 代理作业，该作业每天自动运行以下查询并生成一个 CSV 文件，该文件存储在 C:\test.csv 上，并通过电子邮件发送给人们。

我在网上尝试了各种选项，但找不到适合我的查询的选项。它是来自多个数据集的数据，放入单个文件中，以便导入到另一个电子表格中进行报告。

如有任何帮助，我们将不胜感激。

我已经使用执行查询的任务创建了一个 SQL 作业，并且尝试使用高级页面上的高级选项来输出文件，但是，输出文件没有更新。

use Prod_data
declare @ReportingStart datetime = dateadd(HH,-17,convert(datetime,convert(date,getdate())))
declare @ReportingEnd datetime = dateadd(HH,7,convert(datetime,convert(date,getdate())))


-- Daily Production time
declare @Production float = (select sum(dDurationSeconds/60)
from OEEQStateData
where tstart >= @ReportingStart and tstart < @ReportingEnd
and sStateDescription = 'Production'and sWorkcellDescription ='Hoisting')

-- Daily Idle time
declare @Idle float = (select isnull(sum(dDurationSeconds/60),0)
from OEEQStateData
where tstart >= @ReportingStart and tstart < @ReportingEnd
and sStateDescription = 'Idle Time'and sWorkcellDescription ='Hoisting')

-- Daily Unplanned time
declare @Unplanned float = (select sum(dDurationSeconds/60)
from OEEQStateData
where tstart >= @ReportingStart and tstart < @ReportingEnd
and sStateDescription like 'Unplanned%'and sWorkcellDescription ='Hoisting')

--Daily Maintenance time
declare @Planned float = (select sum(dDurationSeconds/60)
from OEEQStateData
where tstart >= @ReportingStart and tstart < @ReportingEnd
and sStateDescription like 'Planned%'and sWorkcellDescription ='Hoisting')

--Util
declare @Util float = @Production/(1440-@Planned-@Unplanned)

--Avail
declare @Avail float = ((@Production+@Idle)/1440)

--Hoist Schedule
declare @HoistSched int = (select round(DS_Prod+NS_Prod,-2)
from Schedule
where date = convert(date,@ReportingStart))


--Hoist Schedule for tomorrow 
declare @HoistSchedTom int = (select round(DS_Prod+NS_Prod,-2) 
from Schedule
where date = convert(date,@ReportingEnd))

--PM for tommorrow
declare @PM int = (select (DS_DT+NS_DT) 
from Schedule
where date = convert(date,dateadd(dd,1,getdate())))

--Hoist Daily Production

declare @Tonnes int = (select top 1
    case
        when coalesce(lead(value) over(partition by tagname order by datetime),0) - value < '0' then ''
        else coalesce(lead(value) over(partition by tagname order by datetime),0) - value
    end
 from  Linked_Database
 where datetime between @ReportingStart and @ReportingEnd
 and wwResolution = (1440 * 60000)
 and tagname = 'SALV_CV005_WX1_PROD_DATA.Actual_Input'
 )

 --MPS 24HR

declare @MPS_today float = (select sum(value)
from  Linked_Database
 where datetime = @ReportingEnd
 and tagname like 'MPS_FI7940%.Actual_Input')

 declare @MPS_yest float = ( select sum(value) 
from  Linked_Database
 where datetime = @ReportingStart
 and tagname like 'MPS_FI7940%.Actual_Input')

declare @MPS_total float = (@MPS_today-@MPS_yest)

--IPDW 24HR (claypit + IPDW)

declare @IPDW_today float = (select isnull(sum(value),0)
from  Linked_Database
 where datetime = @ReportingEnd
 and tagname like '%FI792%.Actual_Input')

 declare @Clay_today float = (select isnull(sum(value),0) 
from  Linked_Database
 where datetime = @ReportingEnd
 and tagname like '%FI764%_TOTAL.PVAI')

 declare @IPDW_yest float = (select isnull(sum(value),0) 
from  Linked_Database
 where datetime = @ReportingStart
 and tagname like '%FI792%.Actual_Input')

 declare @Clay_yest float = (select isnull(sum(value),0) 
from  Linked_Database
 where datetime = @ReportingStart
 and tagname like '%FI764%_TOTAL.PVAI')

 declare @IPDW_total float = (@IPDW_today+@Clay_today-@IPDW_yest-@Clay_yest)

--Average airflow across both vent fan

declare @VF_Avg float = (select avg(value) 
from  Linked_Database
 where datetime between @ReportingStart and @ReportingEnd
 and tagname = 'vfans_totalairflow.pv_at')

 --BAC wet bulb
declare @BAC_Wet float = (select avg(value) 
from  Linked_Database
 where datetime between @ReportingStart and @ReportingEnd
 and tagname = 'gb_bac_tt787125a._analog_PV')

 declare @BAC_Dry float = (select avg(value) 
from  Linked_Database
 where datetime between @ReportingStart and @ReportingEnd
 and tagname = 'gb_bac_tt787125b._analog_PV')

  --Final Select Statement
 select @HoistSched as Hoist_Sched_today, @HoistSchedTom as Hoist_Sched_Tom, @PM as PM_Tom, @Tonnes as Hoist_Act, @Util as Hoist_Util, @Avail as Hoist_Avail, @MPS_total as MPS_Dewatering_Total, @IPDW_total as IPDW_Dewatering_Total,  @VF_Avg as VFan_AVG, @BAC_Dry as BAC_Dry_AVG, @BAC_Wet as BAC_Wet_AVG

您可以使用以下命令创建 csv 文件xp_cmdshell但需要先启用它：

EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO  

然后构建 bcp 命令并运行它xp_cmdshell

declare @fileName varchar(4000) = 'C:\Temp\MyFile.csv'
declare @bcpCommand varchar(4000)

SET @bcpCommand = 'bcp "SELECT ' + @HoistSched + ' AS Hoist_Sched_today, ' + @HoistSchedTom + ' as Hoist_Sched_Tom" queryout ' + @fileName + '  -c -t , -r \n  -S . -T'

select @bcpCommand 

EXEC master..xp_cmdshell @bcpCommand

然后通过电子邮件发送文件sp_send_dbmail

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='MyEmailProfileName',
@recipients='[email protected]',
@file_attachments=@fileName

如果您不需要保存文件而只需通过电子邮件发送结果，那么您需要根据查询构建电子邮件正文并使用 @body 参数sp_send_dbmail