Post reply

sp_send_dbmail problems

  • February 3, 2010 at 4:47 pm

    #386241

    I'm trying to get this query to send an email to a defined group either A,B,or C if the result set is greater then 1.

    I continue to have issues with the recipient and with the URL I try to define. Any help would be appreciated.

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'administrator',

    @recipients = 'user@domain.com',

    @query = 'Select

    Owner = a1.User_Display

    ,Recipient = Case When Left(ReqOwner_TicketNum,2) = 'MN' Then 'GroupA@domain.com' When Left(ReqOwner_TicketNum,4) = 'ITON' Then 'GroupB@domain.com' Else 'GroupC@domain.com' End

    ,Creator = a2.User_Display

    ,Status = Status_Name

    ,Ticket = ReqOwner_TicketNum

    ,Subject = ReqOwner_Subject

    ,URL = 'Http://Sitename/Support.aspx?id=' + Convert(varchar(10),ReqOwner_PK)

    from ReqOwner

    Inner Join AppUser a1 on ReqOwner_UserFK = a1.User_PK

    Inner Join AppUser a2 on ReqOwner_AssignBy= a2.User_PK

    Inner Join Status on Status_PK = ReqOwner_StatusFK

    Where ReqOwner_Priority = 3 and ReqOwner_IsComplete=0 and

    ReqOwner_Interested=0 and dateDiff(hour,ReqOwner_AssignDate, GetDate() - 2) > 1'

    @subject = 'Need To RESOLVE Issue',

    @attach_query_result_as_file = 1 ;

  • February 3, 2010 at 5:28 pm

    #1113356

    You have to define the recipients outside of the @query parameter.

    I'd use that query to build a temp table and assign the related value to @recipients.

    @query then would be something like 'Select * from #temp'


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply