Article: Building a Mail Queue System

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?

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

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

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',

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

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

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

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

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

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

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.

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.

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!

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!

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

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

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

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

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

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources