Commands

[inet]

Hi All,

I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?

set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:\MyFolder\ErrorLogMsg.txt'

EXEC master.dbo.xp_cmdshell @cmd, no_output

SET @email = 'mailsend -f someone@mymail.com -d -smtp -t someone@mymail.com -sub "Error Log Errors" -m J:\MyFolder\ErrorLogMsg.txt'

EXEC master.dbo.xp_cmdshell @email, no_output


Thanks.

[nosepicker]

There are a few different ways to do this. This is potentially one:

set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:\MyFolder\ErrorLogMsg.txt'

EXEC master.dbo.xp_cmdshell @cmd, no_output

IF (select COUNT(*) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')) > 0
BEGIN

SET @email = 'mailsend -f someone@mymail.com -d -smtp -t someone@mymail.com -sub "Error Log Errors" -m J:\MyFolder\ErrorLogMsg.txt'

EXEC master.dbo.xp_cmdshell @email, no_output

END

[inet]

It works. Thank you very much for your help.