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)
 Iteration to send Individual Emails

Author  Topic 

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-07 : 04:12:52
I have a query which will generate rows of information and one field is an email address.

What I then need to do is perform something to fire off individual instances of this row one at a time, like a card, displayed in particular format, which I have ready in HTML, and to send by email to PDA devices.

Can you tell me how to change this row format into spooling a set of individual email messages? I think each will use xp_smtp_sendmail. I have the variables declared to put into the HTML. I think I'll need cursors to produce the one-by-one instances. Will I need to use a FUNCTION, or stored procedure, or can I run the whole thing in just a query I can run?

I have the xp_smtp_sendmail template as

DECLARE
SELECT assignments
SELECT statement
EXEC

somewhere in there I'll need to set up a CURSOR, and a BEGIN and END...

But I get the error:

Server: Msg 141, Level 15, State 1, Line 53
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

But this is like saying you can't cook food and then eat it. How could you ever assign a value to a variable if you don't get the data for it?

Thanks
Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-07 : 04:17:41
What's the SELECT statement that throws this error?
Go to Top of Page

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-07 : 04:41:54
The query I paste below runs fine otherwise. I've added it to the xp_smtp_sendmail template, and at the top left I've now added the variables in there so that, when running, these are populated. I am mystified why this isn't allowed because you have to run a query, don't you?

I am hoping I don't have to resort to CURSORS and loops, but maybe I do. Running the query gives me rows. Maybe with these variables (declared above) they are taking one row's worth, yes? So maybe I need to make a table-valued FUNCTION, yes? Or just pop of one card per email address and get these sent...

How simple can I keep this?

Thanks a lot!!



select distinct @SOR = ccs_qos_item_ref,
@Operative = ccs_mee_oper_name,
@email = ccs_cd_email,
@date = ccs_quo_user_date#1, -- current datetime from jobcompletedUpdate
@desc = ccs_qos_title,
@text = ccs_not_text#1 + ' ' + ' ' + ccs_not_text#2 + ' ' + ccs_not_text#3,
@jobno = ccs_quo_ref_number#2,
@cust = ccs_quo_site_contact,
@client = ccs_quo_company_ref,
@address = ccs_quo_address#1 + '', '' + ccs_quo_address#2 + ', ' + ccs_quo_address#3 + ', ' + ccs_quo_address#4 + ', ' + ccs_quo_address#5 + ', ' + ccs_quo_address#6,
@cost = ccs_qos_quoted_price#6
from [ken-sql-002].constructmobile.dbo.ccs_quotes q
left outer join [ken-sql-002].constructmobile.dbo.ccs_q_sections qs
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
left outer join [ken-sql-002].constructmobile.dbo.ccs_site_meeting m
on m.ccs_mee_quote_number = q.ccs_quo_quote_number
left outer join [ken-sql-002].constructmobile.dbo.ccs_notes n
on q.ccs_quo_note_number = n.ccs_not_number
left outer join [ken-sql-002].constructmobile.dbo.ccs_company_file f
on q.ccs_quo_company_ref = f.ccs_cd_account_number
where q.ccs_quo_analysis_code = 'PENDING'
Go to Top of Page

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-07 : 06:06:16
OK!

I think I partly have the answer. I've read that each field in a SELECT statement can be assigned to a local variable but not put in a rowset in the same statement. You have to do one or the other. Fine

BUT

Looking at the query above, I concatenate text1, text2, text3 etc and also address1, address2... etc. Do I need to assign a variable to each of those fields, or eliminate those to get this to work? I understand if I want to get a rowset I can run a seperate SELECT statement, but in the same script, yes?

Thanks
Go to Top of Page

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-07 : 06:10:55
Can a concatenation stand as a single field? Can I make it? That's the intention. As well as just joining the text together, I want to call it one field technically. Also, something I didn't really think about when I put this query in here, I had to do away with the aliases I had for each column, "in here" and it didn't matter to me because I now had varable names @like_this
Go to Top of Page
   

- Advertisement -