Send Emails Using SQL Server and rs.EOF redirection

http://www-level3.experts-exchange.com/questions/20738596/CDO-email-with-HTML-AND-attachement.html

http://www.eggheadcafe.com/articles/20010301.asp

http://www.databasejournal.com/scripts/article.php/1963221

Thru SMTP
Here is the sample.
http://www.sqlteam.com/item.asp?ItemID=5003

oh, thanks... let me work on that...

There are many ways to do it. Using xp_sendmail (Extended Proc)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6hbg.asp

I am done now.

:)

Configuring Mail Profiles
----------------------------------
The SQL Mail session must be started prior to executing xp_sendmail. Sessions can be started either automatically or with xp_startmail  SQL Mail and SQLAgentMail can use the same or different mail profile. If necessary, each mail profile can be configured within its own domain account.

Configuring a SQL Mail Profile
When configured, mail profiles are specific to the Microsoft® Windows NT® 4.0 or Windows® 2000 user domain account that is activated when a user logs on to Windows NT 4.0 or Windows 2000 successfully. SQL Mail must have a mail profile created in the same user domain account or context that is used to start an instance of Microsoft SQL Server™. When a mail stored procedure is executed, SQL Mail looks for the defined mail profile in the domain account that triggered it.
If you plan to use mail stored procedures you must:
·      Have a mail server that is extended MAPI-compliant.
·      Configure a mail profile for MSSQLServer to use to connect to your mail server.
How to configure a mail profile (Windows)
To configure a mail profile
1.      Log on to the Microsoft® Windows NT® 4.0 or Microsoft Windows® 2000 server by specifying the name and password used to start Microsoft SQL Server™ services.
2.      In Control Panel, double-click the Mail icon to create and configure or copy an existing mail profile. Select the appropriate mail service to interact with your mail host. Choose a name for the profile that will help the recipient identify the messages.
3.      On the instance of SQL Server, start the mail client using the newly created mail profile.
4.      Send a message addressed to the same profile name to ensure that the mail client, mail profile, and e-mail provider are working properly.
If your e-mail message does not appear, you may need to establish mail synchronization by going to the Tools menu and then clicking Deliver Now.
How to set up SQL Mail (Enterprise Manager)
To set up SQL Mail
1.      Expand a server group, and then expand a server.
2.      Expand Support Services, right-click SQL Mail, and then click Properties.
3.      In the Profile name list, type or select the mail profile that you configured for SQL Mail.
4.      Click Test to check the ability of the system to start and stop mail client services with the profile entered.
You should receive a message that SQL Mail has started successfully and stopped a mail session with this profile.
How to set up SQLAgentMail (Enterprise Manager)
To set up SQLAgentMail
1.      Expand a server group, and then expand a server.
2.      Expand Management, right-click SQL Server Agent, and then click Properties.
3.      If you have configured a mail profile using a domain account different from the one used by Microsoft® SQL Server™, click This account, and then enter the Microsoft Windows NT® 4.0 or Windows 2000® account name and password used to create the mail profile for SQLAgentMail.
4.      In the Mail profile box, select the mail profile you created for SQLAgentMail.
5.      Click Test to check the ability of the system to start and stop mail client services with the profile entered.
You should receive a message that SQL Mail has started successfully and stopped a mail session with this profile.
Sharing a Profile with SQL Mail
SQL Mail and SQLAgentMail sessions may be configured to use the same Windows NT 4.0 or Windows 2000 domain user account. When using the same domain account, SQL Mail and SQLAgentMail can share the same mail profile using a common mailbox.
SQL Server uses two separate mail sessions:
·      The MSSQLServer service uses a mail session that is referred to as SQL Mail.
SQL Server uses this mail session when database applications execute the xp_sendmail extended stored procedure to send a message or query result set to a recipient.
·      The SQLAgentMail uses a mail session that is exclusive to SQL Server Agent activities.
Creating Separate Profiles
You can configure separate mailboxes for SQL Mail and SQLAgentMail by creating separate mail profiles. There are two ways to accomplish this:
·      Use separate domain accounts for each service. This requires you to configure a mail profile for each user account.
·      Use the same domain account for each service and create multiple mail profiles.
For more information about setting up and configuring mail clients and mail profiles, see the Windows NT 4.0 and Windows 2000 documentation.

Note:- (MAPI)Messaging Application Programming Interface; a system built into Microsoft Windows that enables different e-mail programs to interface to distribute e-mail. When both programs are MAPI-enabled, they can share messages.

So I create a procedure like:


Create Procedure sp_SMTPMail

      @SenderName varchar(100),
      @SenderAddress varchar(100),
      @RecipientName varchar(100),
      @RecipientAddress varchar(100),
      @Subject varchar(200),
      @Body varchar(8000),
      @MailServer varchar(100) = 'localhost'

      AS      
      
      SET nocount on
      
      declare @oMail int --Object reference
      declare @resultcode int
      
      EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
      
      if @resultcode = 0
      BEGIN
            EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
            EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
            EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
      
            EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress
      
            EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
            EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body
      
      
            EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL
      
            EXEC sp_OADestroy @oMail
      END      
      

      SET nocount off
GO


//// So where do I specify to send emails when table INVENTORY gets new record?
This code is weird to me

call your proc in insert trigger of INVENTOTY table to send email.

CREATE TRIGGER tri_Inventory ON Inventory
FOR INSERT
AS
BEGIN

If Exists(Select 1 from Inserted)
BEGIN
  // Add you code as necessary to send email.
END



END
GO

Sorry, I do not know what code to add to send emails. Is there a way you can be more specific?
Let's say: on insertion to table INVENTORY an email with subject "New Item Added" is emailed to destination@mail.com from (if needed) sender@mail.com .  
Thank you for your time. I really appreciate.

"The stored procedure definition must include name and text" error is given to me when I try to save it. Please, advise.
Thank you.

By the way, that is exactly what I insert from your last answer above. I might be missing something:

CREATE TRIGGER tri_Inventory ON Inventory
FOR INSERT
AS
BEGIN

If Exists(Select 1 from Inserted)
BEGIN
  // Add you code as necessary to send email.
  exec sp_SMTPMail 'YourName', 'name.name@company.com', 'Recipent Name', 'a.a@a.com', 'test', 'Test Message'
END

END
GO

Thank you very much.

Try this. (This line // Add you code as necessary to send email. should not be in there.)
Replace emailids and names, messages with actual emailids, names and messages as well.

CREATE TRIGGER tri_Inventory ON Inventory
FOR INSERT
AS
BEGIN

  If Exists(Select 1 from Inserted)
  BEGIN
    exec sp_SMTPMail 'YourName', 'name.name@company.com', 'Recipent Name', 'a.a@a.com', 'test', 'Test Message'
   END
END

Yes... that's exactly what I did. I took // line away too. And that's what error I get. Let me try to play around with it.
Thank you. I appreciate your time.

Let me cear up what I am doing:
I open my DB and click the 'Stored Procedures'. There I simply paste the code below and try to save it by clicking OK. I then get the error message:
"Error 21001 [SQL-DMO] Stored Procedure Definition must include name and text (for Standard StoreProcedure) or libraryname (for Extended StoredProcedure)"
 


///// Code I pasted (note: I erased PROCEDURE ....):

CREATE TRIGGER tri_Inventory ON INVENTORY
FOR INSERT
AS
BEGIN

If Exists(Select 1 from Inserted)
BEGIN
  exec sp_SMTPMail 'Fname Lname', 'me@site.com', 'Fname Lname', 'me@site.com', 'Record Added', 'Record Is Successfully Added to the Database.'
END

END
GO

Please, help!!!

Why are you pasting the trigger into stored procedure.

You cannot call triggers from stored procedures. You have to create triggers separately. If using Query Analyser open a new sql winodw and past the code for trigger into and press F5 to compile.

This trigger gets fired automatically when a row is added into Invontory table. That trigger then calls sp_SMTPMail  to send an email

That's why I am here... I really do not know SQL Server that much except all the similarities with Access. Thanks for telling.
The error I get is 'Invalid object name INVENTORY'. That's the name of my table, isn't it?. Where do I specify the name of the database?
Please, advise.
Thanks a lot.

OK. Verify if the name of the table you are trying to add this trigger to is "INVENTORY". If table INVENTORY does not exist in your database then find out the actual name of the inventory table and replace below with the actual table name.

CREATE TRIGGER tri_Inventory ON INVENTORY -- <---This is the table name
FOR INSERT
AS
BEGIN

If Exists(Select 1 from Inserted)
BEGIN
  exec sp_SMTPMail 'Fname Lname', 'me@site.com', 'Fname Lname', 'me@site.com', 'Record Added', 'Record Is Successfully Added to the Database.'
END

END
GO

Thank you for a very detailed explanation. The table INVENTORY exists in a DB because I created it by myself. So everything should be fine with that. The question is how do I tell this procedure to find INVENTORY table in a MANAGE database? If it does by itself that's fine... I just don't know. Otherwise, that's all I have to do to send emails?
Thanks a lot.

> The question is how do I tell this procedure to find INVENTORY table in a MANAGE database?

If you create this proc and trigger in MANAGE database everything should be fine.

Sorry for asking but how can I delete this trigger? I can't find in a list of stored procedures

OK I found

Microsoft OLE DB Provider for SQL Server error '80004005'
Could not find stored procedure 'sp_SMTPMail'.

This is what I get.

I used this template

CREATE TRIGGER tri_Inventory ON Inventory
FOR INSERT
AS
BEGIN

If Exists(Select 1 from Inserted)
BEGIN
  exec sp_SMTPMail 'YourName', 'name.name@company.com', 'Recipent Name', 'a.a@a.com', 'test', 'Test Message'
END

END
GO

This proc must be created in the database where you are trying to create this trigger.

Thanks... it works...