SQL Array to email group for ASPmail ??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Array to email group for ASPmail ???

Author

Topic

wbhall13
Starting Member

4 Posts

Posted - 2004-06-16 : 21:32:59

Help !!! I need to output my email addresses into a string output for group emailing. I am running the folowing SQL in SQL server to retive email addresses from a table:
declare @email varchar(100)
declare xemail cursor for
SELECT [Pager] FROM [CNOC].[dbo].[SLGContacts_Email] where [Service Group]like 'White Plains%' and [Work Center ID]='416wp500' and [Category]='ACT' and [Y1]='1'
open xemail
while(0=0) begin
fetch next
from xemail
into @email
if (@@Fetch_status <>0) Break
Print @email +';'
End
close xemail
deallocate xemail

The output looks like this:
gmancini@xtt.com;
hobrown@xtt.com;
nixonj@xtt.com;
pertelesi@xtt.com;
karlenesmith@xtt.com;
spanaro@xtt.com;

It needs to look like this:
gmancini@xtt.com;hobrown@xtt.com;nixonj@xtt.com;pertelesi@xtt.com;karlenesmith@xtt.com;spanaro@xtt.com

This output will allow me to mail to a group of users based on a database query.... I am not sure if the correct output function is the "Print @email +','" if I need to use the output in my ASPmail tool (Mailer.AddAddress = the above SQL).
Any suggestions??????
Thanks in advance for your help....
Bill

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-16 : 23:59:42

Print will add a C/R.

You're better off creating a new parameter to recieve the email string, so instead of:
Print @email + ';'
you would have
SET @emailList = @emailList + @email + ';'

Then at the end you can either call Print @emailList or you can return the string as an output parameters (assuming it's a stored procedure...)

Tim

wbhall13
Starting Member

4 Posts

Posted - 2004-06-17 : 12:03:33

quote:
Originally posted by timmy

Print will add a C/R.

You're better off creating a new parameter to recieve the email string, so instead of:
Print @email + ';'
you would have
SET @emailList = @emailList + @email + ';'

Then at the end you can either call Print @emailList or you can return the string as an output parameters (assuming it's a stored procedure...)

Tim

wbhall13
Starting Member

4 Posts

Posted - 2004-06-17 : 12:23:26

All, Thanks for your help.... I used the following solution to resolve the string issue:
declare @emailList varchar(100)
declare @email varchar(300)
declare xemail cursor for
SELECT [Pager] FROM [CNOC].[dbo].[SLGContacts_Email] where [Service Group]like 'White Plains1%' and [Work Center ID]='416wp500' and [Y1]='1'
set @email=''
open xemail
while(0=0) begin
fetch next
from xemail
into @emailList
if (@@Fetch_status <>0) Break
SET @email = @email + @emailList +'; '
END
close xemail
deallocate xemail
Select @email as Group_Mail
SET NOCOUNT OFF
GO

Output:
Group_Mail wsif@xxt.com; wsif@xxt.com; wsif@xxt.com; wsif@xxt.com; wsif@xxt.com;
(1 row(s) affected)

How do I place the above output into my ASPmail address field? Here's what I have so far: (everything works except the email address output) I recieve the following error after submitting my form.. "Invalid addresses"

if Notification.email_alarm.Value = "1" then
Dim Mailer, SQL, Connection, ErrorMessage, mylist
SQL = "UPDATE B_Notification SET email_date = '" & Notification.email_date.Text & "' WHERE Records ='" & CCDLOOKUP("Records", "Records_Count","",DBCNOC) & "' And hrid = '" & Notification.hrid.Text & "'"
Set Connection = New clsDBCNOC
Connection.Open
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0

mylist = "declare @emailList varchar(100) declare @email varchar(300) declare xemail cursor for SELECT Pager FROM SLGContacts_Email where [Service Group] like 'White Plains1' and [Work Center ID]='416wp500' and [Y1]='1'" &_
"set @email='' open xemail while(0=0) begin fetch next from xemail into @emailList if (@@Fetch_status <>0) Break SET @email = @email + @emailList +'; '" &_
"END close xemail deallocate xemail Select @email as mailid SET NOCOUNT OFF GO"

Set Connection = New clsDBCNOC
Connection.Open
Connection.Execute(mylist)
'SET list = mylist
ErrorMessage = CCProcessError(Connection)
Set Mailer = Server.CreateObject("Persits.MailSender")
Mailer.From = CCDLookUp("email", "P_Master_Employee", "emp_hrid='9594227'", DBCNOC)
Mailer.FromName = CCDLookUp("emp_first, emp_last", "P_Master_Employee", "emp_hrid='9594227'", DBCNOC)
Mailer.AddAddress mylist
Mailer.Host = "localhost"
Mailer.IsHTML = True
Mailer.Subject = "CNOC - KPI Alarm Notification"
Mailer.Body = "CNOC - KPI Alarm Notification<br><br>" & "Service Level Group: " & Notification.ServiceLevel.Text & "<br>" & "Highest Alarm WorkCenter: " & Notification.Workcenter.Text & "<br>" &_
"Date: " & Notification.Date1.Text & "<br>" & "Start Time: " & Notification.StartTime.Text & "<br>" & "End Time: " & Notification.EndTime.Text & "<br>" &_
"Category: " & Notification.Category.Text & "<br>" & "Alarm Status: " & Notification.TOA.Text & "<br>" & "Alarm Level" & Notification.TOAL.Text & "<br>" &_
"Total Tickets: " & Notification.Total_Tkts.Text & "<br>" & "Red Tickets: " & Notification.Red_Total_Tkts.Text & " Yellow Tickets: " & Notification.Yellow_Total_Tkts.Text &_
" Green Tickets: " & Notification.Green_Total_Tkts.Text & "<br>" & " No. of people on Tickets: " & Notification.NPOT.Text & "<br>" & "Root Cause: " &_
Notification.RootCause.Text & "<br>" & "Analysis: " & Notification.Analysis.Text & "<br>" & "Observations: " & Notification.Observations.Text
Mailer.Send
set Mailer = Nothing
Connection.Close
Set Connection = Nothing
On Error Goto 0
else
end if

Thnaks,

Bill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources