getting error "The system cannot find the path specified." using xp_cmdshell
-
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
-
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 -
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.
-
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)
-
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:
-
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
-
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.
-
i too had the same issue in Production servers even with proper folder acess rights.
adding double quotes to path worked fine
-
Adding double quotes saved my hours:)
-
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?
-
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.
-
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