• Follow
  • Follow
Freelance web developer
Hire Me!
  • Home
  • Blog

Real Code Ltd

Hire Me!
  • Home
  • Blog

Output SQL Queries to an email address

by Brad | Dec 10, 2012 | SQL Server

Once you have a user profile in SQL server 2008 that can send email saving something along the lines of the below as a job will attach a textfile containing the output of a query and send it to an email address in a rather tidy fashion.

 

DECLARE @Filename VARCHAR(50) = ‘Somefilename’ + REPLACE(CONVERT(VARCHAR,GETDATE(),103),’/’,’-‘) + ‘.csv’

EXEC msdb.dbo.sp_send_dbmail
@execute_query_database= [NAMEOFDATABASE],
@profile_name = [USERPROFILENAME],
@recipients = ‘[email protected];[email protected]’,
@body = ‘Something light and fluffy for email body text’,
@sensitivity = ‘Confidential’,
@subject = ‘The subject of your email’,
@query = ‘SELECT * from SomeTable’,
@query_attachment_filename = @Filename,
@attach_query_result_as_file = 1,
@query_result_width=32500,
@query_result_separator=’,’,
@query_result_no_padding = 1

@attach_query_result_as_file is a boolean that makes the output either an attachment or if you prefer set to false (default) to become the email body text

@query_result_width to the upper range of integer gets around wrapping text problems to make output nicer

@query_result_separator defines the column delimiters for your output

@query_result_no_padding trims white space from column output if you desire

Categories

  • AppSumo
  • ASP.NET
  • C#
  • Coding
  • Crunch Accounting
  • GiffGaff
  • Lebara Mobile
  • Linux
  • Octopus Energy
  • RackNerd
  • Recommendations
  • Revolut
  • Security
  • SEO
  • SQL Server
  • Technology
  • Tips
  • Videos
  • Virgin Active
  • Visual Studio
  • Vitality Insurance
  • Windows 8


Email

[email protected]



Support

[email protected]



Call

0345 463 4637

Navigation

  • Home
  • Blog

Get Social!

  • Follow
  • Follow

Stay Connected

Success!

Subscribe

Copyright © 2023 Brad McAllister

Privacy Policy