Solved

Send Emails Using SQL Server and rs.EOF redirection

Posted on 2003-11-05
26
360 Views
Last Modified: 2012-06-21
Hi,

I am not an expert in SQL Server, however, I know how to use it in a simple way. I created the tables, relationships, connectivity, etc. I really did not try using Mail from SQL Server but need to do it badly. I have Outlook installed for MAPI.
Could somebody tell a simple good way of how I can send emails once smb adds a new record it a table.

Also, additional question: if rs.EOF <> true then how can I send a user back prompting for correct input and displaying a short message like "make sure you enter correct data"?

Thank you for your time.
0
Comment
Question by:Vadymus
  • 14
  • 11
26 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9689642
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9689675
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9689686
Thru SMTP
Here is the sample.
http://www.sqlteam.com/item.asp?ItemID=5003
0
 

Author Comment

by:Vadymus
ID: 9689697
oh, thanks... let me work on that...
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9689730
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.

:)
0
 

Expert Comment

by:rajeshjacob3
ID: 9693396
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.






0
 

Author Comment

by:Vadymus
ID: 9694767
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
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9700818
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
0
 

Author Comment

by:Vadymus
ID: 9715396
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.
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 30 total points
ID: 9715512
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

0
 

Author Comment

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

Author Comment

by:Vadymus
ID: 9715968
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.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9716404
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
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:Vadymus
ID: 9717125
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.
0
 

Author Comment

by:Vadymus
ID: 9733132
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!!!
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9733243
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

0
 

Author Comment

by:Vadymus
ID: 9733500
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.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9733564
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
0
 

Author Comment

by:Vadymus
ID: 9733623
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.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9733797
> 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.
0
 

Author Comment

by:Vadymus
ID: 9733980
Sorry for asking but how can I delete this trigger? I can't find in a list of stored procedures
0
 

Author Comment

by:Vadymus
ID: 9733997
OK I found
0
 

Author Comment

by:Vadymus
ID: 9818063
Microsoft OLE DB Provider for SQL Server error '80004005'
Could not find stored procedure 'sp_SMTPMail'.

This is what I get.
0
 

Author Comment

by:Vadymus
ID: 9818071
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
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9833106
This proc must be created in the database where you are trying to create this trigger.
0
 

Author Comment

by:Vadymus
ID: 9837848
Thanks... it works...
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now