SQL job fails calling DTS using UNC path

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL job fails calling DTS using UNC path

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-07 : 08:23:45

DTS package uses UNC path to get access to an access file. If I run it manually works fine. But it fails if I use a SQL job to run it. Anyone know why? I'd reallly appreaciated if you help me solve this one.

The error msg is:

DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: Delete from Table [ProdRept].[dbo].[tblUM-RollsPerCase] Step

DTSRun OnFinish: Delete from Table [ProdRept].[dbo].[tblUM-RollsPerCase] Step

DTSRun OnStart: Copy Data from tblUM-RollsPerCase to [ProdRept].[dbo].[tblUM-RollsPerCase] Step

DTSRun OnError: Copy Data from tblUM-RollsPerCase to [ProdRept].[dbo].[tblUM-RollsPerCase] Step, Error = -2147467259 (80004005)

Error string: The Microsoft Jet database engine cannot open the file ''. 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: -67568648 (FBF8FBF8)

Error string: The Microsoft Jet database engine cannot open the file ''. 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: Copy Data from tblUM-RollsPerCase to [ProdRept].[dbo].[tblUM-RollsPerCase] Step

DTSRun: Package execution complete.

My conclusion is SQL Job can't take UNC paths. Please comfirm!

http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 09:30:25

Huh?
I think someone has the Access database in mind already opened when you try to access it.
Ask that person to close down the Access database and try again.

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-07 : 20:29:12

nobody has it open.
it gives an error because the file is in another server.

http://www.sqlserverstudy.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-07 : 23:25:08

Does sql agent service account have permission to open remote file?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources