SQL Server 7.0 SP4

Hello:

I guess most of are aware that ownership between creating
and executing a DTS package as opposed to scheduling the
same, are different especially when you create the DTS
package on a SQL Server that's not local.

I have a server to which I don't have physical or even
remote access to (PC Anywhere, VNC, etc). I've created a
DTS package on this server via my Enterprise Manager. I've
also been able to run this package successfully. The owner
of this package is picked up from my machine
(localmachinename\username)

When I schedule this package, I see that the owner is 'sa'
causing the job to fail.

Without actually being on the server either physically or
remotely, I was wondering if there's a way to have this
package scheduled to run as a job.

Your responses and suggestions are highly appreciated.
Thanks.

The job is not failing because of the owner of the package or at least that
is very unlikely. If the DTS pkg is failing as a job then it more than
likely has to do with this

http://support.microsoft.com/?kbid=269074

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Rob" <anonymous (AT) discussions (DOT) microsoft.com> wrote

According to this KB article, my point exactly. I guess
I'll need to re-register to this machine as 'sa' and then
recreate this package and then try scheduling it. That
should do it, right? Thanks.

No

The job owner determines who runs the package and what is used within for
the connections.

if the owner is in the Sysadmin role then the SQL Server Agent account will
be used else the Proxy account. It is their repective credentials which are
being used. The owner of the package makes little to no difference. If you
register yourself on this server as a member of the Sysadmin role and YOU
own the job then the SQL Server Agent Account will be used as the invoker.

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Rob" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Hey Allan... thanks for your response again.

Let me try and paint the entire picture just so that there
are no gray areas.

The account I've used to register to the SQL Server is
part of the SYSADMIN role.

In the DTS package's SQL Server connection, I authenticate
to the server using the 'sa' account.

When this pkg is saved, the owner is shown as
<localmachine\myuserID>. Executing this pkg. from the DTS
console runs successfully.

When I schd. this pkg. as a job, I ensured that the owner
is 'sa'

When I try execute this job, it fails with the following
error message:

================================================== =========
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -
2147217887 (80040E21)
Error string: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No
work was done.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records:
Error: -2147217887 (80040E21); Provider Error: 0 (0)
Error string: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No
work was done.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
================================================== =========

Thanks again for all your help.

so the package will be executing under the guise of the SQL Server Agent
service account (sa = sysadmin = executed as Service Account)

What happens in this step?

DTSStep_DTSDataPumpTask_1,

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Rob" <anonymous (AT) discussions (DOT) microsoft.com> wrote

All it does is copy all rows from a table into the same
table, on the same server.

Essentially, there are two similar SQL Server connections,
both being authenticated using the 'sa' account. And
there's a 'Transform Data Task' which connects these two
connections and copies all the rows from table A back into
table A.

Thanks Allan.

I would expect to see the dreaded 80040005 error if this was a permissions
error.
is it possible for you to log into your PC as the Server's SQL Server Agent
service account (You said you couldn't log onto the server directly)
Now execute the package manually

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Rob" <anonymous (AT) discussions (DOT) microsoft.com> wrote