Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Location
    sydney, australia
    Posts
    3

    Question Unanswered: Error opening datafile: Access is denied.

    Hi all!
    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: Loading...
    DTSRun: Executing...
    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

    Please help!

    Regards
    Uday
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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.
    MCDBA

  3. #3
    Join Date
    Jul 2002
    Location
    sydney, australia
    Posts
    3
    Hi there, thanks for the response!

    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.

    Regards
    Uday

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    The "SET" command is just that "SET", the output will be something like this:

    ALLUSERSPROFILE=C:\Documents and Settings\All Users
    APPDATA=C:\Documents and Settings\Administrator\Application Data
    CommonProgramFiles=C:\Program Files\Common Files
    COMPUTERNAME=MYPC
    ComSpec=C:\WINNT\system32\cmd.exe
    HOMEDRIVE=C:
    HOMEPATH=\
    NUMBER_OF_PROCESSORS=1
    OS=Windows_NT
    Os2LibPath=C:\WINNT\system32\os2\dll;
    Path=C:\Perl\bin\;C:\WINNT\system32;C:\WINNT;C:\WI NNT\System32\Wbem
    PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WS F;.WSH
    PROCESSOR_ARCHITECTURE=x86
    PROCESSOR_IDENTIFIER=x86 Family 6 Model 3 Stepping 4, GenuineIntel
    PROCESSOR_LEVEL=6
    PROCESSOR_REVISION=0304
    ProgramFiles=C:\Program Files
    PROMPT=$P$G
    SystemDrive=C:
    SystemRoot=C:\WINNT
    TEMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp
    TMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp
    USERDOMAIN=@HOME
    USERNAME=Administrator
    USERPROFILE=C:\Documents and Settings\Administrator
    windir=C:\WINNT

    So from this we can see that I'm running as Administrator. You should also add "NET USE" into the command file to see your mappings. So add these 2 lines into your batch file:

    SET
    NET USE
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •