Post reply

EXEC xp_cmdshell error : the system cannot find the drive specified

  • October 14, 2011 at 4:01 am

    #245328

    I have a problem with one of my scripts. I need to put an error log file on a file share. Part of the script that does this is:

    DECLARE @ErrorLognvarchar(60)

    DECLARE @ErrorSQLvarchar(500)

    SET @ErrorLog = 'P:\ErrorLogging\Errorlog_DRE.log'

    SET @ErrorSQL= '"echo Time of error : '+convert(nvarchar(25),getdate())+' " >> '+ @ErrorLog

    EXEC xp_cmdshell @ErrorSQL

    when running this script I get the message "the system cannot find the drive specified"

    when I run this in a command line window it works fine.

    It's run on a sql 2005 server standard edition on a windows 2003 machine.

    The service SQL Server(MSSQLSERVER) is running on local system (not allowed to change this on this moment)

    The service SQL Server Agent is running on a domain administrator (changed it from local system to the domain admin user and restarted the service)

    Both in MS SQL studio management as via a sql server agent job I get the same error message.

    I also tried to put the log file on the local drive (this works fine) and then via a .bat file (on local c-drive) copy the file to the share. Also in this case I get the same message.

    I also already enabled the server proxy account (with the domain admin user account)

    I checked the BUILTIN\Aministrators and thAdministratorsysadmin server roles. The domain admins are also member of the local admin group.

    I tried to do this to different drives. Mounted LUN drives from the SAN and local drives are working fine. Only on shares it doesn't work.

    can anybody help my with this, it's getting a bit annoying.:w00t:

    Already many thanks for feedback,

    Bennie Pielaat

  • October 14, 2011 at 4:42 am

    #1394538

    It can only be a permission issue.

    xp_cmdshell uses the SQL Server Service Account.

    Ensure this account has write access to the drive your writing too.

  • October 14, 2011 at 5:09 am

    #1394557

    I was already afraid for this. I need to change then the user on the service while this is now the local system account. I will ask for a domain account for these services and change the user.

    Thanks,

    Bennie

  • October 14, 2011 at 9:16 am

    #1394707

    It may not be a permissions issue... You also have to remember that xp_CmdShell will NOT find "mapped drives" using drive letters... only physical drives. Instead of mapped drives, you need to use a UNC path (which includes the machine name) instead of just a driver letter.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply