?
Solved

cannot use the master..xp_cmdshell

Posted on 2005-04-04
26
Medium Priority
?
2,449 Views
Last Modified: 2012-06-27
I am very new in SQL server, and thinking to copy a file from one location to another and also changing the name... please help

declare @FROMPATH as varchar (30)
declare @TOPATH as varchar (30)
declare @FULPATH as varchar (30)


SET @FROMPATH ='Copy "C:\OpenPO.txt"'
SET @TOPATH ='"C:\OpenPO2.txt"'
SET @FULPATH=@FROMPATH + @TOPATH
Exec master..xp_cmdshell @FULPATH

Thanx and this is urgent... it seems not sorking and showing the error message as below,

The filename, directory name, or volume label syntax is incorrect.

Whats wrong with it? please


Thanx in advance

Me, Yee
0
Comment
Question by:mingfattt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
  • 6
  • +1
26 Comments
 
LVL 1

Expert Comment

by:mike2312
ID: 13695616
SET @FROMPATH ='Copy "C:\OpenPO.txt"'
SET @TOPATH ='"C:\OpenPO2.txt"'
SET @FULPATH=@FROMPATH + @TOPATH
Exec master..xp_cmdshell @FULPATH


If this is how is is written exactly then it looks like to me the effective command will be

'Copy "C:\OpenPO.txt""C:\OpenPO2.txt"

put a space either at the front of the From path or the end of the to path.
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13695637
well, i still get the error, can you show me where is my mistake and anything wrong please... maybe if can try to correct my syntax above... thanx very much
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13695656
i also try it in this way

declare @File as varchar(255)

SELECT @File = 'COPY C:\Testing\OpenPO.txt+C:\OpenPO2.txt'
EXEC master..xp_cmdshell @File

and this give me the error, "The system cannot find the path specified". but the thing is there... What happened... urgent

Yee


0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 39

Expert Comment

by:appari
ID: 13695720
try like this

declare @File as varchar(255)

SELECT @File = 'COPY C:\Testing\OpenPO.txt C:\OpenPO2.txt'
EXEC master..xp_cmdshell @File
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13695725
same error

The system cannot find the path specified.


Plese help.....
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13695726
maybe any other issue that related to this?
0
 
LVL 39

Expert Comment

by:appari
ID: 13695771
is the path and file exists on server?

this command executes on server not on the client.
0
 
LVL 39

Expert Comment

by:appari
ID: 13695774

check if C:\Testing\OpenPO.txt  is existing on the server or not
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13695782
you mean? will this execute in the local machine, because i m just trying to run this at the SQL analyzer and try to copy the data from one destination to another... is this possible? i think this is nothing to do with server connection right'..
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13695854
xp_cmdshell is intended to execute commands ON THE SERVER.
You can NOT execute code on your local machine from Query Analyser
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13695870
Instead, you could use a .BAT file
to perform the file copy operations,
and then use the command-line version of Query Analyser (osql) to run SQL commands/scripts
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13695873
so is it any other way i mean copy the text file from local machine to somewhere in the server by using stored procedure beside xp_cmdshell? please advise..
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13695882
well... to be frank i m just get into this in one week time... and i am not very familiar with SQL server.. please advise further on.... all help is very appreciated

Thanx

Yee
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13695896
Stored procedures execute on the server too.
May I ask what you're trying to achieve ?

If you just need to load data from a file to a table, a reasonable option might be to use the command-line bulk copy utility aka BCP

If you give us more details, we'll be able to post more relevant suggestions
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13695908
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13695920
well... now i have a bundle of dat (either in .txt and also the .xls) which will download to the local machine in either daily or weekly (depends on the day of the week) to load the data inside the table i use the DTS as the help tools, however i need to copy the raw file from my local pc to the server before i make use of DTS... which this previously being done by the .NET which i found out that it is not stable... and pop up with a lot of problem... i really hope that with the full 'migrate' to the SQL server i can make the perfomance better... thanx very much
0
 
LVL 39

Assisted Solution

by:appari
appari earned 1000 total points
ID: 13695948
you can use shared folders on your network with cmdShell. you just share the folder where your raw data files exists and execute the command as in the following example

eg:
declare @File as varchar(255)

SELECT @File = 'COPY \\yourmcName\sharedfoldername\filename C:\tmp\copytofilename'
EXEC master..xp_cmdshell @File

0
 
LVL 39

Expert Comment

by:appari
ID: 13695957
in the example in my previous post "C:\tmp\copytofilename" is the path on your server, make sure the directory named tmp exists on the server.
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13696006
declare @File as varchar(255)

SELECT @File = 'COPY \\agmpimo173\Brio_Schedule\OpenPO.txt \\pimoserv\Raw File by WW\Current Week\openpo.txt'
EXEC master..xp_cmdshell @File

it can copy but still cannot make it inside my server which started with the \\pimoserv in the statement above... please advise.. i think i am closer to the success... thanx for the assist... please advise

0
 
LVL 26

Accepted Solution

by:
Hilaire earned 1000 total points
ID: 13696016
file names with spaces must be enclosed within doubles quotes
please try

SELECT @File = 'COPY \\agmpimo173\Brio_Schedule\OpenPO.txt "\\pimoserv\Raw File by WW\Current Week\openpo.txt"'
0
 
LVL 39

Expert Comment

by:appari
ID: 13696019
try like this

declare @File as varchar(255)

SELECT @File = 'COPY \\agmpimo173\Brio_Schedule\OpenPO.txt "\\pimoserv\Raw File by WW\Current Week\openpo.txt"'
EXEC master..xp_cmdshell @File


your server filename has spaces embedded inside the path/file name. in that case you need to enclose the path within double quotes. but if spaces exists in source file also then you will be in trouble again. you can not use more than one set of double quoes in XP_cmdShell sp.

see the following link from MSDN for details
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

copied from above link,
Arguments
'command_string'

Is the command string to execute at the operating-system command shell. command_string is varchar(8000) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.


0
 
LVL 2

Author Comment

by:mingfattt
ID: 13696025
thanx very much, you guys are great... if can i really want to make this the equal mark to you all... but sadly... i need to seperate it... you guys are really an expert... a long way to go with you guys... thanx

Cheers and thanx again..

Me, yee
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13696040
sorry appari,
I think you deserved al the credit on this Q. Didn't want to steal points, I was just trying to bring my two cents.

Regards
Hilaire
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13696171
you guys can i have one very last question... can i copy the file to more than one location.. maybe for the backup?
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13696182
well, both of you did greats... just wish that i can give equally 500 to both of you... but.... well to me the afford and the heart of help is more important... anyway i hope both of you wont mind about this.... thanx very much
0
 
LVL 39

Expert Comment

by:appari
ID: 13696299
Hilaire :
never mind:) it happens here may be next time i steal your points;)

mingfattt :
you can run the command one more time with a different parameter.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question