Unanswered: Error opening datafile: Access is denied.
I am using SQL Server 7.0 Ent Edition. Defined a DTS Local Pkg which basically picks up a whole heap of txt files on a unix m/c and loads them into sql tables. I have a mapped drive to the unix box and yes MSSQLServer a/c has access to this mapped drive.
The task runs fine when I execute it, but when I try to run it at scheduled intervals via NT Scheduler, I get this message.
E:\ProgramFiles\MSSQL7\Binn>DTSRun /S SYD1SHIVA /E /N Test Package
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217887 (80040E21)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
You say that MSSQLServer a/c has access, I guessing that a/c is the Agent Service for SQL. But you are running the job through NT Scheduler as who? In your batch file enter the "SET" command at the top and make sure the output is directed to a file. This will show you the USERNAME and USERDOMAIN environment variables of who you are running as.
Also in the batch file do a NET USE to see if the mapping exists and if so add a DIR on the drive. Make sure this is all added to the batch and run through the NT Scheduler like you want to run, piping the output to a log file.
I am using seperate domain a/cs for the MSSqlServer Service and SQLAGENT.
Running the job thro' NT scheduler logged in as myself (domain administrator). However, I have granted permissions for the SQLserver and SQLAgent domain a/cs execute perms on this file.
Can you give me an example for the "SET" command that you are advising me to stick in my batch file. I am running a seperate batch file to just do the drive mappings (NET USE) in case they've dissappeared or something like that.
Yes, I have o/p of my main batch file going to a text file already and am curious to try out your "SET command" to see the difference in o/p.