How to send an email from SQL database

Question

Post reply

How to send an email from SQL database

Elise-239769

Valued Member

Points: 64
More actions
June 16, 2005 at 1:40 pm

#362971

Hi,
I have this big dilemma and I am not sure where to start from, so any help will be appreciated:
I have a form on the web that is connected to an SQL server database running on MS server 2003.
I need to get that form filled back to an email, so that it can be printed and signed. To do this I have a million questions:
1 - I need to set up something that will allow me to send mail (ASP mail?). I've read that SQL mail is not the best choice and since I have not done anything yet, I am open to the easy or more efficient way. I will need somewhat specific instruction, as I am the newest of the newbies...
2 - Then I will need to tell the database to get that info that it has just received ans immediately send it to an address I specify, but it has to be sent on html format. How do I do this?
3 - I also need the SQL statement that tells the database that a new record has arrived, put it in a new form, send the form...
Am I trying to run before I can walk? I think so. But I am also pretty good at putting pieces together, so with the right directions and some codes hopefully I could do it.
Thank you so much in advance,
elise

Viewing 10 posts - 1 through 9 (of 9 total)

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

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 1

Will this work?

http://www.sqlteam.com/item.asp?ItemID=5908

You might also want to check out http://www.asp.net and see if there is anything on there you can use.

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

philcart SSC-Forever Points: 47794 More actions · Answer 2

Store the information from the form into a table in the database.

Create a stored procedure to format the data required in the body of the email into a string with appropriate HTML tags. Then use xp_smtp_sendmail (http://www.sqldev.net) to send the email.

--------------------
Colt 45 - the original point and click interface

Ken Klaft-191610 SSC Veteran Points: 270 More actions · Answer 3

Elise

You may want to research triggers in the SQL BOL. Also, here's another link that may or may not be helpful:

http://www.databasejournal.com/features/mssql/article.php/3489111

I'm not sure what the performance will be like but I wish you the best of luck. Please post your findings and results when you get things working. I'd be interested in the most efficient way to accomplish this.

Cheers,

Ken

Elise-239769 Valued Member Points: 64 More actions · Answer 4

Thank you all for the suggestions.

I am still evaluating which way is best for me to go.

I will continue this thread, though, so maybe it can help the next person...

Can I see an example of a stored procedure to format the body of the email?

Thank you.

philcart SSC-Forever Points: 47794 More actions · Answer 5

As an example of whats possible, I use this following function to email me reports about failure messages from my maintenance routines. At the end of the maintenance, if an error is found it calls a stored procedure to send out an email. This stored procedure calls this function to build the body of the message which contains the error message in a html table.

CREATE FUNCTION dbo.udf_Maint_MsgBody (
 @intMaintGroup int
 , @dteStart datetime
 , @dteFinish datetime
)
RETURNS varchar(8000)
AS
BEGIN
 DECLARE @MinID Int
 DECLARE @tblMsg TABLE ( ID int IDENTITY(1,1), msg varchar(250) )
 DECLARE @rtn varchar(8000)
 SET @rtn = ''

 INSERT INTO @tblMsg ( msg )
 SELECT
  '<HTML><HEAD><TITLE>Maintenance Errors</TITLE></HEAD><BODY><H4>'
  + 'Errors from maintenance group ' + CAST(@intMaintGroup as varchar(10)) 
  + ' run at ' + CONVERT(varchar(30), @dteStart, 113)
  + '</H4><HR><P><P><TABLE BORDER=1><TR><TH ALIGN=LEFT>Process</TH>'
  + '<TH ALIGN=LEFT>ErrCode</TH><TH ALIGN=LEFT>Message</TH></TR>'
 UNION ALL
 SELECT 
  '<TR><TD><TT>' + [Process] + '</TT></TD><TD><TT>' + [ErrCode] 
  + '</TT></TD><TD><TT>' + [Message] + '</TT></TD></TR>'
 FROM [DBA].[dbo].[tblMessageLog] 
 WHERE MsgDate BETWEEN @dteStart AND @dteFinish 
 AND Process LIKE 'usp_Maint%' 
 AND ErrCode <> ''
 UNION ALL
 SELECT'</TABLE><HR></BODY></HTML>'
 SELECT @MinID = MIN([ID]) FROM @tblMsg
 WHILE @MinID IS NOT NULL
 BEGIN
  SELECT @rtn = @rtn + [msg]
  FROM @tblMsg WHERE [ID] = @MinID
  SELECT @MinID = MIN([ID]) FROM @tblMsg
  WHERE [ID] > @MinID
 END
 RETURN @rtn
END

--------------------
Colt 45 - the original point and click interface

James Asante Old Hand Points: 361 More actions · Answer 6

Hi Phil,

I have installed the DTS SMTP Mail task.exe but getting the error message:

"The procedure entry point GetAddrInfoW could not be located in the dynamic link library WS2_32.dll"

I have admin rights on the sql 2000 server but not a professional DBA.

How can I correct the error?

Thanks.

philcart SSC-Forever Points: 47794 More actions · Answer 7

Sounds like the infamous DLL versioning problem. The DTS SMTP Mail task is expecting a procedure to exist in the DLL but it doesn't.

Haven't played with that custom task myself, I just use the extended stored procedure xp_smtp_sendmail.

--------------------
Colt 45 - the original point and click interface

Brad Scott Grasshopper Points: 20 More actions · Answer 8

I'm in the same boat. I've installed DTS SMTP Mail Task on my XP box running MSDE 2000A and it worked fine. I then installed it on a Windows 2000 AS (SP4) running SQL 2000 (SP3a) and I received the same error.

If I try manually registering the DLL I receive the following error message: LoadLibrary("smtptask.dll") failed. - The specified procedure could not be found.

Any assistance would be appreciated.

Brad Scott Grasshopper Points: 20 More actions · Answer 9

I have an email into sqldev.net. Once I have their reply, I will post it.