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.
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)
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 @email@example.com;firstname.lastname@example.org', @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