July 8, 2002 at 5:11 pm
When trying to execute "xcopy" using the xp_cmdshell in query analyser, the output states that it runs, yet it doesn't. Microsoft state that this is a known bug (and to use "copy"), but someone else suggested using a handle with the command line statement. How does this work?
This isn't the only issue I have with xp_cmdshell - I also cannot run VB applications using this option (trying to schedule a DTS job to run). Any ideas? Or am I chasing a lost cause?
Thanks in advance
July 8, 2002 at 5:20 pm
Can you post the code you are having issues with?
Another alternative is to use an ActiveX script in DTS to move or copy your files.
Steve Jones
July 8, 2002 at 5:34 pm
Steve,
the line of code looks something like this:
xp_cmdshell 'xcopy d:\temp\*.* e:\temp\*.*'
This line is actually contained within a batch file - when I execute the batch file, everything inside it runs ok, except for this line - therefore I'm trouble-shooting it through query analyser. The output states "Command completed successfully", when it didn't.
If I use a copy statement, it's a-ok
cheers
Brendon
July 8, 2002 at 6:16 pm
I made a batchfile containing this:
xcopy C:\test1\*.* c:\test2\*.*
And then ran this:
xp_cmdshell 'c:\testxcopy.bat'
Which worked without error, copying the file and giving this output:
NULL
C:\WINNT\system32>xcopy C:\test1\*.* c:\test2\*.*
C:\test1\test1.sql
1 File(s) copied
NULL
Andy
July 9, 2002 at 8:53 am
One difference between xcopy and copy is that copy is an internal command. It's built into the command interpreter, so as long as you have a correct path to command.com you'll be able to execute it. XCOPY is a seperate executable... You might want to try and path directly to the executable when you make your call within your batch file, for example:
%WINDIR%\SYSTEM32\XCOPY.EXE d:\temp\*.* e:\temp\*.*
You might want to also check to make sure someone didn't create a fake batch file on your system called xcopy.bat or xcopy.cmd... you might be inadvertantly calling the wrong program.
July 9, 2002 at 7:48 pm
Checked the file - looks fine. I've tried referencing it as well - still no joy. System is SQL v7.0 and NT 4.0 (should have mentioned earlier).
One thing I did notice is that if you execute the same command on the command line, NT asks if the destination is a file or a folder - this makes me think this is why there is a problem. Checked the parameters for the command, but there didn't seem to be one for "no prompting".
I've changed my script to utilise a "copy" instead - (not sure why xcopy was used in the first place). Thanks anyway.
July 9, 2002 at 9:07 pm
The advantage xcopy had over copy in the DOS days was copy copies one file at a time... regardless of how large they are. The xcopy command was a bit more optimized in that before a copy was made, the buffer would be filled up. As a result, if you were copying a bunch of small files, xcopy would be slightly faster. I'm not sure if there is an advantage on the current OSes.
We've also experienced issues with xcopy using the xp_cmdshell, or anything related to SQL Server, to include executing a .cmd file from SQL Agent. When we went to copy, everything was okay. We especially saw a problem when we were copying using UNC names. In those cases, xcopy just refused to work, but copy worked every time.
It tooks us a while to figure out what was going on since when we ran the .cmd file ourselves, everything was normal. However, whenever SQL Server would execute it, we'd see the failure. Switching to copy resolve the problem.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 9, 2002 at 9:35 pm
I agree that xcopy used to be more efficient for copying, would be interesting to test to see if still the case. Xcopy was also able to act as a mini backup, look at the various switches supported.
The /I switch tells xcopy that if the destination folder doesn't exist and you are copying more than one file, the destination is a folder.
Brian, Im curious about your note. If xcopy just doesn't support UNC (does it?) thats one thing, but if it fails when running from xp_cmdshell only...thats something different. Could it be that the agent account didnt have the same access you did interactively? Be nice to unravel the mystery.
Andy
July 9, 2002 at 9:46 pm
The only bug I find is Q152134, which pertains to 6.0 & 6.5, I dont see any others.
Andy
July 10, 2002 at 7:05 am
I see the knowledge base article only lists SQL Server 6.0 and 6.5. However, we were having problems on a 7.0 server. As soon as we switched to copy, everything worked like a charm.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply