Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Building a Mail Queue System

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-09-24 : 00:17:19

This article demonstrates an email queue system using SQL Server and VB Script. This overcomes the limitations of the Sending SMTP Mail using a Stored Procedure method.

Article Link.

parkstech
Starting Member

5 Posts

Posted - 2002-06-10 : 13:49:23
Curiosity is killing me. I get errors trying to run both scripts, Merkin's and blake's. I am using SQL Server 7, what are you guys using?

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-06-10 : 19:15:36
Hi

I run SQL 2000. What errors are you getting ?

Damian
Go to Top of Page

rocklinzc
Starting Member

7 Posts

Posted - 2002-07-30 : 16:54:42
Hello, I am pretty new to stored procedures and I am having a problem creating the "SendMail" Procedure.
I am trying to create the procedure that uses the SELECT statement to insert records. But i get an error that says "Incorrect syntax near the keyword 'Select'.. Here is my code can anyone tell me what I am doing wrong.. oh and I am using SQL Server 7 in case it matters.

thanks - Zach
------------------------------------------
Create Procedure SendMail
@FromName VarChar(200),
@FromAddress VarChar(200),
@Subject VarChar(200),
@Body VarChar(200)

AS

Select @FromName = 'Me',
Select @FromAddress = 'me@mydomain.com'
Select @Subject = 'Hi there'
Select @Body = 'The body of the message'

INSERT Into MailQueue (FromName, FromAddress, ToName, ToAddress, Subject, Body)
Select
@FromName, @FromAddress, UserFirstName + ' ' + UserSurName,
UserEmail, @Subject, @Body
FROM
Users

Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-31 : 06:34:47
i think the problem is the comma at the end of the following code....

Select @FromName = 'Me',

Go to Top of Page

rocklinzc
Starting Member

7 Posts

Posted - 2002-08-09 : 11:32:35
thanks that worked, i knew it would be something stupid like that.. :)

Zach


Go to Top of Page

Callaway
Starting Member

16 Posts

Posted - 2002-08-14 : 00:47:58
Wow, that article was great, but it has taken me forever to get the darn VB code to run. It still doesn't work for me though. I am trying to use Persit’s ASP Email component. When I run the DTS package that executes the VB script to send mail, it says "Cannot create object 'Persits MailSender.'

Does this mean that the object isn't registered? It works fine through my ASP applications. On their site it gives instruction for using it with VB, but these relate directly to working in a VB project, which I don't do. Just a web guy.

Oh here is the mail part of the script:

quote:
dim objMail

Set objMail = CreateObject("Persits.MailSender")
objMail.Host = "mail.host.com"
objMail.From = strFromEmail
objMail.FromName = strFromEmail
objMail.AddAddress strToEmail
objMail.Subject = strSubject
objMail.Body = strText

objMail.Send

Set objMail = nothing


Any help would be greatly appreciated.

Callaway




Edited by - Callaway on 08/14/2002 00:51:14

Edited by - Callaway on 08/14/2002 00:51:47
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-08-14 : 02:15:07
Hi

OK, here is my guess.
You have the component installed on the server, but you are hitting execute on this script on your development workstation, which doesn't have it installed.

Am I close ?

When you manually run a DTS package, it runs from your machine. Go jump on the server and try it. Or, create a job to run the package, and rather than setting a schedule, call the msdb..sp_start_job procedure to fire it off.

Hope that helps

Damian
Go to Top of Page

Callaway
Starting Member

16 Posts

Posted - 2002-08-14 : 11:24:11
Huh...I was thinking the same thing, but I never quite thought DTS executed on the local machine, it's worth a try, will update with my results in bit.

Thanks Merkin,
Callaway

Go to Top of Page

Callaway
Starting Member

16 Posts

Posted - 2002-08-14 : 11:32:35
Merkin,
It works. DTS does in fact execute on the local machine.

Two more small problems to perfection:

1) After running the DTS Package (on the server!) It says:
‘Invalid Task Result Value’

2) Every mailing sends double, Example: if there are 2 subscribers it sends 4 emails, 2 to each.

Any ideas?

Callaway



Edited by - callaway on 08/14/2002 11:33:28
Go to Top of Page

Callaway
Starting Member

16 Posts

Posted - 2002-08-15 : 00:26:17
Everything is working! Wow SQL is so kick A$$. I can't believe it acts as such a good mailing agent. Now I don't have to pay thousands for newsletter software. Personnally I think my App is better anyway!

