SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Activex Script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cindylee
Yak Posting Veteran

Australia
55 Posts

Posted - 08/02/2005 :  20:25:40  Show Profile  Reply with Quote
Hi gurus,
I am trying a accomplish a mail task which gets its information from the tables. iam using DTS[Activex and send email tasks].

table info:

agentid agentmail orderno
1 xx@hh vinet
2 xy@hh husk
1 xx@hh husk

i need to loop through all the table(currently iam doing it using global variable) and send mail to the agents with messagetext as there orderno(s)
I figured to send mail when an agent has one order but iam stuck when the agent has more than 1 order .
Can you anyone please help /advice any better solution.

Thanks
Cindy

Edited by - cindylee on 08/03/2005 22:07:12

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 08/02/2005 :  20:31:25  Show Profile  Visit timmy's Homepage  Reply with Quote
Cindy,

You can use CDO objects within the DTS ActiveX tasks:

	Set objConfig = CreateObject("CDO.Configuration")
	With objConfig.Fields
		.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
		.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServerName (probably your exchange server)
		.Update
	End With

	' Instantiate the CDO Message object.
	Set objMail = CreateObject("CDO.Message")
	
	On Error Resume Next

	With objMail
		Set .Configuration = objConfig
		.From = "spam@yourcompany.com"
		.To = "spammed@customer.com"
		.Subject = "Subject Line"
		.TextBody = "Body Text"
		.ReplyTo = "noreply@yourcompany.com"
		.Send
	End With

You'll just need to write some code to work through the records and send the emails. This is a lot easier to control than the sendmail tasks, but it's a bit of extra work.

HTH,

Tim
Go to Top of Page

cindylee
Yak Posting Veteran

Australia
55 Posts

Posted - 08/02/2005 :  21:15:41  Show Profile  Reply with Quote
tim,
As i said the issue is sending mail for a agent who has more than one order no.
my script works fine when an agent has one order.
i need a script for a agent who has more than one order.
i guess iam confusing you all

thanks
cindy
Go to Top of Page

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 08/02/2005 :  21:27:46  Show Profile  Visit timmy's Homepage  Reply with Quote
I understand what you're getting at; my point was that an alternative to sendmail might be easier to maintain.

What will the email contain?

It may be easier to generate your source records based on what you want to send, then use sendmail to send them. By this I mean create some stored procedure that takes your table
agentid agentmail orderno
1 xx@hh vinet
2 xy@hh husk
1 xx@hh husk

and transforms it into:
agentid agentmail ordernumbers
1 xx@hh vinet, hust
2 xy@hh husk

And then sends it.

Can you post the code you're using to send the mail?

Cheers,

Tim
Go to Top of Page

cindylee
Yak Posting Veteran

Australia
55 Posts

Posted - 08/03/2005 :  20:14:35  Show Profile  Reply with Quote
Thanks Tim, that gave me some idea. But can you please tell me how you do that conversion. I tried the cross tab query and couldnt get it work.

agentid agentmail orderno Orderdate
1 xx@hh vinet 10/01/04
2 xy@hh husk 11/02/04
1 xx@hh husk 13/05/04

the output should be

agentid agentmail ordernumbers orderdate
1 xx@hh vinet, hust 10/01/04,13/05/04
2 xy@hh husk 11/02/04

Any help greatly appreciated

Thanks
Cindy



Go to Top of Page

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 08/03/2005 :  20:36:44  Show Profile  Visit timmy's Homepage  Reply with Quote
You'll need to create a stored procedure that uses a loop to create the output.
Do you have an identity field on your orders table? It will make things much easier...

Go to Top of Page

cindylee
Yak Posting Veteran

Australia
55 Posts

Posted - 08/03/2005 :  21:07:06  Show Profile  Reply with Quote
Yes
The identity field is Orderid .

Hope it helps
Cindy
Go to Top of Page

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 08/03/2005 :  21:35:41  Show Profile  Visit timmy's Homepage  Reply with Quote
This basically does it. It might need some tweaking to get it to exactly what you need:
Declare @output TABLE(agentid int, agentmail varchar(30), ordernos varchar(100), OrderDate varchar(100))

declare @orderID int,
	@agentMail varchar(20),
	@orderNo varchar(20),
	@orderDate datetime,
	@agentID	int,
	@orderNos varchar(100),
	@orderDates varchar(100)

SET @agentID = 0

WHILE EXISTS (SELECT NULL FROM zOrders WHERE agentID > @agentID)
BEGIN
	SELECT TOP 1 @agentID = agentID
	FROM zOrders
	WHERE agentID > @agentID
	ORDER BY agentID ASC
	SELECT @orderID = 0, @orderNos = '', @orderDates = ''
	WHILE EXISTS (SELECT NULL FROM zOrders WHERE agentID = @agentID AND orderid > @orderID)
	BEGIN
		SELECT TOP 1 @orderID = orderID, @agentMail = agentMail, @orderNo = orderNo, @orderDate = orderDate
		FROM zOrders
		WHERE orderID > @orderID AND agentID = @agentID 
		ORDER BY orderID ASC
		SELECT @orderNos = @orderNos + @orderNo + ', ', @orderDates = @orderDates + Convert(varchar, @orderDate, 101) + ', '


	END
	SELECT @orderNos = LEFT(@orderNos, LEN(@orderNos)-1), @orderDates = LEFT(@orderDates, LEN(@orderDates)-1)
	INSERT INTO @output(agentid, agentmail, ordernos, OrderDate)
	VALUES (@agentID, @agentMail, @orderNos, @orderDates)
END
SELECT * FROM @output

HTH,

Tim
Go to Top of Page

cindylee
Yak Posting Veteran

Australia
55 Posts

Posted - 08/03/2005 :  22:03:50  Show Profile  Reply with Quote
Interesting script. thanks tim.
i will implement and let you know the outcome

Ta Cindy
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000