EXECUTE AS question: manager

Showing posts with label manager. Show all posts

Tuesday, March 27, 2012

Executing exe delphi program with Agent

Hi,
I'm trying to execute a program from de agent. The program executes(i can see it in the task manager), but it block in that step and still in the task manager.
I tried with the calc.exe command with de same result.
?only DOS commands can execute the agent or there is another way to make it work?
Thanks in advance
Lomu
Hi
If the program shows a GUI then you should not run it from a scheduled job.
If you make it a command line program that will run and exit correctly then
you may be ok. Depending on what you are wanting to do there could be other
alternatives, such as writing an extended stored procedure, making it a COM
object.
John
"Lomu" <Lomu@.discussions.microsoft.com> wrote in message
news:BCDDC329-81E3-457E-90E4-3C38A830AF92@.microsoft.com...
> Hi,
> I'm trying to execute a program from de agent. The program executes(i can
see it in the task manager), but it block in that step and still in the task
manager.
> I tried with the calc.exe command with de same result.
> only DOS commands can execute the agent or there is another way to make
it work?
> Thanks in advance
> Lomu

Executing exe delphi program with Agent

Hi,
I'm trying to execute a program from de agent. The program executes(i can see it in the task manager), but it block in that step and still in the task manager.
I tried with the calc.exe command with de same result.
Â¿only DOS commands can execute the agent or there is another way to make it work?
Thanks in advance
LomuHi
If the program shows a GUI then you should not run it from a scheduled job.
If you make it a command line program that will run and exit correctly then
you may be ok. Depending on what you are wanting to do there could be other
alternatives, such as writing an extended stored procedure, making it a COM
object.
John
"Lomu" <Lomu@.discussions.microsoft.com> wrote in message
news:BCDDC329-81E3-457E-90E4-3C38A830AF92@.microsoft.com...
> Hi,
> I'm trying to execute a program from de agent. The program executes(i can
see it in the task manager), but it block in that step and still in the task
manager.
> I tried with the calc.exe command with de same result.
> ¿only DOS commands can execute the agent or there is another way to make
it work?
> Thanks in advance
> Lomu

Executing exe delphi program with Agent

Hi,
I'm trying to execute a program from de agent. The program executes(i can se
e it in the task manager), but it block in that step and still in the task m
anager.
I tried with the calc.exe command with de same result.
?only DOS commands can execute the agent or there is another way to make it
work?
Thanks in advance
LomuHi
If the program shows a GUI then you should not run it from a scheduled job.
If you make it a command line program that will run and exit correctly then
you may be ok. Depending on what you are wanting to do there could be other
alternatives, such as writing an extended stored procedure, making it a COM
object.
John
"Lomu" <Lomu@.discussions.microsoft.com> wrote in message
news:BCDDC329-81E3-457E-90E4-3C38A830AF92@.microsoft.com...
> Hi,
> I'm trying to execute a program from de agent. The program executes(i can
see it in the task manager), but it block in that step and still in the task
manager.
> I tried with the calc.exe command with de same result.
> only DOS commands can execute the agent or there is another way to make
it work?
> Thanks in advance
> Lomu

Monday, March 26, 2012

executing DTS Package from stored procedure

There is a DTS package that work when when it is executed directly
from Enterprise Manager or dtsrun utility.
But it does not work when it is executed from a stored procedure.
Do you have any idea?How are you executing the DTS package from the proc? What does "does not
work" mean? Turn on logging (under Package Properties) to see any error
messages.
David Portas
SQL Server MVP
--|||Following is the code of proc that call DTS.
When I run DTS package directly from Enterprise Manager, it works as I want
(185 rows added).
But when I use following procedure, there is no row added.
CREATE PROC [dbo].[Usp_CFDBE13ExecuteDTSPkg]
@.PkgName varchar(255)
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int
--1. Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
--Create Package object failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG, @.PkgName
RETURN 1
END
--2. Load the Pkg
EXEC @.hr = sp_OAMethod @.oPKG,'LoadFromSqlServer', NULL,
@.ServerName='', @.PackageName=@.PkgName, @.Flags=256
IF @.hr <> 0
BEGIN
-- LoadFromSQLServer failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--3.Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
--Execute failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--4.Check Pkg Errors
--EXEC @.ret=spDisplayPkgErrors @.oPKG --Will do it later
--5.Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
--UnInitialize failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--6.Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
RETURN 0
GO|||If you haven't already done so, turn on logging in the package so that
you can trap errors there and determine that the package does run. The
package execute method alone won't show up package errors.
Using a blank server name in the LoadFromSQLServer method defaults to
the default server instance _not_ to the instance in which your code is
executing. If you are targeting a non-default instance you must specify
the name. Better still, specify the name anyway. You can use
SERVERPROPERTY('SERVERNAME') to retrieve the name of the current
instance if you need to.
Be aware that your package will execute in the security context of the
local or domain account configured for the SQL Server service. That
account must therefore have access to any files or other resources
required by the DTS package.
Finally, make sure that your package uses fully qualified names for any
files that it accesses. The drive mappings available when you execute
locally in Enterprise Manager may not be available on the server.
David Portas
SQL Server MVP
--|||Thank you very much.
I got it.

Monday, March 19, 2012

Execute windows on client machine

Hello.

How to execute add users window, backup wizard and other windows from
Enterprice Manager on client machine? Does MDAC or sth is able to do this?

bye...

--
__ __
|__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
|__// |__|| | \\ \__// / \\ +----------+
+[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+Hi

It is not clear exactly what you are wishing, but at a guess you have EM on
the client machine but your server is not registered using an account that
is sufficiently privileged.

You may want to write your own interface that calls the appropriate stored
procedures.

John

"[BuKoX]" <bukox.wytnij@.tlen.pl> wrote in message
news:186574153.20041223193834@.tlen.pl...
> Hello.
> How to execute add users window, backup wizard and other windows from
> Enterprice Manager on client machine? Does MDAC or sth is able to do this?
> bye...
> --
> __ __
> |__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
> |__// |__|| | \\ \__// / \\ +----------+
> +[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+

Sunday, February 26, 2012

Execute permission lost for nonadmin user after db migration with attach

I have moved a SQL2000 database from one machine to another by detaching and
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitriedimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
--
David Gugick
Imceda Software
www.imceda.com|||Hi
Your problem is that users are stored in master. You moved your DB from one
server to another one that does not have the same users in it's master.
Generally, before you detach and attach, script out the users and
permissions, remove the permissions, detach the DB, attach the DB and then
re-apply the users and permissions from the script.
The user ID between syslogings and sysusers in the master and user DB must
match exactly, the name is not a good enough match.
You can also copy users between servers by creating a new DTS task using the
'Transfer Logins Task' and running it.
Regards
Mike
"dimitrie" wrote:
> I have moved a SQL2000 database from one machine to another by detaching and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
>|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost after database migration with attach

Sunday, February 19, 2012

Execute Package Task says Login failed for 'sa'

Please help me with this error, I am desperate.

SSIS package "Clear.dtsx" starting.
Error: 0xC0202009 at Clear, Connection manager "10.11.60.30.msdb.sa": An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.".
Error: 0xC00220E4 at Execute Package Task: Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.
.
Task failed: Execute Package Task
Warning: 0x80019002 at Clear: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Clear.dtsx" finished: Failure.
Did you run this package in BIDS or the command line? As we're talking about in your other similar post, SSIS doesn't store passwords when promoting them to the server for execution. Either that, or you have the wrong password indicated for "sa."|||Child package is using different username so error should not be for 'sa'. Parent is using sa in Connection which is used to call this child package.|||

Hi Fahad,

If you are absolutely certain the sa password is supplied correctly, the issue may lie with the Package ProtectionLevel property.

First, I would ensure the sa password is correct. Make sure you can connect to the msdb database on the target server using the sa account credentials and that you can execute some simple query: Select 1 As 'One' or Select GetDate() are my favorites. If this succeeds:

Try setting the ProtectionLevel property on the Control Flow properties to either EncryptAllWithPassword or EncryptSensitiveWithPassword. You will need to supply a PackagePassword for this test as well - it's located just above the ProtecionLevel property.
When you're done, rebuild and re-deploy the package. You will need to supply the password when executing the package. If this succeeds, the issue is with the Package ProtectionLevel.

To address this, I recommend using Windows Authentication to connect to the server.

Hope this helps,
Andy

Friday, February 17, 2012

Execute DTS Packages

I have created a functioning DTS package inside Enterprise Manager and
now I want to be able to execute it outside of EM. The package imports
data from an SQL dbase to a Visual FoxPro dbase. I would like this DTS
package to execute everytime an Insert(of certain critera) is made on
the SQL dbase. I am thinking that using an Insert Trigger on the SQL
table is the way to go about this. I am seeking advice as to... is this
the best way to go about this.

Also, and if so, in a trigger which route is the best to take...
1. use dtsrun command line utility or
2. setup a COM object to run the DTS package

I have tried both in SQL query analyzer and am having troubles with the
syntax. Could anyone possibly send the code for both ways. Thanks.
Good Day to all,

BrettIts not clear why you would want to do this... What are you doing in
the DTS that you cant do thru normal trigger? It would help if you can
explain more....

Not sure if you can run the package thru trigger.. May be set up a job
and invoke a job... I am also curious...|||I have tried linking SQL database to the VFP tables with T-SQL and have
had no luck. I have tried for weeks to link the two via Remote Servers
or T-SQL and working with directly in triggers. So far this is what I
have been able to get to work. If you have other ideas or any help
would be appreciated.

Brett|||You should be able to fire up DTSRUN using sp_oacreate in your trigger.
Have you tried this?

Having a job that calls the DTS is another option, then start the job
in your trigger with msdb..sp_start_job or something like that.
I have done both of these.|||Yes I have tried this without sucess. The package executes and works
properly when I run it manualy in EM. However, when I place the code
listed below in SQL Query Analyzer it does not execute. The code
parses fine, no errors and when it is executed QA returns 'completed
the command successfully', but nothing has happened. Here is the
code...

DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object,
'LoadFromSQLServer("ShannonPC", "sa", "", 256, , , ,
"MiTekTransfer")', NULL
IF @.hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
RETURN
END

Any ideas as to why this is not running my DTS package.

Thanks|||This code shows an example, I have used it before with success:

http://www.databasejournal.com/feat...cle.php/1459181

The first problem I see is, your LoadFromSQLServer parameter "256" is
incompatible with passing in "sa" as your username. "256" means
DTSSQLStgFlag_UseTrustedConnection, which says to use a trusted
connection. So my guess is that you're having a permissions problem.
Change the "256" to "0" and try again.

I am surprised that you got no error message back from sp_oacreate
though...

Gary|||Yes, I recongized this issue after I made the post and did some further
searching. If I use win. auth. in the package I set parameter to 256,
with no user or pass, and if I set SQL auth. in the package I set
parameter to 0 with user sa. I have tried both with the same
results... no errors and no execution.|||Do you have DTS package logging turned on? Can you tell it's not even
starting the package?

Try just starting DTSRUN.exe from a command line, and see if that
works.

sp_oacreate may be disabled as a security measure, but if it was I
would expect that you would get an error back.

Can you try putting a dtsrun.exe call in a job? Run the job and see if
it starts the DTS package. If not you have bigger problems.

If it works in the job, use msdb..sp_start_job to start the job from
the trigger.|||It seems that the issue might be with connecting to a Visual Fox Pro
table outside of the DTS program. The package which transfers from SQL
to VFP works in DTS but not in SQL Query Analyzer. I setup up another
dts package which transfers the same data but from SQL to a text file.
This package executes properly in DTS and SQL QA. It operates under
the same code as posted above, with changing the package name. The VFP
table are on the local machine though.

I also tried exec master..xp_cmdshell 'dtsrun /Sshannonpc /E256 /
Npackagename. This executes the package with the text file but not the
VFP tables. I get the error 'Invalid class string.' However, no
errors occur when runing the package manually in DTS. Ideally I would
like to import right into the VFP tables and not have another app that
reads from the text file then to the VFP tables. ?|||When you say "The VFP table are on the local machine", what do you
mean? On your local desktop machine, or on the database server?

Sometimes people get local/server paths mixed up. The path to the VFP
needs to be reachable via the server, not your local PC. Is this the
problem?|||Gary,

I am running SQL Sever 2000 on my local desktop machine and the Visual
FoxPro tables are located on the c:\ drive on this same computer.

Maybe you could answer this... In SQL Query Analyzer this command
**(exec master..xp_cmdshell 'dtsrun /SShannonPC /E256
/NMiTekTransfer')** to run the DTS Package gives me this error:
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147221005
(800401F3) Error String: Invalid class string

However if I run **(dtsrun /SShannonPC /E256 /NMiTekTransfer)** from
the command prompt it execute fine. The DTS package also executes
manually form the DTS window.
Any thoughts? Thanks for helping.

Brett

Tuesday, March 27, 2012

Executing exe delphi program with Agent

Executing exe delphi program with Agent

Executing exe delphi program with Agent

Monday, March 26, 2012

executing DTS Package from stored procedure

Monday, March 19, 2012

Execute windows on client machine

Sunday, February 26, 2012

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost after database migration with attach

Sunday, February 19, 2012

Execute Package Task says Login failed for 'sa'

Friday, February 17, 2012

Execute DTS Packages

EXECUTE AS question

Blog Archive

About Me