Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Introduction:

We received a requirement from one of the our client to setup an automated email on Azure Managed instance which will enable DB email to send the sql query output/results in CSV format.

Please note, this code works with all SQL platform (SQL on-premises, SQL on VM, Azure SQL database, Azure SQL managed instance)

Solution:-

As we all know Azure SQL database commonly called as single-DB PaaS does not support DB email features but Azure SQL managed instance support the same.

Once you all done with configuring Db email and testing db- email, based on the requirement explained above , below code can be used to achieve the same with some modification based on asked.

We also know that Azure SQL database and Azure Managed instance does not expose to OS. that means, OS all takes care by Microsoft itself. We do not have access to it by any means, not on file subsystem as well.

Hence, the SQL query result should be converted in formatted CSV or text file on the fly/ dynamically.

USE [database]
GO

-- pass table name and object type to OBJECT_ID - a NULL is returned if there is no object id and DROP TABLE is ignored 
IF OBJECT_ID(N'dbo.SQLtoCSV', N'U') IS NOT NULL  
   DROP TABLE [dbo].[SQLtoCSV]
GO

-- Here comes your reporting query, insert the result in above non-temp table or any non-temp table of your wish or temp table in case of SQL on-premises or SQL on VM.
-- Please note, use [database.dbo.table] at all the applicable places to get proper email and results in CSV 

SELECT [reporting query statement]


into database.dbo.SQLtoCSV  ----Insert into user table for easiness 

FROM [rest of reporting query statement]
--Here your query gets ended now, build the logic for CSV

      

declare @qry varchar(8000)
declare @column1name varchar(50)
-- Create the column name with the instrucation in a variable
SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'First_Original_column_NameOfQueryOutputFrom_SQLtoCSV]'
 
-- Create the query, concatenating the column name as an alias
select @qry='set nocount on;


select ''Original_Column1'',''Original_Column2'',''Original_Column3'',''Original_Column4'',''Original_Column5'',''Original_Column6'',''Original_Column7''  ''union all'' 
select  Original_Column1 ' + @column1name + 
             ' ,Original_Column2, Original_Column3, Original_Column4, Original_Column5, Original_Column6, Original_Column7  from database.dbo.SQLtoCSV
			 
			 '
 
-- Send the e-mail with the query results in attach
exec msdb.dbo.sp_send_dbmail 
@recipients='xyz@abc.com;def@abc.com',
@copy_recipients='',
@profile_name = '',
@execute_query_database = 'Database Name',
 @body_format = 'html',
 @body ='<p>Hi Team,<br>Please refer the enclosed report for so and so...<br><br>Regards,</br>DBA team.</p>',  
 @importance = 'Normal',
@query=@qry,
@subject='',
@attach_query_result_as_file = 1,

@query_attachment_filename = 'File_Name.csv',
--@query_result_separator=','
@query_result_separator='	'
,@query_result_width =32767,
@query_result_no_padding=1,
@query_result_header=0

Happy Learning!