CODEWITHSUNDEEP
sqlsql-serverbatch-file
user2317587
user2317587

Reputation: 1

Store sql query output and send it in email to DL or particular email ID

I want to run sql query from a .bat file. I understand how to create a .bat file and .sql file and then call .sql file from the .bat file. My question is how can I store its output under any location and then send it over to email.

My current .bat looks like 

osql -S <ServerName> -U <User Name> -P <Password> < mysqlfile.sql

...And my .sql file runs the below query

select * from sysssislog where event='onerror' order by starttime desc

Appreciate your help.

Upvotes: 0

Views: 1771

Answers (2)

Matt Williamson
Matt Williamson

Reputation: 7095

I'd use Blat or a VBscript to do it. 

osql -S <ServerName> -U <User Name> -P <Password> -o Out.txt < mysqlfile.sql
Blat out.txt -to <recipient> -s <subject> -server <ip or hostname of SMTP server:<port>

Here is a Batch file that creates a VBScript to do it. 

@echo off
setlocal

if exist "out.txt" del "out.txt"
osql -S Server -U user -P password -o Out.txt < mysqlfile.sql
set vbs_=Email.vbs
set skip=
findstr "'%skip%VBS" "%~f0" > "%vbs_%"
cscript //nologo "%vbs_%"
ping -n 3 localhost>nul
if exist "%vbs_%" del "%vbs_%"
goto :eof

 Const ForReading = 1, ForWriting = 2                      'VBS
 sFile="Out.txt"                                           'VBS
 Set oFso = CreateObject("Scripting.FileSystemObject")     'VBS
 Set oFile = oFso.OpenTextFile(sFile, ForReading)          'VBS
 Set oMail=CreateObject("CDO.Message")                     'VBS
 sSchema="http://schemas.microsoft.com/cdo/configuration/" 'VBS
     with oMail.Configuration.Fields                       'VBS
     .Item(sSchema & "sendusing")= 2                   'VBS  
     .Item(sSchema & "smtpserver")= "YourServer"       'VBS
     .Item(sSchema & "smtpserverport") = 25              'VBS 
     .Update                                           'VBS                                              
     end with                                              'VBS 
 oMail.Subject="SQL Log"                                   'VBS
 oMail.From="[email protected]"                             'VBS
 oMail.To="[email protected]"                           'VBS
 oMail.TextBody = oFile.ReadAll                            'VBS 
 oMail.Send                                                'VBS
 oFile.Close                                               'VBS 
 set oMail=nothing                                         'VBS
 set oFile = Nothing                                       'VBS

Upvotes: 0

Michael Harmon
Michael Harmon

Reputation: 746

You can set up SQL Server 2008 Database mail as shown here

Then, you can Email the results of a query as shown here

Upvotes: 2

Related Questions