Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

getting error "The system cannot find the path specified." using xp_cmdshell Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2008 3:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 11, 2008 5:11 AM
Points: 2, Visits: 3
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
Post #445796
Posted Tuesday, January 22, 2008 3:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #445804
Posted Wednesday, January 23, 2008 1:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 11, 2008 5:11 AM
Points: 2, Visits: 3
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.
Post #446278
Posted Wednesday, January 23, 2008 12:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:12 AM
Points: 1,166, Visits: 1,973
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.

Post #446583
Posted Thursday, February 7, 2008 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2008 5:14 AM
Points: 1, Visits: 1
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 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
-----------------------
Post #452659
Posted Thursday, February 7, 2008 8:01 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:06 PM
Points: 398, Visits: 2,428
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

Post #453047
Posted Thursday, February 7, 2008 9:04 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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.



Post #453054
Posted Friday, December 28, 2012 2:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 28, 2012 2:06 PM
Points: 1, Visits: 19
i too had the same issue in Production servers even with proper folder acess rights.
adding double quotes to path worked fine
Post #1401056
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse