Export Query Result in CSV File using T-SQL

Let's say you have a program or sql agent job, which runs a stored procedure, extract data from some tables, join them, and do some calculations. Then you want to export the result into a csv file, into a local drive or shared folder somewhere in your network. You can NOT fire BCP utility by xp_cmdshell because your company security administrator not allow your program spawning Windows command shells, and if that stored procedure is to be executed per each user button click, it will impact your sql server hardware performance.
In this post, I will show you how to do this by using OLE Automation Procedures in SQL Server. The prerequisites are you must turn on this server option, and the owner of your user database must be the same as that of the master database (using sa account as the owner of all your databases actually is the best practice).
USE master
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
USE TESTDB
ALTER AUTHORIZATION ON DATABASE::[TESTDB] TO sa
GO
-- =============================================
-- Author:  Peter Lee
-- Description: Convert local temp table #TempTblCSV into a CSV string variable
-- =============================================
CREATE OR ALTER PROCEDURE ConvertLocalTempIntoCSV
 @csvOutput nvarchar(max) OUTPUT
AS
BEGIN
 SET NOCOUNT ON;
 -- get list of columns
 DECLARE @cols nvarchar(4000);
 SELECT @cols = COALESCE(@cols + ' + '', '' + ' + 'CAST([' + name + '] AS nvarchar(4000))', 'CAST([' + name +'] AS nvarchar(4000))')
  FROM tempdb.sys.columns WHERE [object_id] = OBJECT_ID('tempdb..#TempTblCSV') ORDER BY column_id;
 CREATE TABLE #TempRows (r nvarchar(4000));
 EXEC('INSERT #TempRows SELECT ' + @cols + ' FROM #TempTblCSV');
 SELECT @csvOutput = COALESCE(@csvOutput + CHAR(13)+CHAR(10) + r, r) FROM #TempRows;
END
GO
/****** Object:  StoredProcedure [TLB].[WriteToFile] ******/
CREATE OR ALTER PROC [WriteToFile]
 @file varchar(2000),
 @text nvarchar(max)
WITH EXECUTE AS 'dbo'
AS  
 DECLARE @ole int;
 DECLARE @fileId int;
 DECLARE @hr int;
 EXECUTE @hr = master.dbo.sp_OACreate 'Scripting.FileSystemObject', @ole OUT;
 IF @hr <> 0 
 BEGIN 
  RAISERROR('Error %d creating object.', 16, 1, @hr)
  RETURN
 END
 EXECUTE @hr = master.dbo.sp_OAMethod @ole, 'OpenTextFile', @fileId OUT, @file, 2, 1;  -- overwrite & ALTER if not exist
 IF @hr <> 0 
 BEGIN 
  RAISERROR('Error %d opening file.', 16, 1, @hr)
  RETURN
 END
 EXECUTE @hr = master.dbo.sp_OAMethod @fileId, 'WriteLine', Null, @text;
 IF @hr <> 0 
 BEGIN 
  RAISERROR('Error %d writing file.', 16, 1, @hr)
  RETURN
 END
 EXECUTE @hr = master.dbo.sp_OADestroy @fileId;
 IF @hr <> 0 
 BEGIN 
  RAISERROR('Error %d closing file.', 16, 1, @hr)
  RETURN
 END
 EXECUTE master.dbo.sp_OADestroy @ole;
GO
-- Create a local temp table, name must be #TempTblCSV
-- you can define any columns inside, e.g.
CREATE TABLE #TempTblCSV (pk int, col1 varchar(9), dt datetime2(0));
-- Fill the local temp table with your query result, e.g.
INSERT #TempTblCSV VALUES (1, 'a', '20180112 12:00');
INSERT #TempTblCSV VALUES (2, 'b', '20180113 13:00');
INSERT #TempTblCSV VALUES (3, 'c', '20180113 14:00');
-- convert the local temp table data into a string variable, which is the CSV content
DECLARE @csv nvarchar(max);
EXEC ConvertLocalTempIntoCSV @csv OUTPUT;
-- write the CSV content into a file
EXEC WriteToFile 'H:\TEMP\PETER\output.csv', @csv;
-- Not a must if to drop the temp table, especially if you're writing a stored proc
DROP TABLE #TempTblCSV;

And here's the sample output: