Results 1 to 3 of 3

Thread: Commands

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Commands

    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.

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Since you are cmdshell everything, you can cmdshell the following"

    'type J:\MyFolder\ErrorLogMsg.txt | find /I /C "error" ' and see if count is greater than 1, which would indicate error text in output file.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    or, how about this:

    Code:
    IF EXISTS (select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like '%Error: %') )
    BEGIN
         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
    END
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •