Building a Mail Queue System - SQL Server Forums

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

Building a Mail Queue System

Author

Topic

Juls
Yak Posting Veteran

51 Posts

Posted - 2002-03-18 : 14:39:35

Hi,

I did everything according to Merkins directions in his Article and set up a job with the following VBScript syntax. However, I am running into a syntax problem when i run this job.

I should mention that this same VBScript code runs fine in the browser.

The error is:
Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Object required: 'Server'

Here it is, and I would really appreciate any help

'--------------------'
'ADO Constants'
Const adCmdStoredProc = &H0004
'--------------------'

'create database object and open a connection'
dim ConnectionString
ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI; Persist Security Info=False;Initial Catalog=DB_Name; Data Source = ServerName"
set DB = Server.CreateObject("ADODB.Connection")
DB.Open ConnectionString

Sub Main()

dim sTo, sSubject, sBody
dim objRs

Set objCmd = Server.CreateObject("ADODB.Command")

objCmd.ActiveConnection = DB
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "procEmailReminder" 'Our Stored procedure'
objCmd.Parameters("@FundID") = cint(18)
Set objRs = objCmd.execute 'Gets an ADO recordset of all the emails'

do until objRs.EOF 'Loop through the emails'

sTo = objRs("fld_EMail")
sSubject = "Program# " & objRs("fld_Program_ID") & " finished yesterday. TEST"
sBody = "Dear " & objRs("fld_Person_First_Last_Name")

'Call our mail subroutine'
Call SendMail(sTo, sSubject, sBody)
objRs.movenext
loop

'Clean up'
objRs.close
Set objRs = nothing
Set objCmd = nothing

set sTo = nothing
set sSubject = nothing
set sBody = nothing
End Sub

Sub SendMail(sTo, sSubject, sBody)
dim objMail

'Create the mail object'
set objMail = CreateObject("CDONTS.Newmail")

'Set all the properties for this email'
objMail.from = "them@there.com"
objMail.to = "me@here.com"
objMail.Subject = sSubject
objMail.BodyFormat = 0
objMail.MailFormat = 0
objMail.Importance = 2
objMail.Body = sBody

'Send it'
objMail.Send

'Clean up'
Set objMail = nothing
End Sub

Call Main 'execute this job'

DB.Close
set DB=nothing
set ConnectionString = nothing

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-18 : 14:47:25

"Server" is a reference to the ASP Server object, which does not exist in regular VBScript. Simply remove every instance of "SERVER." and leave the rest of the code intact.

Edited by - robvolk on 03/18/2002 14:47:57

Juls
Yak Posting Veteran

51 Posts

Posted - 2002-03-18 : 15:12:30

I got rid of 'Server', but now i am getting this message:

Error Code: 0 Error Source= ADODB.Command Error Description: Item cannot be found in the collection corresponding to the requested name or ordinal.

Any ideas?

Thanks so much,
Juls

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-18 : 15:23:58

I think you need to change this:

objCmd.Parameters("@FundID") = cint(18)

To this:

objCmd.Parameters.Add "@FundID", adInteger, , , 18

Juls
Yak Posting Veteran

51 Posts

Posted - 2002-03-18 : 15:41:00

I tried that but i get an error:

Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Object doesn't support this property or method: 'objCmd.Parameters.Add'

It is also referencing this line:

objCmd.CommandText = "procEmailReminder"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-18 : 16:01:09

The only thing I can guess at this point is that ADO is not properly installed on your SQL Server, or needs to be reinstalled. Try download the MDAC components here:

http://www.microsoft.com/data/download.htm

Look for the MDAC 2.6 SP1 package, download it and install it on the SQL Server where you are creating this job. You should also reboot the machine, even if it does not require you to do so.

I don't think this is the issue either, but you don't need to declare Sub Main() in a SQL VBScript job. Try removing the Sub Main(), End Sub, and Call Main lines from the script.

Juls
Yak Posting Veteran

51 Posts

Posted - 2002-03-18 : 16:39:59

Thanks for the info.

After looking at the MDAC 2.6 SP1 package I noticed that it says thjat it should not be used for SQL Server 7 (which is what I have).

Which should I be installing? MDAC 2.10.3513.2 (SQL7) ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-18 : 17:04:18

That only applies to clustered SQL 7.0 servers, if you are not using clusters then it is perfectly OK to use MDAC 2.6 SP1. If you don't want to take a chance, use MDAC 2.5 SP2.

DO NOT USE ANY VERSION of MDAC earlier than 2.5, if you do you'll most likely get even more problems than you have now. Numerous bugs were fixed and many features enhanced with version 2.5.

Edited by - robvolk on 03/18/2002 17:05:06

Juls
Yak Posting Veteran

51 Posts

Posted - 2002-03-19 : 09:20:43

Thanks for the info... one more question: I have IIS installed on this same server as my SQL Server, does that make any difference or contribute to my problem?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-19 : 10:06:06

Probably not. I've never used CDONTS mail objects though. The other thing you should do is apply the latest service pack for the version of SQL Server you're running (SP3 for 7.0, SP2 for 2000) You can find them at http://www.microsoft.com/sql

Juls
Yak Posting Veteran

51 Posts

Posted - 2002-03-20 : 16:11:40

Hi, thanks for the help, I seems to got it working now.

Now I am trying to attach a file to the email which is located on one of my OTHER servers. I can't get the syntax right, please help..

Here is what I have:
objMail.AttachFile "\\servername\folder\file.doc"

I can only attach the file if it's located on the same server as my SQL server by using the path "c:/folder/file.doc"

Thanks,
Juls

Edited by - Juls on 03/20/2002 17:07:26

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-20 : 18:32:39

quote:

I can only attach the file if it's located on the same server as my SQL server by using the path "c:/folder/file.doc"

I think that is the only way you could do it - but I've been wrong a million times before (at least)....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources