SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Sending SMTP Mail using a Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 5

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/22/2001 :  15:02:16  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
One of the downsides of SQL Mail is the fact that it is tied to a MAPI Profile. Here is a way to send email using the sp_OACreate procedure to call a third party SMTP component.

Article Link.

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/23/2001 :  15:08:31  Show Profile  Reply with Quote
Hey Merk, nice article man. Mail alerts are going to be a big part of the app I'm working on. Hadn't gotten to dealing with them just yet but when I do I'll be coming back to this article. Thanks

Justin



Edited by - justinbigelow on 07/23/2001 15:08:59
Go to Top of Page

nizmaylo
Constraint Violating Yak Guru

USA
258 Posts

Posted - 07/23/2001 :  16:40:22  Show Profile  Reply with Quote
Damian,
you proc is a big help.
Simple question here:
How do you format the e-mail body?

helena
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 07/23/2001 :  18:21:43  Show Profile  Visit Merkin's Homepage  Reply with Quote
quote:

How do you format the e-mail body?



Glad you like it

Two ways of formatting it. One is insert CR LFs ascii characters 10 and 13 (I think), or the other way is modify the code to the COM object to set the content type to HTML, then build an HTML string with appropriate formatting.



Damian
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 07/23/2001 :  20:23:09  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Not to be too picky, but CRLF is 13:10. 10:13 is backwards, and I don't know what happens if you do that.

MC


Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 07/23/2001 :  20:34:31  Show Profile  Visit Merkin's Homepage  Reply with Quote
Could be interesting

Thanks for that, good catch. Thats what happens when I post before I have had a coffee.




Damian
Go to Top of Page

solart
Posting Yak Master

USA
148 Posts

Posted - 07/24/2001 :  11:03:10  Show Profile  Reply with Quote
I have a SMTP server, which when it gets mail can reroute the mail to the individual addressed. How can the sp_SMTPMail procedure be modified to use my in-house SMTP server?

Thanks!!!!

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 07/24/2001 :  11:34:31  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Great article Damian! The timing on this is perfect. I was just trying to figure out why I was having trouble sending SQL mail from our QA Server, but could do it fine from my local dev box... Of course it's because my local machine HAS a MAPI client installed, but the other does not.

Now, a question... Would you expect that I could use the CDONTS object, like I do in ASP pages, to send mail from SQL Server?

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 07/24/2001 :  18:21:31  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi all

solart : This is exactly what it is designed to do. Pass your server's address in to the @Mailserver parameter.

AjarnMark : Yes, you could easily change it to use any mail component. The example shows how to set properties and execute methods, just replace it with CDONTS relevant ones.





Damian

Edited by - merkin on 07/24/2001 18:22:00
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 07/26/2001 :  19:42:27  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
FYI - Here's the modified script to get this to work with CDONTS (which is free).

Create Procedure sp_SMTPMail
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000)
AS
SET nocount on

declare @oMail int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT

if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body

EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL

EXEC sp_OADestroy @oMail
END


SET nocount off
GO


To test, call with something like:

exec sp_SMTPMail @SenderName='You', @SenderAddress='YourName@YourMailSite.com',
@RecipientName = 'YouAgain', @RecipientAddress = 'YourName@YourMailSite.com',
@Subject='SQL Mail Test',
@body='This is a test message from SQL Server. Smile! It worked.'


The only catch is getting CDONTS installed on your SQL Server without dumping excess junk there. I found one of our SQL Servers had IIS installed on it by mistake, and CDONTS came with it.

