Showing posts with label setting. Show all posts
Showing posts with label setting. Show all posts

Friday, February 24, 2012

Execute Package Task: setting child variables

Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.

How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

Thanks

fleo wrote:

Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.

How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

Thanks

No, it's not possible using the Execute Package Task. However, using Parent Package Configurations, you can hook the parent variable to a child variable.|||

fleo wrote:

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

You could use execute process task to run the child packages via dtexec and using the /SET option for pushing the parameter. The only problem I see with that is that the master package will not know when the package finish as opposed to Execute package task.

Just an idea you do the test.

|||

We have a "common" stored proc to run SSIS packages (dtexec is called by that sp).
It can be used in a Execute SQL task and we are able to determine when the execution is done and wheter the package was executed successfully. But building the command and retrieving the result is quite a hassle...

The parent/child package configuration will do the trick. The only thing I dislike is the parent being dependent upon the child (maybe I'm from another school of thought).

Thanks all for your input.

Friday, February 17, 2012

Execute msdb.dbo.sp_sqlagent_get_perf_counter

Hello:
Need some help. I do not recall setting up to run
msdb.dbo.sp_sqlagent_get_perf_counter
But it is running and taking up quite a bit of CPU time, about 60%, in the
server. Would it be ok if I kill it. Any benefit in running it?
I do not see it in the Jobs list, where is it set?
Thanks,
Q
If you haven't defined any alerts, it is from the Demo
alerts installed by default. You can delete them if you
aren't using them.
The stored procedure checks performance conditions for
alerts. It won't run if you don't have any alerts defined.
If you do have alerts, it runs at a default of every 20
seconds which is in the registry under
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstance\SQLServerAgent.
Under SQLServerAgent, the value for
PerformanceSamplingInterval is set in seconds. Setting it to
0 will disable this entirely if you aren't using any such
alerts.
-Sue
On Wed, 14 Dec 2005 17:46:02 -0800, "Q"
<Q@.discussions.microsoft.com> wrote:

>Hello:
>Need some help. I do not recall setting up to run
>msdb.dbo.sp_sqlagent_get_perf_counter
>But it is running and taking up quite a bit of CPU time, about 60%, in the
>server. Would it be ok if I kill it. Any benefit in running it?
>I do not see it in the Jobs list, where is it set?
>Thanks,
>Q

Execute msdb.dbo.sp_sqlagent_get_perf_counter

Hello:
Need some help. I do not recall setting up to run
msdb.dbo.sp_sqlagent_get_perf_counter
But it is running and taking up quite a bit of CPU time, about 60%, in the
server. Would it be ok if I kill it. Any benefit in running it?
I do not see it in the Jobs list, where is it set?
Thanks,
QIf you haven't defined any alerts, it is from the Demo
alerts installed by default. You can delete them if you
aren't using them.
The stored procedure checks performance conditions for
alerts. It won't run if you don't have any alerts defined.
If you do have alerts, it runs at a default of every 20
seconds which is in the registry under
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstance\SQLServerAgent.
Under SQLServerAgent, the value for
PerformanceSamplingInterval is set in seconds. Setting it to
0 will disable this entirely if you aren't using any such
alerts.
-Sue
On Wed, 14 Dec 2005 17:46:02 -0800, "Q"
<Q@.discussions.microsoft.com> wrote:
>Hello:
>Need some help. I do not recall setting up to run
>msdb.dbo.sp_sqlagent_get_perf_counter
>But it is running and taking up quite a bit of CPU time, about 60%, in the
>server. Would it be ok if I kill it. Any benefit in running it?
>I do not see it in the Jobs list, where is it set?
>Thanks,
>Q

Execute msdb.dbo.sp_sqlagent_get_perf_counter

Hello:
Need some help. I do not recall setting up to run
msdb.dbo.sp_sqlagent_get_perf_counter
But it is running and taking up quite a bit of CPU time, about 60%, in the
server. Would it be ok if I kill it. Any benefit in running it?
I do not see it in the Jobs list, where is it set?
Thanks,
QIf you haven't defined any alerts, it is from the Demo
alerts installed by default. You can delete them if you
aren't using them.
The stored procedure checks performance conditions for
alerts. It won't run if you don't have any alerts defined.
If you do have alerts, it runs at a default of every 20
seconds which is in the registry under
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstance\SQLServerAgent.
Under SQLServerAgent, the value for
PerformanceSamplingInterval is set in seconds. Setting it to
0 will disable this entirely if you aren't using any such
alerts.
-Sue
On Wed, 14 Dec 2005 17:46:02 -0800, "Q"
<Q@.discussions.microsoft.com> wrote:

>Hello:
>Need some help. I do not recall setting up to run
>msdb.dbo.sp_sqlagent_get_perf_counter
>But it is running and taking up quite a bit of CPU time, about 60%, in the
>server. Would it be ok if I kill it. Any benefit in running it?
>I do not see it in the Jobs list, where is it set?
>Thanks,
>Q

Wednesday, February 15, 2012

Execute As error

I have a stored procedure that needs to run with elevated privliges, so I tried setting it to run as one of the db owners. When a regular user tries to execute the SP, I get the error

Cannot execute as the database principal because the principal
"joejoe" does not exist, this type of principal cannot be impersonated, or
you do not have permission.

What do I need to set up so a user can be impersonated?

Can you post a quick repro of the code you used to create the user, and to execute as? You might just try:

execute as user ='dbo'

Since that is the dbo. Or execute as SELF, if the object is owned by DBO.

|||

Here you go.

ALTER PROCEDURE [dbo].[ClearStaleLocks]

with execute as 'mpswaim'

AS

BEGIN

SET NOCOUNT ON;

create table #spids

(

spid int not null

)

insert into #spids select spid from master.dbo.sysprocesses

delete locks where spid not in (select spid from #spids)

drop table #spids

END

|||

Did you try as SELF? It should work like you want. mpswaim is a user, who is the db_owner group, right? You really shouldn't need impersonation for this procedure to work anyhow, since all users have rights to sysprocesses.

Here is the script I used to reproduce the situation. I created the users without login for ease of testing, but if they are attached to logins it should make any difference.

create table dbo.locks
(
spid int
)
go
create user mpswaim without login
go
exec sp_addrolemember 'db_owner','mpswaim'
go
create PROCEDURE [dbo].[ClearStaleLocks]
with execute as 'mpswaim'
AS
BEGIN
SET NOCOUNT ON;
create table #spids
(
spid int not null
)
insert into #spids select spid from master.dbo.sysprocesses
delete locks where spid not in (select spid from #spids)
drop table #spids

END
go
create user test without login
go
grant execute on [dbo].[ClearStaleLocks] to test
go
execute as user = 'test'
go
[dbo].[ClearStaleLocks]
go

|||

I need impersonation so the SP can see other users sessions. Let me try creating a user w/out a login and see what that does.

|||

I think you miss my point. This is what I would try next:

alter PROCEDURE [dbo].[ClearStaleLocks]
with execute as self
AS
BEGIN
SET NOCOUNT ON;
create table #spids
(
spid int not null
)
insert into #spids select spid from master.dbo.sysprocesses
delete locks where spid not in (select spid from #spids)
drop table #spids

END
go

Self should make it execute in the context of DBO and give you the rights you want. The loginless user was simply for me to simulate users in a database.

|||

If I try to impersonate dbo, I get the error,

Cannot execute as the database principal because the principal \"dbo\" does not exist,
this type of principal cannot be impersonated, or you do not have permission.

|||Can you script out the users that you are using for this? Especially what roles they are in? I am thinking we are not quite connecting here. If you are building a procedure in the dbo schema, owned by dbo, and the user who is building the procedure is dbo, not to mention that you cannot drop the dbo user, then something is either wrong, or I am not hearing what you are saying (and that would not be the first time I have missed the point completely!)|||

Here's the user that I'm testing against.

CREATE USER [doolittle] FOR LOGIN [doolittle] WITH DEFAULT_SCHEMA=[dbo]

|||I've also tried granting impersonation privliges to doolitle. That didn't help either.|||

The reason why the impersonation doesn't work is due to the VIEW SERVER STATE permission. And this is a server level permission. This is required to view information for all SPIDS in sys.sysprocesses for example. And your impersonation is at the user level and hence it is restricted to the database resources only. There are ways to do this using a certificate login, grant server permissions to it, and signing your module with the certificate. Or set TRUSTWORTHY bit for the database ON depending on the impersonation model that you choose. BOL has the necessary examples to facilitate this scenario. Please see the following topics for more details and understanding how the impersonation context works.

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

http://msdn2.microsoft.com/en-us/library/ms345102.aspx

The module signing topic (2nd link above) has the specific example that will help resolve your problem.