getting error “The system cannot find the path specified.” using xp_cmdshell

  • asif_ku

    Old Hand

    Points: 324

    Hi,

    I am getting error “The system cannot find the path specified.” using xp_cmdshell, tyring to copy file from one folder to another. Following is code i execute:

    DECLARE @fn char(6),

    @cmd varchar(100)

    SET @fn=REPLACE(convert(char(8),getdate(),1),’/’,”)

    SET @cmd =’Move C:\Shippment_Tracking\ShippmentTracking.txt C:\Shippment_Tracking\Archive\’+@fn+’.txt’

    EXEC master..xp_cmdshell @cmd

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21921

    A simple question: does this folder actually exist on the server on which SQL Server is running? The xp_cmdshell is executed in the security context of the SQL Server service. Does this have permissions to the folder? So does a simple dir work:

    EXEC master..xp_cmdshell ‘dir C:\Shippment_Tracking\’

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • asif_ku

    Old Hand

    Points: 324

    First of all thanks for quick respond!

    Yes, that folder exisit on same machine where Sql server is running.

    I am not sure how to give permission to that folder? Can u plz let me know the steps to do so?

    Thanks.

  • Adam Bean

    One Orange Chip

    Points: 26440

    asif_ku (1/23/2008)


    First of all thanks for quick respond!

    Yes, that folder exisit on same machine where Sql server is running.

    I am not sure how to give permission to that folder? Can u plz let me know the steps to do so?

    Thanks.

    xp_cmdshell will use the account which is running your SQL server.

    To figure out the account running SQL:

    – Start | Run | services.msc

    SQL2000

    – MSSQLSERVER(INSTANCENAME)

    SQL2005

    – SQL Server(INSTANCENAME)

    – Double click the service

    – Click Log On

    – Note the account running the service

    Now that you know the account, check your folder permissions

    – Right click the folder in question

    – Go to Properties / Security

    – Ensure the account in question has the appropriate permissions (read/write from the sounds of it)

    ---
    SQLSlayer
    Making SQL do what we want it to do.

  • get-12

    Valued Member

    Points: 65

    analogue xp_cmdshell

    ———————–

    MSSQL2000 :

    nothing

    MSSQL2005 :

    sp_configure ‘show advanced options’, 1;

    GO

    RECONFIGURE;

    GO

    sp_configure ‘Ole Automation Procedures’, 1;

    GO

    RECONFIGURE;

    GO

    ———————–

    declare @iShl int, @iStm int, @iStdOut int, @hr int, @EndOfStream int,

    @strText nvarchar(4000), @cmd nvarchar(4000)

    set @cmd = ‘Exec(“cmd /c ping -n 3 -w 1000 http://www.ru“)’

    EXEC @hr = sp_OACreate ‘Wscript.Shell’, @iShl OUT

    EXEC @hr = sp_OAMethod @iShl, @cmd, @iStm out

    EXEC @hr = sp_OAGetProperty @iStm, ‘StdOut’, @iStdOut out

    EXEC @hr = sp_OAGetProperty @iStdOut, ‘AtEndOfStream’, @EndOfStream out

    WHILE @EndOfStream = 0 BEGIN

    EXEC @hr = sp_OAGetProperty @iStdOut, ‘ReadLine’, @strText out

    print @strText

    EXEC @hr = sp_OAGetProperty @iStdOut, ‘AtEndOfStream’, @EndOfStream out

    END

    EXEC @hr = sp_OADestroy @iShl

    ———————–

    :hehe:

  • Lester Policarpio

    SSCertifiable

    Points: 7109

    Andras Belokosztolszki (1/22/2008)


    A simple question: does this folder actually exist on the server on which SQL Server is running? The xp_cmdshell is executed in the security context of the SQL Server service. Does this have permissions to the folder? So does a simple dir work:

    EXEC master..xp_cmdshell 'dir C:\Shippment_Tracking\'

    Regards,

    Andras

    Also check for this :

    EXEC master..xp_cmdshell ‘dir C:\Shippment_Tracking\Archive\’

    I had almost the same situation as you are ( for me i used copy instead of move) i solved my problem by letting the value of the variable in a single line…

    I also tested your query i just replaced the path and it works for me..

    "-=Still Learning=-"

    Lester Policarpio

  • Ian Yates

    SSCoach

    Points: 19678

    Also, just to future proof your code, enclose the filenames and paths in double quotes as in

    “c:\my folder\my file.txt” because without them you have 4 parameters to move rather than the expected 2.

  • rajeshpandyan32

    SSC Journeyman

    Points: 95

    i too had the same issue in Production servers even with proper folder acess rights.

    adding double quotes to path worked fine

  • Keistad

    Ten Centuries

    Points: 1006

    Adding double quotes saved my hours:)

  • mpepe 21547

    Old Hand

    Points: 369

    I am having a similar issue:

    EXEC master..xp_cmdshell

    “dir \\fserver1\NextgenRoot\Prod\EMR\Images\ImageSaved\84C06D96-D350-47ED-B26D-BF1A794D93A6.pdf”

    Results:

    Volume in drive \\fserver1\NextgenRoot is Data

    Volume Serial Number is 3CEC-F907

    NULL

    Directory of \\fserver1\NextgenRoot\Prod\EMR\Images\ImageSaved

    NULL

    09/28/2014 01:29 PM 413,455 84C06D96-D350-47ED-B26D-BF1A794D93A6.pdf

    1 File(s) 413,455 bytes

    0 Dir(s) 15,483,371,520 bytes free

    NULL

    But when I try to move that file:

    EXEC master..xp_cmdshell

    ‘MOVE “\\fserver1\NextgenRoot\Prod\EMR\Images\ImageSaved\C06D96-D350-47ED-B26D-BF1A794D93A6.pdf” “\\fserver1\NextgenRoot\IHS_DocsImagesToBeRemoved\C06D96-D350-47ED-B26D-BF1A794D93A6.pdf”‘

    Results are:

    The system cannot find the file specified.

    Clearly the file is there but the move command cannot find it. What am I missing?

  • Eirikur Eiriksson

    SSC Guru

    Points: 182292

    mpepe 21547 (1/27/2016)


    I am having a similar issue:

    EXEC master..xp_cmdshell

    "dir \\fserver1\NextgenRoot\Prod\EMR\Images\ImageSaved\84C06D96-D350-47ED-B26D-BF1A794D93A6.pdf"

    Results:

    Volume in drive \\fserver1\NextgenRoot is Data

    Volume Serial Number is 3CEC-F907

    NULL

    Directory of \\fserver1\NextgenRoot\Prod\EMR\Images\ImageSaved

    NULL

    09/28/2014 01:29 PM 413,455 84C06D96-D350-47ED-B26D-BF1A794D93A6.pdf

    1 File(s) 413,455 bytes

    0 Dir(s) 15,483,371,520 bytes free

    NULL

    But when I try to move that file:

    EXEC master..xp_cmdshell

    'MOVE "\\fserver1\NextgenRoot\Prod\EMR\Images\ImageSaved\C06D96-D350-47ED-B26D-BF1A794D93A6.pdf" "\\fserver1\NextgenRoot\IHS_DocsImagesToBeRemoved\C06D96-D350-47ED-B26D-BF1A794D93A6.pdf"'

    Results are:

    The system cannot find the file specified.

    Clearly the file is there but the move command cannot find it. What am I missing?

    Add permission for the sql server service account to read/write/delete in the source folder.

    😎

  • mpepe 21547

    Old Hand

    Points: 369

    Thank you… that did it! I thought the sys admin had already configured that for me, but it was not done.

    😀 😀 😀 😀

Viewing 12 posts - 1 through 12 (of 12 total)

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