用BCP从SQL Server 数据库中导出Excel文件

BCP(Bulk Copy Program)是一种简单高效的数据传输方式在SQL Server中，其他数据传输方式还有SSIS和DTS。

这个程序的主要功能是从数据库中查询Job中指定step的执行信息，并将结果输出到Excel文件中，并利用SQL Server的邮件功能，发送生成的excel文件给指定的人.

DECLARE @command varchar(8000),
        @msgBody varchar (4000),
        @withJobOutcome VARCHAR(1),
        @APJobExecTime DATETIME,
        @msgSubject varchar(200),
        @reportName varchar(200),
        @rundate DATE

-- Set report date parameter
-- Set @withJobOutcome as 'T' to retrieve history of job step 'Job Outcome' as well.
SELECT @rundate = '2013-7-22', @withJobOutcome = 'F'

-- This file name is specific to DB23, please provide an available
-- path respect to the server you want to monitor.
SET @reportName = 'E:ReportJobExecutionHistory_' + CONVERT(CHAR(10), @rundate, 120) + '.xls'

-- Remove possible report file
SELECT @command = 'del ' + @reportName
EXEC master..xp_cmdshell @command, NO_OUTPUT

-- Get execution date time of Job A0000SQ-AP
SELECT TOP 1 @APJobExecTime = CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS DATETIME), 120) +
       CAST(STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)),6),5,0,':'),3,0,':') AS DATETIME)
  FROM sysjobhistory h
 INNER JOIN sysjobs j
    ON j.job_id = h.job_id
 WHERE CAST(STR(h.run_date,8, 0) AS DATE)= @rundate
   AND j.name = 'A0000SQ-AP'

-- Build Job Execution History Query Statement
SELECT @command ='bcp " SELECT ''Job Name'' JobName, ''Step Name'' StepName, '' Execution Time'' RunDate, ''Step Duration'' StepDuration, ''Execution Status'' ExecutionStatus, ''Shift'' Shift UNION ALL SELECT JobName, StepName, CONVERT(CHAR(19), RunDate, 120), StepDuration, ExecutionStatus,(CASE WHEN RunDate < ''' + CONVERT(char(19), @APJobExecTime, 120) + ''' THEN ''NA'' WHEN RunDate >= ''' + CONVERT(char(19), @APJobExecTime, 120) + ''' THEN ''AP'' END) AS Shift FROM (SELECT j.name JobName, h.step_name StepName, CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS DATETIME), 120) + CAST(STUFF(STUFF(RIGHT(''000000'' + CAST(h.run_time AS VARCHAR(6)),6),5,0,'':''),3,0,'':'') AS DATETIME) RunDate, LEFT(RIGHT(''000000'' + CAST(h.run_duration AS VARCHAR(10)),6),2) + '':'' + SUBSTRING(RIGHT(''000000'' + CAST(h.run_duration AS VARCHAR(10)),6),3,2) + '''''''' + RIGHT(RIGHT(''000000'' + CAST(h.run_duration AS VARCHAR(10)),6),2) + '''''''''''' StepDuration, (CASE h.run_status WHEN 0 THEN ''failed'' WHEN 1 THEN ''Succeded'' WHEN 2 THEN ''Retry'' WHEN 3 THEN ''Cancelled'' WHEN 4 THEN ''In Progress'' END) AS ExecutionStatus, h.message MessageGenerated FROM msdb..sysjobhistory h INNER JOIN msdb..sysjobs j ON j.job_id = h.job_id WHERE CAST(STR(h.run_date,8, 0) AS DATE)= ''' + CONVERT(CHAR(10), @rundate, 120) + ''' AND h.step_name not like (SELECT TOP 1 CASE ''' + @withJobOutcome + ''' WHEN ''T'' THEN '''' WHEN ''F'' THEN ''(Job outcome)'' END FROM msdb..sysjobs) AND (j.name in (''A0000SQ-NA'',''A0000SQ-AP'') or (j.name like ''A0%'' and j.name not like ''%-%''))) job ORDER BY RunDate, JobName, StepName" queryout ' + @reportName + ' -c -T -S ' + @@SERVERNAME

-- Export to Excel file
EXEC master..xp_cmdshell @command

-- Send email with the report as attachement
SET @msgBody = 'Job Execution Time history report on ' + CONVERT(CHAR(10), @rundate, 120) + ' is ready, please look detail information by checking the attached report file.'
SET @msgSubject = 'Job Execution History Report for [' + CONVERT(CHAR(10), @rundate, 120) + ']'
EXEC msdb..sp_send_dbmail @profile_name = 'Notifications',
                          @recipients = 'xxxxx@gmail.com',
                          @subject = @msgSubject,
                          @body = @msgBody,
                          @body_format ='TEXT',
                          @file_attachments = @reportName

-- Remove the temporary file after email sent
SELECT @command = 'del ' + @reportName
EXEC master..xp_cmdshell @command, NO_OUTPUT

注意事项

BCP中的table或query是不能换行的，不然就会报参数不正确的错误。

Reference

• https://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/