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.
| Author |
Topic |
|
a.g.mohod
Starting Member
6 Posts |
 Posted - 2006-10-10 : 08:29:15
|
| Hi,I am creating a simple DTS package on SQL server installed on my local machin and with sa user which fetches data from excel file on a computer in my LAN (a drive is mapped) and save to a textfile. I have full rights of the folder on that computer. Now when I right click the DTS package and execute it, it gets execute with no problem with proper output file.But my problem begins when I try to shedule the job for running this DTS. The sheduled job fails. In job history the following erro is shown though the file is not opened by any one:Executed as user: MBIHBZ80\SYSTEM. ...ecuting... DTSRun OnStart: Copy Data from Sheet1$ to J:\DT2XLS.txt Step DTSRun OnError: Copy Data from Sheet1$ to J:\DT2XLS.txt Step, Error = -2147467259 (80004005) Error string: The Microsoft Jet database engine cannot open the file 'J:\DTS_Test.xls'. 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 'J:\DTS_Test.xls'. 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 Sheet1$ to J:\DT2XLS.txt Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.Can any one help on this? Any suggestions will be greatly helpfull.Amit MohodClover Infotech Pvt Ltd.Mumbai |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-10-10 : 08:32:22
|
| Yes, it's a permissions issue normally. First of all you need to be running sql server agent service under a windows account that has permission to access the file you want to use, and second if it's on a remote device then you should use a UNC path ( //servername/pathname/ ) rather than a mapped drive.-------Moo. :) |
 |
|
|
a.g.mohod
Starting Member
6 Posts |
Posted - 2006-10-10 : 08:43:08
|
| Thanks for reply.But why then it runs when I run it by right clicking the DTS and selecting "Execute Package". I mean the same thing must happen in both situation.Amit MohodClover Infotech Pvt Ltd.Mumbai |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-10-10 : 08:55:43
|
| No, the same thing doesn't happen. When you right click it, it runs as you. You have permission, that's why it works. -------Moo. :) |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-10-11 : 23:31:37
|
| mr_mist,How do you make sql server run under another account in a temporary basis?thanks |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-12 : 03:08:13
|
| You are sure the xls isn't open by some other user? Ref: ...is already opened exclusively by another user, or you need permission to view its data. You don't need to change the credentials for the entire sql-server, all you need to do is to specify a different owner in the job that runs the dts. You can specify this in the "General"-part of the job setup...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-10-12 : 04:54:07
|
quote:
Originally posted by heze
mr_mist,How do you make sql server run under another account in a temporary basis?thanks
Go into control panel - admin tools - services and change the account that runs the sql server agent service for your instance. Restart agent.-------Moo. :) |
|
|
|
|
|
|
|
|
DTS Error