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.
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 asDECLARESELECT assignmentsSELECT statementEXECsomewhere 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 53A 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?ThanksMike |
|
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? |
 |
|
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#6from [ken-sql-002].constructmobile.dbo.ccs_quotes qleft outer join [ken-sql-002].constructmobile.dbo.ccs_q_sections qson q.ccs_quo_quote_number = qs.ccs_qos_quote_numberleft outer join [ken-sql-002].constructmobile.dbo.ccs_site_meeting mon m.ccs_mee_quote_number = q.ccs_quo_quote_numberleft outer join [ken-sql-002].constructmobile.dbo.ccs_notes non q.ccs_quo_note_number = n.ccs_not_numberleft outer join [ken-sql-002].constructmobile.dbo.ccs_company_file fon q.ccs_quo_company_ref = f.ccs_cd_account_numberwhere q.ccs_quo_analysis_code = 'PENDING' |
 |
|
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. FineBUTLooking 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 |
 |
|
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 |
 |
|
|
|
|
|
|