Package fails when it is scheduled

Hi
I'm using SQL Server 2000. I've created a local package which runs
fine when i execute it manually from Enterprise Manager, but
constantly fails when I schedule it. Can anybody suggest why this
might be?
Thanks
Colin
Most probably, its the permissions given to the userid that is used to
call the job.
either case, What does the history log say? (right click on the job
and select "view history")
On Mar 6, 5:52Xpm, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi
> I'm using SQL Server 2000. I've created a local package which runs
> fine when i execute it manually from Enterprise Manager, but
> constantly fails when I schedule it. Can anybody suggest why this
> might be?
> Thanks
> Colin
|||On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> Most probably, its the permissions given to the userid that is used to
> call the job.
> either case, What does the history log say? (right click on the job
> and select "view history")
>
The history log says the following:
Executed as user: SQLSRV01\SYSTEM. ...Start:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
Error Detail Records: Error: -2147467259 (80004005); Provider
Error: 444 (1BC) Error string: The driver returned invalid (or
failed to return) SQL_DRIVER_ODBC_VER: 2.00 Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: Cannot find all files in data path Error
source: Microsoft OLE DB Provider for ODBC Drivers Help
file: ... Process Exit Code 2. The step failed.
The package has just three steps. Step 1 is an execute SQL task which
deletes from two tables. Steps 2 & 3 connect to our accounts package
(Sage) using ODBC and uses two Transform data tasks to copy data from
Sage tables to the two SQL server tables. I am connecting to SQL sever
using the Microsoft OLE DB provider. I know that Step 1 is working
because after the package fails the two SQL tables are empty.
The package works fine if I
|||On 8 Mar, 09:44, Bobby <bob...@.blueyonder.co.uk> wrote:
> On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
>
> The history log says the following:
> Executed as user: SQLSRV01\SYSTEM. ...Start:
> DTSStep_DTSExecuteSQLTask_1 X DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 X DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 X DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 X DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) X X XError
> string: XThe driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 X X XError source: XMicrosoft OLE DB
> Provider for ODBC Drivers X X XHelp file: X X X XHelp context: X0
> Error Detail Records: X X XError: X-2147467259 (80004005); Provider
> Error: X444 (1BC) X X XError string: XThe driver returned invalid (or
> failed to return) SQL_DRIVER_ODBC_VER: 2.00 X X XError source:
> Microsoft OLE DB Provider for ODBC Drivers X X XHelp file: X X X XHelp
> context: X0 X X X X Error: X-2147467259 (80004005); Provider Error: X0
> (0) X X XError string: XCannot find all files in data path X XXError
> source: XMicrosoft OLE DB Provider for ODBC Drivers X X XHelp
> file: X X ... XProcess Exit Code 2. XThe step failed.
> The package has just three steps. Step 1 is an execute SQL task which
> deletes from two tables. Steps 2 & 3 connect to our accounts package
> (Sage) using ODBC and Xuses two Transform data tasks to copy data from
> Sage tables to the two SQL server tables. I am connecting to SQL sever
> using the Microsoft OLE DB provider. I know that Step 1 is working
> because after the package fails the two SQL tables are empty.
> The package works fine if I
.....right click on it and select Execute package. It only fails when
it is scheduled. Sorry, I got carried away when typing and hit the
wrong key!
Thanks for any help,
Colin
sql

Package fails when it is scheduled

Hi
I'm using SQL Server 2000. I've created a local package which runs
fine when i execute it manually from Enterprise Manager, but
constantly fails when I schedule it. Can anybody suggest why this
might be?
Thanks
ColinMost probably, its the permissions given to the userid that is used to
call the job.
either case, What does the history log say? (right click on the job
and select "view history")
On Mar 6, 5:52=A0pm, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi
> I'm using SQL Server 2000. I've created a local package which runs
> fine when i execute it manually from Enterprise Manager, but
> constantly fails when I schedule it. Can anybody suggest why this
> might be?
> Thanks
> Colin|||On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> Most probably, its the permissions given to the userid that is used to
> call the job.
> either case, What does the history log say? (right click on the job
> and select "view history")
>
The history log says the following:
Executed as user: SQLSRV01\SYSTEM. ...Start:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
Error Detail Records: Error: -2147467259 (80004005); Provider
Error: 444 (1BC) Error string: The driver returned invalid (or
failed to return) SQL_DRIVER_ODBC_VER: 2.00 Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: Cannot find all files in data path Error
source: Microsoft OLE DB Provider for ODBC Drivers Help
file: ... Process Exit Code 2. The step failed.
The package has just three steps. Step 1 is an execute SQL task which
deletes from two tables. Steps 2 & 3 connect to our accounts package
(Sage) using ODBC and uses two Transform data tasks to copy data from
Sage tables to the two SQL server tables. I am connecting to SQL sever
using the Microsoft OLE DB provider. I know that Step 1 is working
because after the package fails the two SQL tables are empty.
The package works fine if I|||On 8 Mar, 09:44, Bobby <bob...@.blueyonder.co.uk> wrote:
> On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> > Most probably, its the permissions given to the userid that is used to
> > call the job.
> > either case, What does the history log say? (right click on the job
> > and select "view history")
> The history log says the following:
> Executed as user: SQLSRV01\SYSTEM. ...Start:
> DTSStep_DTSExecuteSQLTask_1 =A0 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 =A0 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 =A0 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 =A0 DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error =3D -2147467259 (80004005) =A0 =A0 =A0Err=or
> string: =A0The driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 =A0 =A0 =A0Error source: =A0Microsoft OLE DB
> Provider for ODBC Drivers =A0 =A0 =A0Help file: =A0 =A0 =A0 =A0Help contex=t: =A00
> Error Detail Records: =A0 =A0 =A0Error: =A0-2147467259 (80004005); Provide=r
> Error: =A0444 (1BC) =A0 =A0 =A0Error string: =A0The driver returned invali=d (or
> failed to return) SQL_DRIVER_ODBC_VER: 2.00 =A0 =A0 =A0Error source:
> Microsoft OLE DB Provider for ODBC Drivers =A0 =A0 =A0Help file: =A0 =A0 ==A0 =A0Help
> context: =A00 =A0 =A0 =A0 =A0 Error: =A0-2147467259 (80004005); Provider E=rror: =A00
> (0) =A0 =A0 =A0Error string: =A0Cannot find all files in data path =A0 =A0= =A0Error
> source: =A0Microsoft OLE DB Provider for ODBC Drivers =A0 =A0 =A0Help
> file: =A0 =A0 ... =A0Process Exit Code 2. =A0The step failed.
> The package has just three steps. Step 1 is an execute SQL task which
> deletes from two tables. Steps 2 & 3 connect to our accounts package
> (Sage) using ODBC and =A0uses two Transform data tasks to copy data from
> Sage tables to the two SQL server tables. I am connecting to SQL sever
> using the Microsoft OLE DB provider. I know that Step 1 is working
> because after the package fails the two SQL tables are empty.
> The package works fine if I
=2E....right click on it and select Execute package. It only fails when
it is scheduled. Sorry, I got carried away when typing and hit the
wrong key!
Thanks for any help,
Colin

