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: Building a Mail Queue System
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/24/2001 :  00:17:19  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

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.

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 09/26/2001 :  20:21:02  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Great article Damian! And for anyone who knows VBScript, it's easily adaptable to whatever COM object one has for sending mail.

<thinking out loud...>I know this drifts out of being purely in SQL Server, but I wonder what, if any, performance difference there would be to put this code in a VB .exe, with a timer control to repeatedly run it. Or, another approach might be to write an .exe that just pulls one record from the queue at a time to process, and have this .exe running in the background of several computers to spread the load if there were a large number of mails to process.</thinking out loud>

This could be a great jumping-off point for even fancier mail systems. Great job!

-------------------------------------------------------------------
Just because you CAN do something does not mean that you SHOULD!
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 09/26/2001 :  20:28:32  Show Profile  Visit Merkin's Homepage  Reply with Quote
Thanks Mark

Like I said at the end of it, you could develop it in a number of ways. I would think running it as an NT service would be great. There are lots of ways you could take it, But I wanted to keep it simple so that the basic principle was clear.



Damian
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 09/26/2001 :  20:42:24  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
I liked your idea of running it as an NT Service, but had now idea how something became a service. Now I just had a quick talk with one of our NT/2K guru's and he explained to me that you can fairly easily turn an exe into a service, so now I'm off and running with new ideas. Thanks again!

-------------------------------------------------------------------
Just because you CAN do something does not mean that you SHOULD!
Go to Top of Page

blake
Starting Member

Canada
29 Posts

Posted - 11/01/2001 :  23:33:29  Show Profile  Visit blake's Homepage  Reply with Quote
There seems to be a strange thing happening when defining the new job in Enterprise Manager. First, the parsing of the vbs file is very strange. I have defined a new job step as an "Active Script" type with the language set to "Visual Basic Script". When trying to parse the code, it doesn't recognize the single-quote character (') as a comment tag, but rather as a string.

When I remove all comments, it accepts the script. But then when I go to save the job, it gives error number 14277: "The command script does not destroy all the objects that it creates. Revise the command script."

Modifying the SendMail sub by adding the lines:

Set sFromName = Nothing
Set sFromAddress = Nothing
[etc...]
Set sBody = Nothing

makes it accept the new job. However, it never succeeds. Removing the line in the Main sub "Call SendMail(...)" makes the job succeed.

Any idea why this is occurring? It's almost as if it's expecting a different kind of language. Am I defining the job correctly using Enterprise Manager, or is something very bizarre going on?

Go to Top of Page

Sleepy
Starting Member

1 Posts

Posted - 11/02/2001 :  02:10:12  Show Profile  Reply with Quote
hey blake, i have the exact same problem, i have changed the sendmail sub to use CDONTS instead, and have to use a comment like "'Set adf asdlkf" right after i create that object to get it to work, and if i change Set to somehting else in that comment I get the same error again...
So I think there must be some a bug in the system here, or it's really picky with the syntax...ARG!

forgot to say that, it sends two mails right now useing this code:
Call Main

Sub Main()
dim sFromName, sFromAddress, sToName, sToAddress, sSubject, sBody
dim objCmd, objRs

Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = "DSN=test;UID=geno;PWD=test"
objCmd.CommandText = "SELECT * FROM test"
Set objRS = objCmd.execute

do until objRS.eof
sBody = sBody & objRS("title")
objRS.movenext
loop

Call SendMail("test", "sleepy@linc-up.com", "Test", sBody)

objRS.close
Set objRS = nothing
Set objCmd = nothing
End Sub

Sub SendMail(sFromAddress, sToAddress, sSubject, sBody)
dim objMail
Set objMail = CreateObject("CDONTS.NewMail")
'Set all
objMail.From = sFromAddress
objMail.To = sToAddress
objMail.Subject = sSubject
objMail.Body = sBody
objMail.Send

Set objMail = nothing
End Sub

Edited by - Sleepy on 11/02/2001 02:13:12
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/02/2001 :  21:14:39  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hey Guys

I had the same problem with another similar script I did.
I just figured the parser was a little screwy for complex (ish ) scripts.

The way I got around it, was put the script into a DTS package, then schedule that instead. It required a bit of modification to the script, but didn't have any of the other problems.

If the commented Set line doesn't work, give DTS a go..



Damian
Go to Top of Page

blake
Starting Member

Canada
29 Posts

Posted - 11/05/2001 :  19:01:52  Show Profile  Visit blake's Homepage  Reply with Quote
quote:

The way I got around it, was put the script into a DTS package, then schedule that instead. It required a bit of modification to the script, but didn't have any of the other problems.



Thanks, Damian. That definitely did the trick! Ah, the joys of SQL Server... :)

Blake
Go to Top of Page

parkstech
Starting Member

5 Posts

Posted - 06/10/2002 :  13:49:23  Show Profile  Reply with Quote
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!

Australia
4970 Posts

Posted - 06/10/2002 :  19:15:36  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi

I run SQL 2000. What errors are you getting ?

Damian
Go to Top of Page

rocklinzc
Starting Member

7 Posts

Posted - 07/30/2002 :  16:54:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 07/31/2002 :  06:34:47  Show Profile  Reply with Quote
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 - 08/09/2002 :  11:32:35  Show Profile  Reply with Quote
thanks that worked, i knew it would be something stupid like that.. :)

Zach


Go to Top of Page

Callaway
Starting Member

16 Posts

Posted - 08/14/2002 :  00:47:58  Show Profile  Reply with Quote
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!

Australia
4970 Posts

Posted - 08/14/2002 :  02:15:07  Show Profile  Visit Merkin's Homepage  Reply with Quote
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 - 08/14/2002 :  11:24:11  Show Profile  Reply with Quote
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 - 08/14/2002 :  11:32:35  Show Profile  Reply with Quote
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 - 08/15/2002 :  00:26:17  Show Profile  Reply with Quote
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

USA
2 Posts

Posted - 04/15/2003 :  14:53:33  Show Profile  Send aspnetman an AOL message  Reply with Quote
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

USA
2 Posts

Posted - 04/15/2003 :  14:54:17  Show Profile  Send aspnetman an AOL message  Reply with Quote
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

United Kingdom
89 Posts

Posted - 04/22/2003 :  12:03:56  Show Profile  Visit MaverickUK's Homepage  Reply with Quote

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
Page: of 2 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.24 seconds. Powered By: Snitz Forums 2000