krbnldy
asked on
ActiveX script in DTS package
I am using activeX script in a DTS package to send mail to serveral users if thier ID is found in a particular MSSQL view. The code is added below. it is only sending one email for the first employee it finds in the view. Please help me modify the code so that it could send an email for each employee it finds and add the id of each COntent_ID it finds for each employee.
For testing I am only including my email address and not yet getting the email address from the view. I will modify that latr
Sub Send_Mail(strTo, strFrom, strSubject, strMessage )
Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2
set iMsg = CreateObject("CDO.Message" )
set iConf = CreateObject("CDO.Configur ation")
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =2 'cdoSendUsingPort
'this parameter below is the valid SMPT Server Name
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="******.***.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
.Update
End With
With iMsg
Set .Configuration = iConf
.To = strTo ' comma separated list of recipients.
.From = strFrom
.Subject = strSubject
.HTMLBody = strMessage
.Send
End With
End Sub
Function Main()
dim myConn
dim myRecordset
dim iRowCount
dim rsUnread
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connec tion")
set myRecordset = CreateObject("ADODB.Record set")
set rsUnread = CreateObject("ADODB.Record set")
' set the connection properties to point to the CMS database using the ProcessDisplay view
myConn.Open = "Provider=SQLOLEDB.1;Data Source=******; Initial Catalog=*******;user id = '********';password='***** '"
' check for unread documents for each emloyee
mySQLUnread = "Select Emp_ID from vProcessDisplay"
rsUnread.Open mySQLUnread, myConn
For xcnt = 0 to rsUnread.fields.count -1
mySQLCmdText = "Select 'rowcount' = Count(*), Contetn_ID, Content_title,Emp_Name, Reading_Group, email from vProcessDisplay where emp_ID = '" & rsUnread("Emp_ID") & "' group by Content_ID, COntent_title, emp_Name, Reading_Group, email"
myRecordset.Open mySQLCmdText, myConn
set Flds = myRecordset.Fields
set iRowCount = Flds("rowcount")
next
while not myRecordset.eof
If iRowCount.Value = 0 then
Main = DTSTaskExecResult_Failure
else
dim unreaddoc
unreaddoc = iRowCount.Value
strTo="myname<myemailaddrs s@*******. com>"
strFrom="Document Central<process@****.***** *.com>"
strSubject="Unread document for -" & Date() & myRecordset("emp_Name")
' I would like to have a loop here that adds each content_ID to the message as there may be more than one document for each employee
strMessage="There are " & unreaddoc & " documents to be read "
Send_Mail strTo, strFrom, strSubject, strMessage
Main = DTSTaskExecResult_Success
End If
myRecordset.movenext
wend
Main = DTSTaskExecResult_Success
End Function
For testing I am only including my email address and not yet getting the email address from the view. I will modify that latr
Sub Send_Mail(strTo, strFrom, strSubject, strMessage )
Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2
set iMsg = CreateObject("CDO.Message"
set iConf = CreateObject("CDO.Configur
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =2 'cdoSendUsingPort
'this parameter below is the valid SMPT Server Name
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="******.***.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
.Update
End With
With iMsg
Set .Configuration = iConf
.To = strTo ' comma separated list of recipients.
.From = strFrom
.Subject = strSubject
.HTMLBody = strMessage
.Send
End With
End Sub
Function Main()
dim myConn
dim myRecordset
dim iRowCount
dim rsUnread
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connec
set myRecordset = CreateObject("ADODB.Record
set rsUnread = CreateObject("ADODB.Record
' set the connection properties to point to the CMS database using the ProcessDisplay view
myConn.Open = "Provider=SQLOLEDB.1;Data Source=******; Initial Catalog=*******;user id = '********';password='*****
' check for unread documents for each emloyee
mySQLUnread = "Select Emp_ID from vProcessDisplay"
rsUnread.Open mySQLUnread, myConn
For xcnt = 0 to rsUnread.fields.count -1
mySQLCmdText = "Select 'rowcount' = Count(*), Contetn_ID, Content_title,Emp_Name, Reading_Group, email from vProcessDisplay where emp_ID = '" & rsUnread("Emp_ID") & "' group by Content_ID, COntent_title, emp_Name, Reading_Group, email"
myRecordset.Open mySQLCmdText, myConn
set Flds = myRecordset.Fields
set iRowCount = Flds("rowcount")
next
while not myRecordset.eof
If iRowCount.Value = 0 then
Main = DTSTaskExecResult_Failure
else
dim unreaddoc
unreaddoc = iRowCount.Value
strTo="myname<myemailaddrs
strFrom="Document Central<process@****.*****
strSubject="Unread document for -" & Date() & myRecordset("emp_Name")
' I would like to have a loop here that adds each content_ID to the message as there may be more than one document for each employee
strMessage="There are " & unreaddoc & " documents to be read "
Send_Mail strTo, strFrom, strSubject, strMessage
Main = DTSTaskExecResult_Success
End If
myRecordset.movenext
wend
Main = DTSTaskExecResult_Success
End Function
Incidentally iRowCount is only set once before the While ... Wend. Your formatted code shows what I mean:
Function Main()
dim myConn
dim myRecordset
dim iRowCount
dim rsUnread
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connec tion")
set myRecordset = CreateObject("ADODB.Record set")
set rsUnread = CreateObject("ADODB.Record set")
' set the connection properties to point to the CMS database using the ProcessDisplay view
myConn.Open = "Provider=SQLOLEDB.1;Data Source=******; Initial Catalog=*******;user id = '********';password='***** '"
' check for unread documents for each emloyee
mySQLUnread = "Select Emp_ID from vProcessDisplay"
rsUnread.Open mySQLUnread, myConn
For xcnt = 0 to rsUnread.fields.count -1
mySQLCmdText = "Select 'rowcount' = Count(*), Contetn_ID, Content_title,Emp_Name, Reading_Group, email from vProcessDisplay where emp_ID = '" & rsUnread("Emp_ID") & "' group by Content_ID, COntent_title, emp_Name, Reading_Group, email"
myRecordset.Open mySQLCmdText, myConn
set Flds = myRecordset.Fields
set iRowCount = Flds("rowcount")
next
while not myRecordset.eof
If iRowCount.Value = 0 then
Main = DTSTaskExecResult_Failure
else
dim unreaddoc
unreaddoc = iRowCount.Value
strTo="myname<myemailaddrs s@*******. com>"
strFrom="Document Central<process@****.***** *.com>"
strSubject="Unread document for -" & Date() & myRecordset("emp_Name")
' I would like to have a loop here that adds each content_ID to the message as there may be more than one document for each employee
strMessage="There are " & unreaddoc & " documents to be read "
Send_Mail strTo, strFrom, strSubject, strMessage
Main = DTSTaskExecResult_Success
End If
myRecordset.movenext
wend
Main = DTSTaskExecResult_Success
End Function
Function Main()
dim myConn
dim myRecordset
dim iRowCount
dim rsUnread
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connec
set myRecordset = CreateObject("ADODB.Record
set rsUnread = CreateObject("ADODB.Record
' set the connection properties to point to the CMS database using the ProcessDisplay view
myConn.Open = "Provider=SQLOLEDB.1;Data Source=******; Initial Catalog=*******;user id = '********';password='*****
' check for unread documents for each emloyee
mySQLUnread = "Select Emp_ID from vProcessDisplay"
rsUnread.Open mySQLUnread, myConn
For xcnt = 0 to rsUnread.fields.count -1
mySQLCmdText = "Select 'rowcount' = Count(*), Contetn_ID, Content_title,Emp_Name, Reading_Group, email from vProcessDisplay where emp_ID = '" & rsUnread("Emp_ID") & "' group by Content_ID, COntent_title, emp_Name, Reading_Group, email"
myRecordset.Open mySQLCmdText, myConn
set Flds = myRecordset.Fields
set iRowCount = Flds("rowcount")
next
while not myRecordset.eof
If iRowCount.Value = 0 then
Main = DTSTaskExecResult_Failure
else
dim unreaddoc
unreaddoc = iRowCount.Value
strTo="myname<myemailaddrs
strFrom="Document Central<process@****.*****
strSubject="Unread document for -" & Date() & myRecordset("emp_Name")
' I would like to have a loop here that adds each content_ID to the message as there may be more than one document for each employee
strMessage="There are " & unreaddoc & " documents to be read "
Send_Mail strTo, strFrom, strSubject, strMessage
Main = DTSTaskExecResult_Success
End If
myRecordset.movenext
wend
Main = DTSTaskExecResult_Success
End Function
ASKER
I am not too familiar with this but how do I modify the code to make it worse
ALso, what other approach do you recommend, can you give me examples. I am using a dts package as I want to schedule it to run every tuesday at a specific time.
All suggestions and help aprreciated
ALso, what other approach do you recommend, can you give me examples. I am using a dts package as I want to schedule it to run every tuesday at a specific time.
All suggestions and help aprreciated
ASKER
I meant make it work. :-)
>>I am not too familiar with this but how do I modify the code to make it worse<<
Without having access to your data, I have no idea what is going wrong.
>>ALso, what other approach do you recommend, can you give me examples.<<
If you are bound and determined to use DTS than I would:
1. Create a one query instead of two.
2. I would use the Execute SQL task to return the result in a Rowset. See here for an example:
How to loop through a global variable Rowset
http://www.sqldts.com/298.aspx
3. To actually send the email I would use something like:
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
Without having access to your data, I have no idea what is going wrong.
>>ALso, what other approach do you recommend, can you give me examples.<<
If you are bound and determined to use DTS than I would:
1. Create a one query instead of two.
2. I would use the Execute SQL task to return the result in a Rowset. See here for an example:
How to loop through a global variable Rowset
http://www.sqldts.com/298.aspx
3. To actually send the email I would use something like:
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
ASKER
the following is my solution and it works great. I did not go with using just one query as I had more than one view I needed to pull data from
dim myConn
dim myRecordset
dim iRowCount
dim rsUnread
dim btnID
btnID = "1"
dim ID
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connec tion")
set myRecordset = CreateObject("ADODB.Record set")
set rsUnread = CreateObject("ADODB.Record set")
' set the connection properties to point to the CMS database using the vProcessDisplay view
myConn.Open = "Provider=SQLOLEDB.1;Data Source=***; Initial Catalog=***_***;user id = ***;password='*****"
' check for unread documents for each emloyee
mySQLUnread = "Select distinct Emp_ID from vProcessDisplay"
rsUnread.Open mySQLUnread, myConn
while not rsUnread.eof
For xcnt = 0 to rsUnread.fields.count -1
mySQLCmdText = "Select Emp_Name, Reading_Group, email from vAdherenceProcessDisplay where emp_ID = '" & rsUnread("Emp_ID") & "'"
set myrecordset = myConn.Execute(mySQLCmdTex t)
set Flds = myRecordset.Fields
next
while not myRecordset.eof
dim ggroup
ggroup = myRecordset("reading_group ")
strTo="*****<******@***.** ****.com>"
strFrom="Process Central<*********@--****** .com>"
strSubject="Unread document for -" & Date() & myRecordset("emp_Name")
strMessage= myRecordset("Emp_Name") & "<br /><br />"
strMessage= strMessage & "You have {count} documents that you have not yet read .<br /><br />"
strMessage = strMessage & " Please click on the url below to access the following . Please read carefully and afterwards acknowledge that youve read this document.<br /><br />"
sqlSend = "Select * from vDocsnotRead where emp_id = '" & rsUnread("Emp_ID") & "'"
set rsSend = MyConn.Execute(sqlSend)
dim totalDocs
totalDocs = 0
while not rsSend.eof
ID = rsSend("Content_ID")
strMessage = strMessage & "<a href='http://**********.com/*****/****.aspx?ID=" & ID & "&btnID=" & btnID & "&gGroup=" & gGroup & " '>" & rsSend("Content_title") & "</a><br /><br />"
totalDocs = totalDocs + 1
rsSend.movenext
wend
strMessage = strMessage & "Thank You"
myRecordset.movenext
wend
Send_Mail strTo, strFrom, strSubject, replace(strMessage, "{count}", totalDocs)
rsUnread.movenext
wend
Main = DTSTaskExecResult_Success
End Function
dim myConn
dim myRecordset
dim iRowCount
dim rsUnread
dim btnID
btnID = "1"
dim ID
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connec
set myRecordset = CreateObject("ADODB.Record
set rsUnread = CreateObject("ADODB.Record
' set the connection properties to point to the CMS database using the vProcessDisplay view
myConn.Open = "Provider=SQLOLEDB.1;Data Source=***; Initial Catalog=***_***;user id = ***;password='*****"
' check for unread documents for each emloyee
mySQLUnread = "Select distinct Emp_ID from vProcessDisplay"
rsUnread.Open mySQLUnread, myConn
while not rsUnread.eof
For xcnt = 0 to rsUnread.fields.count -1
mySQLCmdText = "Select Emp_Name, Reading_Group, email from vAdherenceProcessDisplay where emp_ID = '" & rsUnread("Emp_ID") & "'"
set myrecordset = myConn.Execute(mySQLCmdTex
set Flds = myRecordset.Fields
next
while not myRecordset.eof
dim ggroup
ggroup = myRecordset("reading_group
strTo="*****<******@***.**
strFrom="Process Central<*********@--******
strSubject="Unread document for -" & Date() & myRecordset("emp_Name")
strMessage= myRecordset("Emp_Name") & "<br /><br />"
strMessage= strMessage & "You have {count} documents that you have not yet read .<br /><br />"
strMessage = strMessage & " Please click on the url below to access the following . Please read carefully and afterwards acknowledge that youve read this document.<br /><br />"
sqlSend = "Select * from vDocsnotRead where emp_id = '" & rsUnread("Emp_ID") & "'"
set rsSend = MyConn.Execute(sqlSend)
dim totalDocs
totalDocs = 0
while not rsSend.eof
ID = rsSend("Content_ID")
strMessage = strMessage & "<a href='http://**********.com/*****/****.aspx?ID=" & ID & "&btnID=" & btnID & "&gGroup=" & gGroup & " '>" & rsSend("Content_title") & "</a><br /><br />"
totalDocs = totalDocs + 1
rsSend.movenext
wend
strMessage = strMessage & "Thank You"
myRecordset.movenext
wend
Send_Mail strTo, strFrom, strSubject, replace(strMessage, "{count}", totalDocs)
rsUnread.movenext
wend
Main = DTSTaskExecResult_Success
End Function
I still do not know why you are opening the recordset myrecordset so many times. But whatever works for you...
P.S. You should also get in the habit of closing the recordset and setting it to Nothing when you no longer need it,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Having said that let me list all the problems:
You are opening myRecordset rsUnread.fields.count times. Surely that is not your intent.
You are setting the Task to Failure in the loop, but not exiting.
Contetn_ID is probably mispelled.
If emp_ID is numeric there is no need for the single quotes.
The two queries would be best combined into one.
As to why it is only firing once, I would suspect that the iRowCount.Value = 0 for all rows after the first. But this can be best verified with debugging turned on.