Package execution failed but no error (SQL2005)

I made an Integration Services Package which runs well if I start it manually
(also from SQL server Management Studio).
Now I have created a new job for the SQL server agent which need to run my
package.
After starting the job, it ends after a few seconds:
"The package execution failed" is the only message there is.
I assume it has something to do with security but for the test I am logged
in as a domain administrator which has all rights to all databases.
Can someone give some clues about how to solve this?
Thanks
Two possibilities come to my mind:
1- SQL Server run under its own account; so you should check the permission
for this account on both Windows and SQL-Server side (for example, does this
account has an explicit access to the database?)
2- You are using things like a network drive (Z:\Repertory\...) instead of
an UNC file name (\\Server\Repertory\...) which are specific to the login
account and unknown to other accounts.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Frans" <Frans@.discussions.microsoft.com> wrote in message
news:6EBD9795-92BA-435C-BCC7-CFEBDC6EAF2F@.microsoft.com...
>I made an Integration Services Package which runs well if I start it
>manually
> (also from SQL server Management Studio).
> Now I have created a new job for the SQL server agent which need to run my
> package.
> After starting the job, it ends after a few seconds:
> "The package execution failed" is the only message there is.
> I assume it has something to do with security but for the test I am logged
> in as a domain administrator which has all rights to all databases.
> Can someone give some clues about how to solve this?
> Thanks

Package execution failed but no error (SQL2005)

I made an Integration Services Package which runs well if I start it manuall
y
(also from SQL server Management Studio).
Now I have created a new job for the SQL server agent which need to run my
package.
After starting the job, it ends after a few seconds:
"The package execution failed" is the only message there is.
I assume it has something to do with security but for the test I am logged
in as a domain administrator which has all rights to all databases.
Can someone give some clues about how to solve this?
ThanksTwo possibilities come to my mind:
1- SQL Server run under its own account; so you should check the permission
for this account on both Windows and SQL-Server side (for example, does this
account has an explicit access to the database?)
2- You are using things like a network drive (Z:\Repertory\...) instead of
an UNC file name (\\Server\Repertory\...) which are specific to the login
account and unknown to other accounts.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Frans" <Frans@.discussions.microsoft.com> wrote in message
news:6EBD9795-92BA-435C-BCC7-CFEBDC6EAF2F@.microsoft.com...
>I made an Integration Services Package which runs well if I start it
>manually
> (also from SQL server Management Studio).
> Now I have created a new job for the SQL server agent which need to run my
> package.
> After starting the job, it ends after a few seconds:
> "The package execution failed" is the only message there is.
> I assume it has something to do with security but for the test I am logged
> in as a domain administrator which has all rights to all databases.
> Can someone give some clues about how to solve this?
> Thanks

pack will NOT schedule......

anyone ever encountered this...
i have a package which will execute fine manually but when i try to schedule it, it fails immediately (within the first second).
i can even right click on the scheduled package and do "generate sql script" and then run that script and again, no problem. it just will NOT schedule.
any ideas?
Package? Are you using Oracle or SQL Server?
|||

Depends on what your package is doing. Are you accessing external files? If so, you need to realize who your logged in user is for the job. When you run them manually, they execute as the runner.

Nick

|||

When you execute the package it is running on your permissions but schedule is running on SQL Server Agent permissions. If your SQL Server Agent is installed with local systems account as most developer installations are the schedule fails because SQL Server Agent does not have to permissions to run the package. try the links below to configure SQL Server Agent permissions. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

owner of maintenance jobs-permission hole?

Hello,
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jjjj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>

owner of maintenance jobs-permission hole?

Hello,
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jj
jj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>