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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Building a Mail Queue System
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

InViz77
Starting Member

1 Posts

Posted - 06/27/2003 :  11:22:00  Show Profile  Reply with Quote
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

USA
3467 Posts

Posted - 08/05/2003 :  08:52:36  Show Profile  Reply with Quote
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!

Australia
4970 Posts

Posted - 08/05/2003 :  09:45:06  Show Profile  Visit Merkin's Homepage  Reply with Quote
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

USA
3467 Posts

Posted - 08/05/2003 :  10:10:07  Show Profile  Reply with Quote
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

USA
15732 Posts

Posted - 08/05/2003 :  22:01:52  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Edited by - robvolk on 08/05/2003 22:04:56
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 08/06/2003 :  11:21:15  Show Profile  Reply with Quote
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

USA
3467 Posts

Posted - 08/27/2003 :  12:33:25  Show Profile  Reply with Quote
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

Edited by - SamC on 08/27/2003 12:34:11
Go to Top of Page

Callaway
Starting Member

16 Posts

Posted - 10/07/2003 :  00:55:42  Show Profile  Reply with Quote
Every mailing sends double, Example: if there are 2 subscribers it sends 4 emails, 2 to each.

Has anyone figured this out yet?
Go to Top of Page

Callaway
Starting Member

16 Posts

Posted - 10/07/2003 :  01:02:43  Show Profile  Reply with Quote
quote:
Every mailing sends double, Example: if there are 2 subscribers it sends 4 emails, 2 to each.


Finally figured it out. Jeez it's only been 2 years since I last looked at this code!

When you add the active task, SQL automatically runs "main", so you need to delete "call main" from your script. "Function main" was getting called twice, running the whole thing 2 times, thus duplicate mail!
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 10/07/2003 :  15:11:56  Show Profile  Reply with Quote
BURNING QUESTION

I get erratic CRLF results reading plain text email in Outlook sent using VBScript.

Seems CRLF CHAR(10) + CHAR(13) will generate two line feeds. Either Char(10) or Char(13) alone will generate a single line feed nicely.

Only sometimes, using char(10) or char(13) alone will NOT generate a line feed.

This is inconsistent.

Has anyone else seen this problem.

Whaddyado?

Sam

Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 10/07/2003 :  21:25:31  Show Profile  Visit robvolk's Homepage  Reply with Quote
The correct sequence is CHAR(13)+CHAR(10). Either one by themselves will be unpredictable, and no combination will be consistent if the mail is in HTML format (use a <br> tag for HTML mail)
Go to Top of Page

rageeb
Starting Member

1 Posts

Posted - 01/17/2004 :  06:13:06  Show Profile  Reply with Quote
quote:
Originally posted by Callaway

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

Has anyone figured this out yet?



How to integrate the code u have given cdo unicode function in ASP pages so that i want to send japanese text this is the code ...iam new to scripting

======= code begins======================

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "raj@v.com"

objEmail.To = "raj2@sn.com"

objEmail.Subject = "from us - the test of ASP"

objEmail.Textbody = "Testing MY ASP script!!!"

objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.v.com"

objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objEmail.Configuration.Fields.Update

objEmail.Send set objmail=nothing

'=============code ends here ====================





Go to Top of Page

golsen
Starting Member

1 Posts

Posted - 10/22/2004 :  10:46:29  Show Profile  Reply with Quote
Hi everyone,

I found a fix for the "Invalid Task Result Value' problem that callaway posted earlier.

In the Function Main, just add this in the end of the function:
Main = DTSTaskExecResult_Success

That should do it.

Here is my code that is working perfect:
*******************************************************************
Const adCmdStoredProc = &H0004

Const ConnString = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=test; User ID=test; Password=test"
Const MailServer = "mail.test.net"

Call Main

Function Main()

dim sFromName, sFromAddress, sToName, sToAddress, sSubject, sBody
dim objCmd, objRs

'ADO Command Object
Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = ConnString
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "GetMailQueue" 'Our Stored procedure
Set objRS = objCmd.execute 'Gets an ADO recordset of all the emails

do until objRs.EOF 'Loop through the emails

sFromName = objRs("FromName")
sFromAddress = objRs("FromAddress")
sToName = objRs("ToName")
sToAddress = objRs("ToAddress")
sSubject = objRs("Subject")
sBody = objRs("Body")

'Call our mail subroutine
Call SendMail(sFromName, sFromAddress, sToName, sToAddress, sSubject, sBody)
objRS.movenext
loop

'Clean up
objRS.close
Set objRS = nothing
Set objCmd = nothing
Main = DTSTaskExecResult_Success

End Function

Function SendMail(sFromName, sFromAddress, sToName, sToAddress, sSubject, sBody)
dim objMail, errCode

'Create the mail object
Set objMail = CreateObject("Persits.MailSender")

'Set all the properties for this email
objMail.Host = MailServer
objMail.From = sFromAddress
objMail.FromName = sFromName
objMail.AddAddress sToAddress
objMail.Subject = sSubject
objMail.Body = sBody

'Send it
objMail.Send

'Clean up
Set objMail = nothing
End Function

********************************************************************

By the way, thanks for posting the article in the first place. It's a good way to send email from the sql server.

Geir
Database Administrator
Go to Top of Page

svgharmode
Starting Member

India
1 Posts

Posted - 05/11/2005 :  09:11:45  Show Profile  Reply with Quote
Dear Friends,

Please help me to sort out the problem. I am using SQL Server 2000 with vbscript / CDONTS for sending the mail. I used the code given on the page. This code is giving me successful message of execution step, nut not sending the mail. Locally it is saving the mail file in to mailroot queue folder and webserver it is not sending mail as well as not save the file in the queue folder.... will u please help me to sort out the problem.

Thanks in advance
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 05/11/2005 :  11:08:19  Show Profile  Visit Merkin's Homepage  Reply with Quote
Sounds like your SMTP service isn't configured properly. Get your network admin to have a look.

Damian
Ita erat quando hic adveni.
Go to Top of Page

RMSProgrammer
Starting Member

1 Posts

Posted - 05/30/2007 :  17:02:56  Show Profile  Reply with Quote
quote:
Originally posted by blake
When trying to parse the code, it doesn't recognize the single-quote character (') as a comment tag, but rather as a string.


That is pretty easy, remember your code is being put into the msdb database, and when it creates a stored procedure, it uses the create job step function. The parameter accepts nvarchar(3200) parameter requiring something like this:
sp_add_jobstep @command = N' your vbscript'
Anyway, if you use the ' for a comment, it will reject it, because it conflicts with it trying to submit to the database. I normally just use rem (for remark). To get a feel, script out your job by right-clicking it and say script to a file (query analizer) that way you can see how the analizer sees the vb script, and my comment about N' will make more sense.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 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.06 seconds. Powered By: Snitz Forums 2000