Professional Web Applications Themes

Auto E-mail results of a query - Microsoft SQL / MS SQL Server

Greetings, I have a table named Pendings which I have created the following view: SELECT MlsNum, COALESCE (AddNum, N'') + COALESCE (N' ' + AddDir, N'') + N' ' + COALESCE (AddStreet, N'') AS Address, City, Status, ListAgt, ListAgtPhn, ListAgtEmail, ListOfc, ListOfcPhn, ListDate, CloseDate FROM dbo.Pendings WHERE (CloseDate <= DATEADD(d, 0, GETDATE())) The table is updated every 24hrs. My goal is when the table is updated, it will fire off an email to each record that falls under this query. Each record contains the email address 'ListAgtEmail' of the recipient along with other information that I want to use in ...

  1. #1

    Default Auto E-mail results of a query

    Greetings,

    I have a table named Pendings which I have created the following view:

    SELECT MlsNum, COALESCE (AddNum, N'') + COALESCE (N' ' + AddDir, N'') +
    N' ' + COALESCE (AddStreet, N'') AS Address, City, Status,
    ListAgt, ListAgtPhn, ListAgtEmail, ListOfc, ListOfcPhn,
    ListDate, CloseDate
    FROM dbo.Pendings
    WHERE (CloseDate <= DATEADD(d, 0, GETDATE()))

    The table is updated every 24hrs. My goal is when the table is updated, it
    will fire off an email to each record that falls under this query. Each
    record contains the email address 'ListAgtEmail' of the recipient along with
    other information that I want to use in the body of the email such as
    'Address' and 'CloseDate'. I have my SQL mail configured and I have looked
    at xp_sendmail and xp_smtp_sendmail and I have looked for examples on BOL
    and the newsgroups, but cannot find anything that pertains to what I am
    looking to do. The examples I have found, direct me to send an email with
    an attached query. What I want is to query and send the results to the
    recipients with data that is in the records. Does anyone have any
    recommendations? Examples? I am running SQL 2000 and Exchange2000. Any
    input would be appreciated. Thanks.

    Greg


    Greg Guest

  2. #2

    Default Auto E-mail results of a query


    Hi Greg if I understood u correctly, you want to send
    emails to few receipients who fall under this query's
    condition.I think you have a job which will update
    Pendings table.Inorder to send emails for eligible people
    we should have one more job which runs after the previous
    job and here is a sample code

    DECLARE MinId int
    , ListAgtEmail varchar(50)
    , Address varchar(100)
    , CloseDate datetime
    , MessageBody varchar(100)

    Create Table Pendings (ListAgtEmail varchar
    (50),Address varchar(100),CloseDate datetime)

    INSERT Pendings values
    ('com','1234 De Soto Ave','01/01/2003')
    INSERT Pendings values
    ('com','1235 De Soto Ave','08/01/2003')
    INSERT Pendings values
    ('com','1236 De Soto Ave','09/01/2003')
    INSERT Pendings values
    ('com','1237 De Soto Ave','07/01/2003')

    select ListAgtEmail
    , Address
    , CloseDate
    , IDENTITY(INT,1,1) as PendingID
    into #Pendings
    from Pendings
    where (CloseDate <= DATEADD(d, 0, GETDATE()))

    select MinId = Min(PendingID)
    from #Pendings

    while MinId is Not NULL
    begin

    select ListAgtEmail = ListAgtEmail
    , Address = Address
    , CloseDate = CloseDate
    from #Pendings
    where PendingID = MinId

    select MessageBody = ' Closing Date is '
    + convert(varchar,CloseDate) + ' Address is ' + convert
    (varchar,Address)

    EXEC master..xp_sendmail
    ListAgtEmail,MessageBody

    select MinId = Min(PendingID)
    from #Pendings
    where PendingID > MinId


    end

    HTH,
    Srinivas Sampangi 
    following view: 
    (N' ' + AddDir, N'') + 
    ListOfc, ListOfcPhn, 
    table is updated, it 
    this query. Each 
    recipient along with 
    email such as 
    and I have looked 
    examples on BOL 
    pertains to what I am 
    send an email with 
    results to the 
    have any 
    Exchange2000. Any 
    sampangi Guest

  3. #3

    Default Re: Auto E-mail results of a query

    Thanks for the input. I appreciate it.

    Greg

    "Greg" <com> wrote in message
    news:phx.gbl... 

    it 
    with 
    looked 


    Greg Guest

Similar Threads

  1. Complex join = no results (for a query that shouldreturn results)
    By jchapman16 in forum Coldfusion Database Access
    Replies: 4
    Last Post: August 23rd, 10:49 PM
  2. cfmail query results in mail message
    By jasun123 in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: May 31st, 06:33 PM
  3. Query results don't display properly in results table.IGNORE PREVIOUS
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 24th, 07:28 PM
  4. Query results don't display properly in results table.
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 24th, 07:17 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139