Should this (and Merkin's Original) go into the Script Library Forum?

------------------------------------------------------------------
Contractor$ never die, they just leave for higher-paying project$.

Edited by - AjarnMark on 07/26/2001 19:43:49
Go to Top of Page

ckempste
Starting Member

Australia
1 Posts

Posted - 07/27/2001 :  00:19:33  Show Profile  Reply with Quote
Hi all

Only problem with all this is that its relying on a specific smtp server. I used this code a while back and found that if your in a segregated network, you may find that your local smtp server can not understand the email addressing in which to rely the messages to. Therefore your CDO code fails and it goes to the "bad" folder. Ideally this code needs to change to allow the user to alter the SMTP mail server in this case. After much buggering around I couldnt get it to work via stored procs and ended up writing a small VB DLL which passed in the smtp server and all worked fine.

This is the only "gotcha" for this code.

Cheers

Chris.

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 07/28/2001 :  02:05:55  Show Profile  Visit Merkin's Homepage  Reply with Quote
quote:

I do not know what is a third party SMTP component.



It is a component.

For SMTP (Simple Mail Transfer Protocol).

Produced by a 3rd party.

There is a link in the article to serverobjects, go there, buy the component, install it on your server. Then the code will work.



Damian
Go to Top of Page

filf
Yak Posting Veteran

67 Posts

Posted - 07/30/2001 :  04:42:58  Show Profile  Reply with Quote
I have got this working with AB Mailer but my biggest problem is the fact that I keep trying to set the body property and the sp:sp_OASetProperty keeps truncating my data, there is an article in BOL about why it does this. The sp coverts the SQL server data type to a Visual Basic data type, has anyone worked out how to get around this.

It would be a great help because it is the only thing which is causing me problems.


I have put a post in the dev forum saying pretty much the same thing:http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=8005&FORUM_ID=5&CAT_ID=3&Topic_Title=Truncation+Problem+using+OLE+automation+SP&Forum_Title=Developer.


If anyone knows a work around to this prob it would certainly improve my Monday. Thanks.




Go to Top of Page

gerbick
Starting Member

USA
11 Posts

Posted - 07/30/2001 :  12:41:24  Show Profile  Visit gerbick's Homepage  Reply with Quote
great article... now, I will be 100% honest... I don't know how to use this procedure with RKmail and a triggered by any insertion into a fail over database.

just call me confused.

great article btw.

___________________
gerbick
Go to Top of Page

filf
Yak Posting Veteran

67 Posts

Posted - 07/31/2001 :  11:23:59  Show Profile  Reply with Quote
Has no-one tried to send a mail longer than char(250), or is everyone just hitting the same brick wall that has stopped me, I am trying a workaround and if it works I will post it, has noone else come up against and or solved this string truncation problem.

There must be a way round it, I have a theory which I am currently testing, is there anyone out there with superior knowledge of visual basic data types?


Go to Top of Page

blake
Starting Member

Canada
29 Posts

Posted - 07/31/2001 :  17:38:43  Show Profile  Visit blake's Homepage  Reply with Quote
Your body parameter should be declared as:

@body varchar(255)

since there is absolutely no way around this 255 character limit when you are directly passing the body as a single parameter. There are some workarounds, however. See:

http://www.google.com/search?q=255+character+limitation+SQL

for some ideas on getting around the 255 character limit.

Go to Top of Page

filf
Yak Posting Veteran

67 Posts

Posted - 08/01/2001 :  05:10:16  Show Profile  Reply with Quote
Thanks Blake I had a look at some of those articles but unfortunately none help, I think I am going to have to bite the bullet and send mails no longer than 255 I am running SQL Server 7.0 and this is the quote from BOL that highlights my issue.

Because Microsoft® SQL Server™ uses Transact-SQL data types, and OLE Automation uses Microsoft Visual Basic® data types, the OLE Automation stored procedures must convert the data that passes between them.

These are the conversions from SQL Server to Visual Basic data types.

SQL Server data type Visual Basic data type
char, varchar, text ---> String


As far as I can see this is a known bug and there is no workaround, my tests have failed. You can BUT try. FILF


Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 08/01/2001 :  08:14:14  Show Profile  Visit Merkin's Homepage  Reply with Quote
DOH!

OK Sorry guys. You got me there, I didn't consider that one

I have another way of doing it which I am writing into an article now.

Give me a few days



Damian
Go to Top of Page

filf
Yak Posting Veteran

67 Posts

Posted - 08/01/2001 :  08:21:50  Show Profile  Reply with Quote
Looking forward to that one, especially if it involves a more efficient method of queueing the mails, this process is seriously resource intensive. I will try and look into it myself if I get the time.

FILF


Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 08/01/2001 :  13:52:23  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
At a previous company, we worked around this 255 limit by having a guy write a VB app that handled the message generation outside of SQL Server. We would write our email messages to a normal SQL table to be processed by this app. The message body was stored in a varchar(8000) field and we had a related table of recipients. The VB app would query the message table every 5 minutes for SentFlag = 0 and then generate the email message through Exchange. It was NOT a pretty sight, but it seemed to do the trick. The app was also written to handle bounce-back messages, etc.

I'm looking forward to Merkin's solution, which I'm sure will be more elegant.

------------------------------------------------------------------
Contractor$ never die, they just leave for higher-paying project$.
Go to Top of Page

Anastasiosyal
Starting Member

Greece
8 Posts

Posted - 08/01/2001 :  15:31:03  Show Profile  Visit Anastasiosyal's Homepage  Reply with Quote
quote:

At a previous company, we worked around this 255 limit by having a guy write a VB app that handled the message generation outside of SQL Server. We would write our email messages to a normal SQL table to be processed by this app. The message body was stored in a varchar(8000) field and we had a related table of recipients. The VB app would query the message table every 5 minutes for SentFlag = 0 and then generate the email message through Exchange. It was NOT a pretty sight, but it seemed to do the trick. The app was also written to handle bounce-back messages, etc.

I'm looking forward to Merkin's solution, which I'm sure will be more elegant.

------------------------------------------------------------------
Contractor$ never die, they just leave for higher-paying project$.



Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000