SQLMail send out a reminder 2

[ironhide1975]

Greetings and Salutations,

I have developed several systems using ASP and SQL server 2000 here at my occupation. We would like to have an automated response be sent out, something that would run every day on the SQL end to notify individuals if something is late or going to be late. I have several tables in the database that contain where to send the email too, and the different applications. The SQL portion can be done with a simple view, what I need to know how to do is within SQLMail pull the email address out of the view and send the specialized email to someone.

So for example, in our customer complaint system. Before two days a response follow up is due, I need an email to go out to the individual who started the complaint to be notified.

Can anyone direct me or help me figure out how to do this? If so it would be most appreciated. Many thanks in advance.

 

[dbomrrsm]

This help ?

CREATE PROC uspSendThemMail
AS BEGIN
  SET NOCOUNT ON

  DECLARE @em nvarchar(100), @rc int

  DECLARE YourCursor CURSOR
  FOR SELECT EmailAddress FROM ...

  OPEN YourCursor
  FETCH NEXT FROM YourCursor INTO @em

  WHILE @@Fetch_Status = 0 BEGIN

    EXEC @rc = master.dbo.xp_smtp_sendmail
      @FROM       = N'MyUsername@xxx.com',
      @TO         = @em,
      @priority   = N'HIGH',
      @subject    = N'Hello SQL Server SMTP Mail',
      @message    = N'Goodbye MAPI, goodbye Outlook',
      @type       = N'text/plain',
      @server     = N'aaaaa'

    SELECT Email = @em, RC = @rc

    FETCH NEXT FROM YourCursor INTO @em

  END

  CLOSE YourCursor
  DEALLOCATE YourCursur
END

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Catadmin]

Don't forget, you also have to have an account for SQL Server Agent & SQL Server that is mapped to an Exchange mailbox in order for SQL Mail to work.

Once you have the Account mapped to an Exchange profile, pointing to the Exchange box and the mailbox, then you should be set to code your stored procedure, etc.

Check Books Online using SQL Mail for details.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[ironhide1975]

I already use this method for my salesman reporting. If you can write a query that selects the starting person's email address then this method works. The query can only process one row of data at a time. So, if the query would select more than one row then each of those rows selected would receive an email.