使用计划的作业SQL Agent Management Studio导出为CSV [英] Export to CSV using scheduled job SQL Agent Management Studio

问题描述

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

I'm trying to create a SQL agent job which automatically runs the below query on a daily basis and generates a CSV file which is stored on C:\test.csv and also emailed to people.

我已经在线尝试了各种选项，但是找不到适合我的查询的选项.将来自多个数据集的数据放入一个文件中，然后导入另一个电子表格中进行报告.

I've tried various options online but cannot find one which will suit my query. It is data from multiple datasets put into a single file for import into another spreadsheet for reporting.

感谢您的协助.

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

I've created a SQL job with the task which performs the query, and I've tried using the advanced option on the advanced page to output the file, however, the output file doesn't get updated.

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

推荐答案

您可以使用xp_cmdshell创建csv文件，但需要首先启用它:

You can create the csv file with xp_cmdshell but it needs to be enabled first:

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

Then build your bcp command and run it with 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='[e-mail]',
@file_attachments=@fileName

如果您不需要保存文件而仅通过电子邮件发送结果，则需要从查询中构建电子邮件的正文，并为sp_send_dbmail

If you don't need to save the file but only email the results then you need to build the body of the email from your query and use the @body argument for sp_send_dbmail

这篇关于使用计划的作业SQL Agent Management Studio导出为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！