Accessing jobs in EM

Hello!
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
IgorThere is no supported way to do this with Enterprise
Manager. The proxy account doesn't really come in to play
here. The system stored procedures involved in displaying
the job info in Enterprise Manager have checks for job owner
or sysadmin server role membership.
-Sue
On Fri, 21 Jan 2005 14:46:12 -0800, "Igor Marchenko"
<igormarchenko@.hotmail.com> wrote:

>Hello!
>
> I would like to allow non-admin users seeing all jobs (including ones
>they do now own) in Enterpise Manager.SQL Profiler displays execution of
>exec msdb..sp_help_job when querying job list. According to BOL: ' A user
>who is not a member of the sysadmin fixed role can use sp_help_job to view
>only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't b
e
>of any help in this case. Is this possible for non-admin users to see all
>jobs?
>
>Thanks,
>Igor
>|||Thanks,Sue. I have found another way:
1.. Grant access to MSDB
2.. Add users to the member of TargetServerRole.
Regards,
Igor
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lvnav0d2r3c3n64s620e9me5o0o7t1pdl6@.
4ax.com...
> There is no supported way to do this with Enterprise
> Manager. The proxy account doesn't really come in to play
> here. The system stored procedures involved in displaying
> the job info in Enterprise Manager have checks for job owner
> or sysadmin server role membership.
> -Sue
> On Fri, 21 Jan 2005 14:46:12 -0800, "Igor Marchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>|||Okay but just remember it's not supported though and how
this works with this role depends on what service pack you
are on.
-Sue
On Tue, 25 Jan 2005 10:39:09 -0800, "Igor Marchenko"
<igormarchenko@.hotmail.com> wrote:

>Thanks,Sue. I have found another way:
> 1.. Grant access to MSDB
> 2.. Add users to the member of TargetServerRole.
>Regards,
>Igor
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:lvnav0d2r3c3n64s620e9me5o0o7t1pdl6@.
4ax.com...
>|||Thanks a lot,Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:5f5dv0d14fq7ajas3r6qq577is20d0gn57@.
4ax.com...
> Okay but just remember it's not supported though and how
> this works with this role depends on what service pack you
> are on.
> -Sue
> On Tue, 25 Jan 2005 10:39:09 -0800, "Igor Marchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>

Accessing Estimated Query Execution Plan (QEP) statisitics

Hi,

I have a question about estimated query execution plans that are
generated in QA of MSSQL.

If I point at an icon/physical operator in the estimated QEP, it shows
me
some statistics about the operator.
Is there a way to retrieve these statistics through a query, i.e., can
these statistics be available to the user?

Also, is there a way to generate these statistics on my own?

thanks in advance

-TC.T Chaudhary (replysoon_04@.yahoo.com) writes:
> I have a question about estimated query execution plans that are
> generated in QA of MSSQL.
> If I point at an icon/physical operator in the estimated QEP, it shows
> me
> some statistics about the operator.
> Is there a way to retrieve these statistics through a query, i.e., can
> these statistics be available to the user?

Yes. It's all done through SQL statements. Rather than saying the exact
statements to use(*), I will tell you how find out: Use the SQL Server
Profiler to see what commands that Query Analyzer submits to SQL Server
when you have enabled these options.

(*) Since to tell which they are, *I* would have to run Profiler.

> Also, is there a way to generate these statistics on my own?

I'm not sure that I understand the questions, but if I'm guessing right
the answer is: no, you can't.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"T Chaudhary" :
> Hi,
> I have a question about estimated query execution plans that are
> generated in QA of MSSQL.
> If I point at an icon/physical operator in the estimated QEP, it shows
> me
> some statistics about the operator.
> Is there a way to retrieve these statistics through a query, i.e., can
> these statistics be available to the user?
> Also, is there a way to generate these statistics on my own?
> thanks in advance
> -TC.

TC,

See SET SHOWPLAN_ALL IN BOL for getting the data you're asking about. To
get the results of your query *and* the plan, see SET STATISTICS PROFILE.

Craig

P.S. Playing with these for a while will give you some real appreciation for
QA! :)

Access to TaskHost from derived Task class.

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

tviel wrote:

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

