-2

I can execute the query via batch file and send it to the folder but I need to send the results to an email and I am not sure how to send the sql query/sp results in an email via batch file.

Any suggestions and help is appreciated.

Thanks

Alica Shah
  • 11
  • 1
  • 8

1 Answers1

0

It sounds like you're executing your SQL query and I'm guess dumping it to a text file (let's say output.txt). I'm assuming Windows and I'm assuming SQL Server since you didn't specify. The answer is below, but I would like to offer two options instead:

OPTION 1: Setup SQL Server to send your email which would have several benefits including:

  • SQL Agency can control scheduling & logging
  • You get a SQL-Only solution
  • Email is centrally managed instead of being in a bunch of different batch files.

It's not hard to setup email in SQL and is clearly explained here:

How to send email from SQL Server?

OPTION 2: Use PowerShell to make your SQL call.

The answer to your question (below) uses PowerShell to send the email message. Since you'll use PS for that you might as well use PS to make your database call. You can find hundreds of examples of how to call SQL from PS.

Finally the answer you asked for...

If you want to continue to use a command line solution (maybe you have to kick off a bunch of non-SQL commands) you'll need to use PowerShell (assuming you don't want start making things complicated, like writing your own .exe).

STEP 1: Read the contents of the query (output.txt) by using:

$sqlOutput = Get-Content Output.txt -Raw

STEP 2: Use $sqlOutput as the body of the email. Here's a nice walkthrough:

http://www.howtogeek.com/120011/stupid-geek-tricks-how-to-send-email-from-the-command-line-in-windows-without-extra-software/

STEP 3: If you want to attach the file as an attachment, you can use the example here:

https://msdn.microsoft.com/en-us/library/system.net.mail.attachment(v=vs.110).aspx

Community
  • 1
  • 1
Robert Paulsen
  • 4,935
  • 3
  • 21
  • 27
  • Ok I tried that too.. I created my account and profile in Database mail and when I execute it says “Mail queued” and when I check the log file I get the message below: The mail could not be sent to the recipients because of the mail server failure. Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond) Am I missing something here? Thanks – Alica Shah Jan 20 '16 at 16:37
  • So there is an issue with connecting to the mail server. There are three likely issues. (1) You've got the wrong server; you'll need to point to a server that is setup to handle SMTP. (2) Often servers will be 'locked down' so they aren't used for spam. This can be because (a) your credentials are wrong or (b) the server/IP you're sending from needs to be white-listed by the SMPT server. SQL stuff can be tricky since things run under various accounts. You could try getting one of the other solutions working so you know you CAN send email, then try getting SQL setup. – Robert Paulsen Jan 21 '16 at 02:48