xp_cmdshell

Question

Post reply

xp_cmdshell

Tara-1044200

SSCoach

Points: 15951
More actions
January 30, 2010 at 10:02 am

#156629

I am using to copy files from 1 folder to another. I get the result as copied but actually they are not copied, i dont see any files in the destination folder.
EXEC master..xp_cmdshell 'COPY \\OBBEVAER\IT_Reports\Database_Reports\*.mhtml
\\OBBEVAER\IT_Reports\DBA\'
Result:
\\OBBEVAER\IT_Reports\Database_Reports\Oct_Revenue_Report.mhtml
\\OBBEVAER\IT_Reports\Database_Reports\Oct_Memebers_Report.mhtml
2 file(s) copied.
NULL
Actually i want to copy all .mhtml files to destination folder and want to rename the existing file with date stamp. any help ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Sounds silly but did you hit "refresh" when you looked in the destination folder?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

ohh yeah, there is no file in there? any other way to do ?

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Without any errors, the files should be copied. Maybe a rights problem? What do you get if you execute the following statement:

EXEC master..xp_cmdshell 'DIR \\OBBEVAER\IT_Reports\DBA\'

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

i get this..

Volume in drive \\OBBEVAER\IT_Reports is OBBEVAER_Data
Volume Serial Number is B2BD-B330
NULL
Directory of \\OBBEVAER\IT_Reports\DBANULL
File Not Found
NULL

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Are there currently files in the destination folder that should have displayed with "DIR" ? Or is the folder empty ? If the folder is empty, put a test file in there for testing purposes.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Tara-1044200 (1/30/2010)
i get this..
Volume in drive \\OBBEVAER\IT_Reports is OBBEVAER_Data
Volume Serial Number is B2BD-B330
NULL
Directory of \\OBBEVAER\IT_Reports\DBANULL
File Not Found
NULL

That means that the login that the SQL Service is starting with cannot see that path/directory. SQL Server probably needs to boot up using a domain user.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 7

I agree that it could be a permission issue and you just don't see any errors. When I have issues such as this with copying filee using xp_cmdshell, I try logging on to the server in SSMS using the service account or whatever account is running the command that fails to copy and try running it. Usually I'll see an error message.