Hi,
I want to schedule a package to import access database to sql2000 server
(sp3).
The pkg fail with permissions error... what do I neet to have in mine for
schedule pkg?
Do I need to install access in my sql2000 server?
The pkg is running ok manually.
Tks in advance
JFB

Here is the error detail...
Executed as user: SQLSERVER\SYSTEM. ...cuting... DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1,
Error = -2147467259 (80004005) Error string: The Microsoft Jet
database engine cannot open the file '\\server\Data\Data.mdb'. It is
already opened exclusively by another user, or you need permission to view
its data. Error source: Microsoft JET Database Engine Help file:
Help context: 5003051 Error Detail Records: Error: -2147467259
(80004005); Provider Error: -534709256 (E020FBF8) Error string: The
Microsoft Jet database engine cannot open the file '\\server\Data\Data.mdb'.
It is already opened exclusively by another user, or you need permission to
view its data. Error source: Microsoft JET Database Engine Help
file: Help context: 5003051 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.

"JFB" <jfb (AT) newSQL (DOT) com> wrote

Under what account is the job scheduled?

Have a look here

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

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"JFB" <jfb (AT) newSQL (DOT) com> wrote

I'm using sa account with secure pwd... other dts are in the same way
without any problem.
I already read this article.
Anything else to think about ???
Tks
JFB

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

It depends on what the specific permissions error is. If you
are positive it's nothing in the article then it could be
just the nature of some access issues. Does the service
account for SQL Server have full control on the folder where
the ldb file is created? Does it always fail? Is the Access
database opened by another user or users when you get the
errors?
Posting the exact error message when you run it manually
would help. If it's Access specific related to some of the
above then you should hit similar error messages when run
manually. It won't matter whether it's scheduled or not.

-Sue

On Mon, 8 Nov 2004 18:06:11 -0500, "JFB" <jfb (AT) newSQL (DOT) com>
wrote:

Tks for u reply...
1. The server have full control of the data folder.
2. Nothing in the article affects my problem.
3. The pkg doesn't fail manually, no errors.
4. Here is the error detail when it runs as a job schedule:
Executed as user: SQLSERVER\SYSTEM. ...cuting... DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1,
Error = -2147467259 (80004005) Error string: The Microsoft Jet
database engine cannot open the file '\\server\Data\Data.mdb'. It is
already opened exclusively by another user, or you need permission to view
its data. Error source: Microsoft JET Database Engine Help file:
Help context: 5003051 Error Detail Records: Error: -2147467259
(80004005); Provider Error: -534709256 (E020FBF8) Error string: The
Microsoft Jet database engine cannot open the file '\\server\Data\Data.mdb'.
It is already opened exclusively by another user, or you need permission to
view its data. Error source: Microsoft JET Database Engine Help
file: Help context: 5003051 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.

JFB

"Sue Hoegemeier" <Sue_H (AT) nomail (DOT) please> wrote

If it doesn't fail when run manually but fails when
scheduled as a job then something in the article is likely
related to your issue. The service account for SQL Server
needs to have the correct permissions and needs to be a
domain account. You can log on to SQL Server under this
account and run the package manually to see if you get any
errors.
You may also want to log on as under the service account and
execute xp_cmdshell to do a dir command on \\server\Data\
and see if you can get the directory listing or if you get
any errors.
You need to also make sure the database is not being opened
exclusively by another user or program. However, being that
this never happens when the package is run manually then
again, it's probably related to permissions or account
settings as described in the article.

-Sue

On Tue, 9 Nov 2004 09:33:17 -0500, "JFB" <jfb (AT) newSQL (DOT) com>
wrote:

Hi Sue,
You were right when I execute xp_cmdshell 'dir \\server\data\*.*' from
sqlagent I got a denied access message.
This is the first time than I'm using a folder out the sqlserver for
schedule job.
My servers are in just workgroup, not domain, not active directory.
How can I setup permission for sa account under \\server\data folder?
Tks in advance.
JFB

"Sue Hoegemeier" <Sue_H (AT) nomail (DOT) please> wrote

Okay well then it does apply to the article Allen
originally referred you to as it is a permissions issue for
the account that SQL Server is running under. Why do you
want to set permissions for sa? You may be misunderstanding
this on what the accounts are, what sa is - it's a SQL
account so it can't have full control to the folder on the
other server as you indicated. Not totally understanding all
the accounts and what they are, how they work can be
tripping you up a bit here. The job owner being sa just
determines the security context under which the jobs will
run - it doesn't mean that sa is the account that needs
permissions on the other computer. That may be where the
confusion is.
For a workgroup and accessing resources on the other
computers, the best you can try is a kluge with this.
Create local account on the computer where SQL Server is.
Run SQL Server and SQL Agent under that account.
Create the exact same account and password on the other
computer where the access database resides. Give the
appropriate permissions to this account for the folder where
the Access database resides.

-Sue

On Wed, 10 Nov 2004 10:15:47 -0500, "JFB" <jfb (AT) newSQL (DOT) com>
wrote: