Export to CSV using scheduled job SQL Agent Management Studio

Question

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.

Any assistance is appreciated.

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

That's a very familiar bunch of terms. Is the problem that "the output file doesn't get updated"? Is it ever created? When you run that script directly does it return any data? I actually suggest that you create all of that as a stored procedure and pop in `msdb.dbo.sp_send_dbmail` at the end and use the `@attach_query_result_as_file` and `@query` parameters. Then you don't need to fiddle about saving files and picking them up again and it's all in one stored procedure. — Nick.Mc, May 16 '17 at 00:23
Hi Nick, The file is never created so I tried creating an empty file for it to update - but not luck. I'll create a single stored procedure for the whole thing and try again then. Thanks — GlenCloncurry, May 16 '17 at 00:29
You need to do something like `EXEC master..xp_cmdshell 'bcp "SELECT ''col1'' AS col1, ''Col2'' AS col2 ... from mytable' queryout c:\MyFile.csv -c -t -r \n -S myServerName -U username -P Password'` — artm, May 16 '17 at 00:37
If you use xp_cmdshell, you have to enable it, you have to set aside some space in a path somewhere,you have to make sure you have right, you have to hard code a path in two spots. This is why I suggested sp_send_dbmail as none of these things are necessary. — Nick.Mc, May 16 '17 at 01:06

score 3 · Answer 1 · answered May 16 '17 at 01:29

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

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

Then email the file with sp_send_dbmail

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='MyEmailProfileName',
@recipients='fgfh@test.com',
@file_attachments=@fileName

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

Export to CSV using scheduled job SQL Agent Management Studio

1 Answers1

Linked