asked
A condition in a stored procedure that determines the email sent
I have a stored procedure that runs a query. The output of the query is emailed to a group of recipients as an attachment. The output for the query can sometimes be blank. I want to put in a condition that if there is no data, then send "No Data" in the email. Otherwise send the output as an attachment. I need help in figuring out how to do that. Thanks on advance for the help
You can send query results as attachment using the methods specified here:
http://calyansql.blogspot.in/2013/03/sending-query-results-to-email-using.html. Have a condition to check result and if there is no data, then specify the body of the message as "No Data" and @attach_query_result_as_file = 0. On configuring database mail, refer to this post :
http://www.sql-server-performance.com/2006/email-functionality/
I think you misunderstood my question. I have a stored procedure that runs a bcp command to execute another stored procedure that produces a list of data as a .txt file. Another command then emails that .txt file to a list of recipients. Sometimes that list is blank. If the list is blank, then I want the email subject line to be "No Data today" otherwise send the .txt file as an attachment. How can I do this?
Check the size of the generated file using the reference here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55812 and if file is having data, size will be > 0 and accordingly you can send mail.
I have a related question about the conditional email. This is my code: If EXISTS(query) SET @varCheck = 1 ELSE SET @varCheck = 0 BEGIN IF @varCheck = 1 --grabs the file from the folder and deposits it in another folder, ftp's it and sends email with the file attached EXEC msdb.dbo.sp_send_dbmail @recipients=a list @subject =' File sent', @profile_name ='IT Email', @file_attachments ='N:\folder\File.txt' IF @varCheck = 0 EXEC msdb.dbo.sp_send_dbmail @recipients=a list @subject =' No File to Process Today', @profile_name ='IT Email' END It is working correctly if there is data in the file. But it is sending 2 emails when there is no data. What am I doing wrong? Please help
@AlphaKilo Yes, it will send 2 emails when there is no data. You have a BEGIN/END block which has 2 independent IF statements. The first IF is meant to send email only if the @varCheck = 1 is true, but the problem is that it does not have the BEGIN/END, and therefore, only one line of code directly under this statement is executed. That line happens to be the comment line " --grabs the file...". There is nothing to execute on that line. After that, the next line is executed (sending email) regardless of the @varCheck value. The next IF statement causes the email to be sent only if @varCheck = 0 is true. So, the @varCheck = 1 causes only one email to be sent while @varCheck = 0 causes 2. Please add the line reading **BEGIN** after the IF statement but before the comment line and then add the line reading **END** after the exec line: IF @varCheck = 1 begin -- the begin above is needed --grabs the file from EXEC msdb.dbo.sp_send_dbmail @recipients=a list @subject =' File sent', @profile_name ='IT Email', @file_attachments ='N:\folder\File.txt' end;
I have a command to ftp after the comment --grabs the file... set @sqlcommand = '"c:\progra~2\winscp\
winscp.com /script=c:\progra~2\winscp\FTSPut.txt"' set @sqlcommand = '"c:\progra~2\winscp\
winscp.com /log=N:\folder\FTPLOG\log.txt /script=c:\progra~2\winscp\FTSPut.txt /parameter "''N:\folder\*.txt"' exec xp_cmdshell @sqlcommand I think It will work when I change the BEGIN/END statements. Thank you Oleg. I will let you know after I test it out.
14 People are following this question.