I might be wrong about this, but I believe that that a Task object can be cast as a TaskHost object at design time and run time.

I hope this helps.

|||

If you look at the two objects and what they inherit or implement, you can see that will never happen.

You can get the Task from a TaskHost using the TaskHost.InnerObject property but not the other way. The TaskHost is what deals in the world of containers, the task is the guts that sits below all that to do the actual work.

Your task will always be in a container, as even the package is a container. For example, Sequence , Package and TaskHost all inherit from EventsProvider, which in turn inherits from DtsContainer. Is you test ever going to be valid, even if we could get to the task host in the task.

Why do you need to do this? Perhaps we could offer an alternative solution is you explain the underlying requirement.

|||

Hi Darren,

thanks for your reply, maybe there is another alternative for generating a solution.

I try to configure (in Design-Time) all tasks of a sequencecontainer by one. Let's say I have some custom tasks, who all have the same properties. For normal use, you can configure the tasks by a custom UI. To check, if the taskHost.Parent is of Type Sequence works, as the custom UI sets the input fields to readonly, when using the tasks in a sequenc container.

Now I wanna provide a custom tasks, having a UI for the same variables as the upper tasks. Adding to the sequence container, and setting the values, it should update all tasks in the container with the user values read.

For so, i have the ability to configure some values of similar tasks by one.

Hope this makes things clear.

Thanks.

PS: AFAIK there is no collection in DtsContainer to iterate over the child tasks.

|||

I am wanting to do something similar to allow me to have access to the TaskHost. I have a controll flow task and I want it to be able to read the initial start time from the outer-most container. I would also like to get the path of the Package that this task is in.

Example:

I call dtexec to run Package_a.dtsx
Package_a.dtsx calls Package_b.dtsx
Package_b.dtsx has MyCustomTask.

I want MyCustomTask to know 1) the start time of Package_a.dtsx and 2) the disk path of Package_b.dtsx

Thanks,

Graham

|||Even the package itself does not "know" the disk path it was loaded from. The package can be loaded from SQL server, or constructed in memory using API without saving it at all, or loaded from file and then completely modified, etc, so the disk path does not always makes sense at all. So there is no way for a task to find out the location from where the package was loaded. If you need some package marker, use a package-level variable instead.|||

Hi,

understandable.

But, I wanna provide a custom task "communicating" with other tasks of itself in a simple manner. Using variables needs the designer to set this variables by hand or committing new variables during Prompt, due to the missing ability to generate variables programmatically and silent. This is a possible error source and not very intuitive.

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

Thanks

Thorsten

|||

tviel wrote:

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

This part is very simple: the Sequence has Executables property that returns list of task hosts or child containers.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.sequence.executables.aspx

|||

Hi Michael,

fine, that should do the trick for me. For now, I phone my oculist to get my eyes checked :-)

Thanks

Thorsten

|||

What about the other part of my question - Can I find out the start time of the initial package while within the Execute method of a Task? Could a connection manager help with that?

Thank you,

Graham

|||

If by initial package you mean the package that hosts the task whose Execute method does the work, then yes, look at the system variable StartTime. That is the package start time.

If the initial package is a parent package that calls a child package, then no, since the child package really has no knowledge of the parent, the parent could be anybody there is no interface that defines parent details. See Michael's post. You could help things though by passing in the parent variable to the chuld. Use a parent package configuration to do this perhaps? Store it in a variable and the n read that variable in the task.

|||

Figured it out. It is sort of like you said, Darren. If you define a static variable on your Task class, you can assign to it in the Execute method with a value obtained at design time. The example below shows how to get the Name of the top most package (e.g. using the scenario from my earlier post, that would be Package_A )

class CustomTask : Task
{
private static string topMostPackage = null;

private string _package;
//This property is accessed at design time
//through the UI class's TaskHost.
public string PackageName{
get{return _packageName;}
set{_packageName = value;}
}

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents
componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult result = DTSExecResult.Success;
_executionPIT = DateTime.Now;

//this will only be true on the first task that executes
if (string.IsNullOrEmpty(topMostPackage))
topMostPackage = _packageName;
}
}

Access to TaskHost from derived Task class.

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

