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

Juls
Yak Posting Veteran

USA
51 Posts

Posted - 03/18/2002 :  14:39:35  Show Profile  Reply with Quote
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

USA
15732 Posts

Posted - 03/18/2002 :  14:47:25  Show Profile  Visit robvolk's Homepage  Reply with Quote
"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
Go to Top of Page

Juls
Yak Posting Veteran

USA
51 Posts

Posted - 03/18/2002 :  15:12:30  Show Profile  Reply with Quote
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

Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 03/18/2002 :  15:23:58  Show Profile  Visit robvolk's Homepage  Reply with Quote
I think you need to change this:

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

To this:

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

Go to Top of Page

Juls
Yak Posting Veteran

USA
51 Posts

Posted - 03/18/2002 :  15:41:00  Show Profile  Reply with Quote
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"

Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 03/18/2002 :  16:01:09  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

Juls
Yak Posting Veteran

USA
51 Posts

Posted - 03/18/2002 :  16:39:59  Show Profile  Reply with Quote
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) ?

Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 03/18/2002 :  17:04:18  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

Juls
Yak Posting Veteran

USA
51 Posts

Posted - 03/19/2002 :  09:20:43  Show Profile  Reply with Quote
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?

Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 03/19/2002 :  10:06:06  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Go to Top of Page

Juls
Yak Posting Veteran

USA
51 Posts

Posted - 03/20/2002 :  16:11:40  Show Profile  Reply with Quote
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
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 03/20/2002 :  18:32:39  Show Profile  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next 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.04 seconds. Powered By: Snitz Forums 2000