I was wondering if it would be possible for me to include a custom job in sql server. The code below is a standard Active X script that the job runs approx every 10 minutes. It sends email out based on records in a table.
CODE
===========================================
Const adCmdStoredProc = &H0004
'--------------------
'System config
Const ConnString = "Provider=SQLOLEDB; Data Source=###; Initial Catalog=###; User ID=###; Password=####"
Const MailServer = "A Mail server"
Call Main
Sub Main()
dim reply, message, email
dim objCmd, objRs
'ADO Command Object
Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = ConnString
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "spSendReplyEmail" 'Our Stored procedure
Set objRS = objCmd.execute 'Gets an ADO recordset of all the emails
do until objRs.EOF 'Loop through the emails
message = objRs("message")
reply = objRs("reply")
email = objRs("email")
'Call our mail subroutine
Call SendMail(reply, message, email)
objRS.movenext
loop
'Clean up
objRS.close
Set objRS = nothing
Set objCmd = nothing
End Sub
Sub SendMail(reply, message, email)
dim objMail, errCode
'Create the mail object
Set objMail = CreateObject("SMTPsvg.Mailer")
'Set all the properties for this email
objMail.RemoteHost = MailServer
objMail.FromName = "PreProfessional Notification Service"
objMail.FromAddress = "[email protected]"
objMail.AddRecipient "PreProfessional User", email
objMail.Subject = "Information about your email"
objMail.BodyText = "Recently, you sent asked a question on the PreProfessional Site. Your reply is below:"
objMail.BodyText = "REPLY:"
objMail.BodyText = "----------------------------------------------------------"
objMail.BodyText = "" & reply & ""
'Send it
errCode = objMail.SendMail
'Clean up
Set objMail = nothing
End Sub
===========================================
If you do, will i have to make a special requests, or can i do this myself?
Thanks.
Richard M.
CODE
===========================================
Const adCmdStoredProc = &H0004
'--------------------
'System config
Const ConnString = "Provider=SQLOLEDB; Data Source=###; Initial Catalog=###; User ID=###; Password=####"
Const MailServer = "A Mail server"
Call Main
Sub Main()
dim reply, message, email
dim objCmd, objRs
'ADO Command Object
Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = ConnString
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "spSendReplyEmail" 'Our Stored procedure
Set objRS = objCmd.execute 'Gets an ADO recordset of all the emails
do until objRs.EOF 'Loop through the emails
message = objRs("message")
reply = objRs("reply")
email = objRs("email")
'Call our mail subroutine
Call SendMail(reply, message, email)
objRS.movenext
loop
'Clean up
objRS.close
Set objRS = nothing
Set objCmd = nothing
End Sub
Sub SendMail(reply, message, email)
dim objMail, errCode
'Create the mail object
Set objMail = CreateObject("SMTPsvg.Mailer")
'Set all the properties for this email
objMail.RemoteHost = MailServer
objMail.FromName = "PreProfessional Notification Service"
objMail.FromAddress = "[email protected]"
objMail.AddRecipient "PreProfessional User", email
objMail.Subject = "Information about your email"
objMail.BodyText = "Recently, you sent asked a question on the PreProfessional Site. Your reply is below:"
objMail.BodyText = "REPLY:"
objMail.BodyText = "----------------------------------------------------------"
objMail.BodyText = "" & reply & ""
'Send it
errCode = objMail.SendMail
'Clean up
Set objMail = nothing
End Sub
===========================================
If you do, will i have to make a special requests, or can i do this myself?
Thanks.
Richard M.
