How can I grab a notification email address from a job?

Is there a handy-dandy way for me to be able, from a stored procedure, to capture the notification email address for a user associated with a job?

I need to send an email out from a stored procedure, and I want it to be set up to send to the same user that is set up in the job that calls the stored procedure.

the same notification stored proc will be used in multiple jobs, but the jobs are already set up with a notification email to be sent to the defined users "on completion" (error or not). I want a separate email to be sent, but I want to use xp_Sendmail because I have to perform a select who's output I want to appear in the body of the email (and unless I am missing something, I have no control over the body of the standard "job completion" email).

I would prefer to NOT have to "hard code" the user email address in the stored proc that calls xp_sendmail, OR to put it in some user table in the database, when the user I want to send it to will always be the same one defined in at the JOB level in Enterprise Manager.

Any thoughts? Thanks in advance for your instantaneous and informative answer-packed responses! ;)I'm shooting from the hip on this, but I'd start with:SELECT j.name, o.email_address
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysoperators AS o
ON (o.id = j.notify_email_operator_id)-PatP|||That's what I was looking for, Oh Great One...

Or something to that effect...I obviously need to get more accustomed to the sys tables...but tried as many ways as I could think of to find a reference to something like that in BOL...but alas, apparently didn't know what to search for *L*

I guess I need to break down and read my SQL200 Unleashed book and learn a little about the architecture that is padding my paycheck *sigh*

As always, thanks for the gentle nudge ;)

How can I get the package name within a script?

Greetings,

I need to programmatically grab the name of the executing package so that it can be used in logging. Does someone have a code sample for this?

Thanks!

Use the system variable, PackageName.|||

Thanks.

For those who may benefit from a code sample:

Dim source AsString

Dim vars As Variables

Dts.VariableDispenser.LockOneForRead("System:ackageName", vars)

source = vars("PackageName").Value.ToString()

|||Also the other options (perhaps not for this example) are to use a derived column to add the system variable to the data flow, or in the control flow, using an Execute SQL task, you can map the variable to a parameter in the SQL statement.

How can I get c# text and use as parameter?

I want to grab what is in textbox1, put in string, and send as a query
to bring up report with only textbox1 occurances in table. How do I do
that please?
Thanks,
Trint"trint" <trinity.smith@.gmail.com> wrote in message
news:1104768719.403928.182220@.f14g2000cwb.googlegr oups.com...
>I want to grab what is in textbox1, put in string, and send as a query
> to bring up report with only textbox1 occurances in table. How do I do
> that please?
> Thanks,
> Trint

You will probably get a better answer in a C# or .NET forum - from a pure
MSSQL point of view, you probably want to look at creating a stored
procedure which takes @.mystring as a parameter. But there are different ways
to call a stored proc (check out the Command and Connection objects in ADO),
which is why you might want to try a different group for a more exact
reponse.

Simon