Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Posts
    33

    Unanswered: Execute xp_cmdShell failing?

    When I run the below command it is not working?

    Code:
    exec xp_cmdshell 'xcopy "\\10.20.1.40\Folder1" "\\10.20.1.41\Folder2" /D /I /S'
    After I execute the above i get:
    1 Invalid drive specification
    2 0 File(s) copied
    3 Null

    If I execute the same command via the command prompt in windows server 2008 it works fine.. Of course the single ' are not at the beginning and end when I run the command from the command prompt... I'm stuck.

  2. #2
    Join Date
    Sep 2011
    Posts
    31
    Check whether the service account has write privileges on the target folder.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First and foremost, xp_cmdshell is an abomination from a security standpoint. I would VERY STRONGLY recommend that you make getting rid of xp_cmdshell a high priority, near term task. Simply having xp_cmdshell enabled will cause your server to fail a HIPPA or JSOX audit, and in most cases will fail even a SOX audit.

    My first suggestion would be to build a SQL Agent Job to perform the copy. Once you get the job built and working, you can call sp_start_job to execute the job as needed.

    If security and compliance aren't issues that you have to worry about, can I have your job ??? The solution in this case is to execute:
    Code:
    EXECUTE xp_cmdshell 'whoami'
    to see what account is being used by xp_cmdshell. By default this will be the SQL Server Service Account, but that can be changed and you need to be sure you know what account is being used. Once you know which account is being used, check to be sure that it has read permissions on the source of your copy and write permissions for the destination.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2010
    Posts
    33
    I am open to ideas on how to accomplish synchronization of two folders on two different machines..

    I have read access to Server 1 Folder 1

    I have full access to Server 2 Folder 2


    Only way I am aware to do this is with xcopy.. I am open to better methods that may be more secure / stable.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Create a SQL Agent job to run a CmdExec task that consists of the xcopy. You should have better control of it from there, than through xp_cmdshell.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There are lots and lots and lots of ways to synchronize two folders on different machines. Most of them don't involve a database.

    How often does this need to happen? What kind of environment is this running in? Small business where you own everything, mega enterprise, other? Is there a business reason you are using SQL Server to do this as opposed to any other method? Are you doing this as part of a larger process? If so, what are the other moving parts?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2010
    Posts
    33
    I was able to get it going with CmdExec MCrowley..

    Teddy,

    I need to sync up every hour. There is no business reason for using the database to perform other than I know how to setup jobs on it. I am by now means a dba just familiar enough to get it going..

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by ptyo View Post
    I was able to get it going with CmdExec MCrowley..
    Very good!

    As Teddy pointed out, there are probably other ways to accomplish this copy operation, but the CmdExec one that you've got working now is "good enough" to get you by for now and should allow you to successfully pass most audits.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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