Thanks to everyone who has helped,

Callaway

Go to Top of Page

aspnetman
Starting Member

2 Posts

Posted - 2003-04-15 : 14:53:33
in order to make the package execute properly change the Sub Main to Function Main and the coresponding End Sub to End Function.

Go to Top of Page

aspnetman
Starting Member

2 Posts

Posted - 2003-04-15 : 14:54:17
in order to make the package execute properly change the Sub Main to Function Main and the coresponding End Sub to End Function.
Go to Top of Page

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2003-04-22 : 12:03:56

Wow, recently (Nov '02) I wrote a system a bit like this, so now any SQL enabled programming langauge can make a request to our mailing system through the database, then a VB service picks up the e-mails and sends them on.

Glad to know someone else went that route too :)

One of the main reasons I wrote the system was so e-mail could be sent from any of our systems, which couldn't normally send e-mails. Plus we can run the VB service which sends the e-mails on any server we like, which means we can have a dedicated e-mailing system, without effecting performance on any other systems.

Although I'd suggest using TEXT as the field type for the body, instead of VARCHAR(8000). As I originally had it setup like that, but intermittely had problems when large e-mails where sent out.
So of course it was the 8000 char limit being reached, updating the system to use TEXT solved this problem. Be warned!


Go to Top of Page

InViz77
Starting Member

1 Post

Posted - 2003-06-27 : 11:22:00
I'm having the exact same problem as Callaway on 8/14/02. Does anyone have any idea what may be problems shown below?

1) After running the DTS Package (on the server) It says:
‘IInvalid Task Result Value?

2) Every mailing sends double, Example: if there are 2 subscribers it sends 4 emails, 2 to each.


Thanks!



Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-05 : 08:52:36
I'd like to see this article revised to use CDOSYS and support UNICODE text in the email.

Sam
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-08-05 : 09:45:06
Then go nuts and start writing

This was written more as a starting point than a feature complete application. If I claimed it was a bulletproof solution I would get even more emails asking for support than I already do.

Damian
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-05 : 10:10:07
Do you ever sleep Damian?

I need a jump start to get the idea of CDOSYS. Microsoft has such poor documentation, few real examples. It's like programming by trial and error to get the details worked out in cdosys.

Sam
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-05 : 22:01:52
Not sure I'd characterize this as "poor documentation":

http://search.microsoft.com/search/results.aspx?na=84&st=a&View=msdn&qu=cdosys&qp=&qa=&qn=&c=4&s=1
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-06 : 11:21:15
Thanks Rob,

I don't want to build a reputation as a MS basher. But I have found most MS documentation light on examples, particularly in the .NET arena.

In the case of CDOSYS, I haven't found any pointers to sending UNICODE email text. In an effort to get the job done sooner, I'm on my way to evaluating email packages as an alternative. It may be a better solution for my needs anyway.

Sam
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-27 : 12:33:25
Here's a CDO based email function that will send UNICODE plaintext.

Any comments or feedback appreciated.

Sam


Function SendEmail (strFrom, strTo, strCc, strBcc, strSubject, strBody)
Const cdoSendUsingPort = 2
Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cdoSMTPServerPort = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"

Dim objMail
on error resume next
strErrorMsg = ""

' Create the CDO Configuration Object and initialize
Set objConfiguration = CreateObject("CDO.Configuration")
Set objFields = objConfiguration.Fields
With objFields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPServer) = "localhost"
.Update
End With

' Create the CDO Message
Set objMessage = CreateObject("CDO.Message")
With objMessage
Set .Configuration = objConfiguration ' Configure the message communications
.MimeFormatted = TRUE
.From = strFrom
.To = strTo
.Cc = strCc
.Bcc = strBcc
.Subject = strSubject
With .Bodypart ' Initialize the bodypart to support UTF-8
.ContentMediaType = "text/plain"
.ContentTransferEncoding = "7bit"
.Charset = "utf-8"
SET Stm = .GetDecodedContentStream
Stm.WriteText strBody
Stm.Flush
End With
.Send
End With
SET objMessage = Nothing
SET objFields = Nothing
SET objConfiguration = Nothing

SendEmail = err.description ' Return last error if any
End Function
Go to Top of Page
    Next Page

- Advertisement -