tviel wrote:

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

I might be wrong about this, but I believe that that a Task object can be cast as a TaskHost object at design time and run time.

I hope this helps.

|||

If you look at the two objects and what they inherit or implement, you can see that will never happen.

You can get the Task from a TaskHost using the TaskHost.InnerObject property but not the other way. The TaskHost is what deals in the world of containers, the task is the guts that sits below all that to do the actual work.

Your task will always be in a container, as even the package is a container. For example, Sequence , Package and TaskHost all inherit from EventsProvider, which in turn inherits from DtsContainer. Is you test ever going to be valid, even if we could get to the task host in the task.

Why do you need to do this? Perhaps we could offer an alternative solution is you explain the underlying requirement.

|||

Hi Darren,

thanks for your reply, maybe there is another alternative for generating a solution.

I try to configure (in Design-Time) all tasks of a sequencecontainer by one. Let's say I have some custom tasks, who all have the same properties. For normal use, you can configure the tasks by a custom UI. To check, if the taskHost.Parent is of Type Sequence works, as the custom UI sets the input fields to readonly, when using the tasks in a sequenc container.

Now I wanna provide a custom tasks, having a UI for the same variables as the upper tasks. Adding to the sequence container, and setting the values, it should update all tasks in the container with the user values read.

For so, i have the ability to configure some values of similar tasks by one.

Hope this makes things clear.

Thanks.

PS: AFAIK there is no collection in DtsContainer to iterate over the child tasks.

|||

I am wanting to do something similar to allow me to have access to the TaskHost. I have a controll flow task and I want it to be able to read the initial start time from the outer-most container. I would also like to get the path of the Package that this task is in.

Example:

I call dtexec to run Package_a.dtsx
Package_a.dtsx calls Package_b.dtsx
Package_b.dtsx has MyCustomTask.

I want MyCustomTask to know 1) the start time of Package_a.dtsx and 2) the disk path of Package_b.dtsx

Thanks,

Graham

|||Even the package itself does not "know" the disk path it was loaded from. The package can be loaded from SQL server, or constructed in memory using API without saving it at all, or loaded from file and then completely modified, etc, so the disk path does not always makes sense at all. So there is no way for a task to find out the location from where the package was loaded. If you need some package marker, use a package-level variable instead.|||

Hi,

understandable.

But, I wanna provide a custom task "communicating" with other tasks of itself in a simple manner. Using variables needs the designer to set this variables by hand or committing new variables during Prompt, due to the missing ability to generate variables programmatically and silent. This is a possible error source and not very intuitive.

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

Thanks

Thorsten

|||

tviel wrote:

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

This part is very simple: the Sequence has Executables property that returns list of task hosts or child containers.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.sequence.executables.aspx

|||

Hi Michael,

fine, that should do the trick for me. For now, I phone my oculist to get my eyes checked :-)

Thanks

Thorsten

|||

What about the other part of my question - Can I find out the start time of the initial package while within the Execute method of a Task? Could a connection manager help with that?

Thank you,

Graham

|||

If by initial package you mean the package that hosts the task whose Execute method does the work, then yes, look at the system variable StartTime. That is the package start time.

If the initial package is a parent package that calls a child package, then no, since the child package really has no knowledge of the parent, the parent could be anybody there is no interface that defines parent details. See Michael's post. You could help things though by passing in the parent variable to the chuld. Use a parent package configuration to do this perhaps? Store it in a variable and the n read that variable in the task.

|||

Figured it out. It is sort of like you said, Darren. If you define a static variable on your Task class, you can assign to it in the Execute method with a value obtained at design time. The example below shows how to get the Name of the top most package (e.g. using the scenario from my earlier post, that would be Package_A )

class CustomTask : Task
{
private static string topMostPackage = null;

private string _package;
//This property is accessed at design time
//through the UI class's TaskHost.
public string PackageName{
get{return _packageName;}
set{_packageName = value;}
}

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents
componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult result = DTSExecResult.Success;
_executionPIT = DateTime.Now;

//this will only be true on the first task that executes
if (string.IsNullOrEmpty(topMostPackage))
topMostPackage = _packageName;
}
}