TSQL to Send Email From a SQL Server With Attachment

There are about a billion different technologies used to connect remotely to SQL Servers and some are better than others.  Some allow file transfer, some allow sharing clipboards and some don’t.  Of course, better depends entirely on your perspective.  In most cases those ‘inconvenient’ things are there for a reason – security.  nonetheless it’s a pain when you have some nice query output that you want to drop into a CSV file and you can’t get it to your own laptop.  Well….here’s how.

First of all you need a few things set up.  you need:

  • Database Mail XP’s enabled.
  • A SQl Databasemail profile set up.
  • mail relaying enabled from the server you are sending from.

In other words, you aren’t going to sneakily move data off a server.  You have to have the rights to set those things up and the co-operation of a system administrator to get the mail sorted.  Now, if it’s just a query result you want sent back you can just use this:

USE msdb
EXEC sp_send_dbmail
@profile_name = ‘ManagedSQL’,
@recipients = ‘me@mymail.com’,
@subject = ‘ROSA OUTPUT’,
@body = ‘Daily Statistics are below.’,
@execute_query_database = ‘MYDB’,
@query = ‘SELECT * FROM [MYDB].[dbo].[tbl_color]’

But if you’ve already got a large output you may have to save it to the server and then send it as an attachment:

USE msdb

GO

EXEC sp_send_dbmail
@profile_name=’ManagedSQL’,
@recipients=’me@mymail.com’,
@subject=’Healthcheck Output’,
@body=’Server Healthcheck data attached.’,
@file_attachments=’C:\temp\config.txt’

Leave a Comment

Your email address will not be published.