SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


referencing Package variables in sp_send_dbmail


referencing Package variables in sp_send_dbmail

Author
Message
ptheberge
ptheberge
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 21
I am using result set, and looping through each record, generating an email for each record. Each email has a different subject and recipients. The values are stored in variables, but I'm not sure how to reference these variables in an email task. I have tried using the sp_send_dbmail and the Send Mail Task.

Please help.
Chuck Rivel
Chuck Rivel
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 357
you can take advantage of the Expressions in the Send Mail task. select Expressions and then click the ellipsis to select which Property you want to setup an Expression for. In your case, select the ToLine property and then in the Expression, select the User Variable to evaluate out to.

hth
ptheberge
ptheberge
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 21
That's what I was trying to do, but when I ran the job, it kept saying that there was no recipient specified. I tested out my SQL to make sure an email address was being selected and it was. Should the variable be defeined as a Package level variable or an Object variable?
ptheberge
ptheberge
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 21
It won't let me run this if the To section is blank within the Mail Tab of the Send mail Task. The examples I have seen show this fiewld being left blank at design time and being populated using Expressions at run time. Why wont this work for me? w00t
Chuck Rivel
Chuck Rivel
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 357
well, both a package level and object type can be the same variable since you are defining scope (package) and type (object), but that is neither here nor there for the issue.

the variable that you should be using in the expression should be String variable which is to be populated via your looping task.

let me take a stab at your design, are you working with a For Each Loop task and which is being run via an Execute SQL Statement to populate an Object type variable. That object variable is the input of the For Each loop?

If so, you will want to ensure that you are mapping a string variable to get the Recipients in the Variable Mappings of the For Each loop. The mapping will be from the field in the recordset to the newly created String variable.

In the Expressions for the Send Mail task, map the ToLine property to the string variable.

If I am way off base on the design, please let me know what you are doing then to initiate the loop process.
Mukti
Mukti
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 747
Try setting the DelayValidation = True on Send Mail Item. I think its trying to validate it before the variables are set.

HTH
~Mukti
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search