Monday, June 30, 2014

SQL Server – Convert results from query to string and send email from DB

Given below is an example of how to convert data from SQL server into a string and send the results in an email from database. This can be used to send scheduled reports. Also there is no need to create an executable in c# or any other language to send email reports in the below example.

  
/*Create temp table to store data.*/
DECLARE @temp TABLE (emp_id int,emp_name varchar(10))

INSERT INTO @temp (emp_id,emp_name) VALUES(1,'John')
INSERT INTO @temp (emp_id,emp_name) VALUES(2,'Tom')

DECLARE @result VARCHAR(4000)

/*Get results into a string*/
SELECT @result =
       (
       SELECT convert(varchar, emp_id)+','+emp_name+'\n'
       FROM @temp
       FOR XML PATH('')
       )

SELECT @result

/*Send the report to recipients*/
EXEC msdb..sp_send_dbmail @profile_name='TEST',
@recipients='someone@somewhere.com',
@subject='DB Job',
@body_format = 'HTML',

@body= @result

No comments: