I have a stored procedure that executes xp_cmdshell and calls DTSRun.
Up until recently this worked fine. However, something has happened and
I can no longer use the stored procedure to start the package.

If I copy the line that is passed to xp_cmdshell and paste it into the
cmd window then the package runs. I am also able to run the package
from within Enterprise Manager. However, when I run the package through
Query Analyzer nothing happens. It does actually get into the package
and does some work inside, but it will not actually run the entire
package (which calls 2 other packages).

When running through QA I get:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSActiveScriptTask_2
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_2
DTSRun OnStart: DTSStep_DTSActiveScriptTask_4
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_4
DTSRun OnStart: DTSStep_DTSActiveScriptTask_3
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_3
DTSRun: Package execution complete.
NULL

but the data that should be placed in my database is not there, only
when I run the package through the command line or EM.

This was working at some point in the past but just stopped working. I
have looked through the posts and I am unable to find any information
to help me. The closest I can come to is a security problem but I do
not understand enough about SQL Server security to know what people are
talking about.

Any help would be greatly appreciated.

Thank you!

Lookup xp_cmdshell in Books Online. What you need to understand is the
security context used to execute the package, which is the windows account
used for xp_cmdshell. This will be the same account as used for the SQL
Server service, see EM or Services in Control Panel.

Does that user have rights to do what it needs?

--
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com


"khoy (AT) dmt (DOT) ca" wrote: