November 5, 2004 at 9:35 am
We are using SQL 7.0 and I have created some DTS Repository Packages that basically take a result set and write them to an Excell file that is then emailed to the appropriate people. When I execute the packages themselves everything runs great and the world is good. When I create jobs to run them once a day however they always fail. Here is the error and message in the job history. What am I doing wrong?
DTSRun: Executing... DTSRun OnStart: Drop table Results Step DTSRun OnError: Drop table Results Step, Error = -2147217911 (80040E09) Error string: Cannot modify the design of table 'Results'. It is in a read-only database. Error source: Microsoft JET Database Engine Help file: Help context: 5003027 Error Detail Records: Error: -2147217911 (80040E09); Provider Error: -538642193 (DFE4F8EF) Error string: Cannot modify the design of table 'Results'. It is in a read-only database. Error source: Microsoft JET Database Engine Help file: Help context: 5003027 DTSRun OnFinish: Drop table Results Step DTSRun OnStart: Create Table Results Step DTSRun OnError: Create Table Results Step, Error = -2147217911 (80040E09) Error string: Cannot modify the design of table 'Results'. It is in a read-only database. Error source: Microsoft JET Database Engine Help file: Help c... Process Exit Code 2. The step failed.
November 5, 2004 at 11:19 am
When you run the DTS job manually, it uses the security context of the current connection in Enterprise Manager.
However, when the job is scheduled, the security is different. It will use the security of the user set up to run the sqlagent service. Go into the services of the SQL Server Agent, and check to see what it's set to. The user set in the service will need proper permissions to write to the location of the Excel file. If the location is a network drive, then the user in the service will need to be set as a Network user with proper file permissions. If it's writing to the local drive on the SQL server, the user could be a local user with proper permissions.
November 5, 2004 at 12:57 pm
Right now the agent is set up to use SQL Server authentication. Should it be set up for NT authentication instead or do we just need to make sure the SQL ID also has proper NT rights?
November 9, 2004 at 11:38 am
You'll need to start the agent using a windows login to do anything outside of SQL Server, even if it's on a local drive. SQL Server logins have no meaning outside of SQL Server.
Greg
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply