March 22, 2011 at 1:24 pm
Hi all,
I need to copy files and subdirectory from one server to the next, and when I ran this command at the Command Prompt - I had no problem. All the files and the sub-directory copy fine.
When I try to add the following command to my SQL job:
EXEC master.dbo.xp_cmdshell 'XCOPY G:\dirname etworkpath\G$\dir /D /E /Y >> G:\logfile.txt'
then I got this error : Invalid drive specification
If I use this command:
EXEC master.dbo.xp_cmdshell 'XCOPY "G:\dirname etworkpath\G$\dir" /D /E /Y >> G:\logfile.txt'
I got this error: "File not found - dirname"
If I use this command :
EXEC master.dbo.xp_cmdshell 'XCOPY "G:\dirname" "etworkpath\G$\dir" /D /E /Y >> G:\logfile.txt'
I got this error : "Invalid drive specification"
I even create a batch file with just the XCOPY command and then have my SQL job call the batch file as:
EXEC master.dbo.xp_cmdshell 'G:\XCopy_script.bat' and I got this error when running the batch file:
C:\WINDOWS\system32>XCOPY G:\dirname \etworkpath\i$\dirname /D /E /C /R /I /K /Y >> G:\logfile.txt
Invalid drive specification.
I can not use the COPY command, because it does not have the Sub-directories that I need to copy over. Any help is appreciate. I had try the BOL and read on all the XCOPY and COPY help page, and I'm getting no where.
Thank you
March 22, 2011 at 2:10 pm
I could be wrong (been a while since I used it), but I seem to remember that xp_cmdshell needs UNCs, instead of mapped drives. Is "G" a mapped network drive?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 22, 2011 at 2:12 pm
probably your sqlserver service account doesn't have auth to your source and/or target folder.
IMO it is better to use a sqlagent for this purpose because you can easily define a proxy account that has all needed windows auth for your xcopy.
In the sqlagent job create a job step of type "Operating system (cmdexec)" and directly perform the xcopy.
(That's the we we copy our bak files to a safe zone, but for that purpose our sqlagent service account has the needed auth, so we use no proxy account.)
Avoid xp_cmdshell whenever you can.
e.g.
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 03/22/2011 21:09:11 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'WS20098002\SQL2008R2DEFULL',
@enabled=0,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Full backup Server volgens DBA-systeem',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [DDBAServerPing Full Database Backup] Script Date: 03/22/2011 21:09:12 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DDBAServerPing Full Database Backup',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'BACKUP DATABASE [DDBAServerPing] TO [DDBAServerPingFull] WITH INIT , NOUNLOAD , NAME = ''DDBAServerPing_Full'', SKIP , STATS = 10, DESCRIPTION = ''Full Database Backup'' , NOFORMAT ',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [CopyToBackupSrv] Script Date: 03/22/2011 21:09:12 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CopyToBackupSrv',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'XCOPY X:\MSSQL10_50.SQL2008R2DE\MSSQL\Backup\*.* \\uabe0fs01.mysafezone.com\bu2diskd$\WS20098002\SQL2008R2DE\Database\ /c /k /h /v /y',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DBA Backup Schema 4',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100622,
@active_end_date=99991231,
@active_start_time=193000,
@active_end_time=235959,
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 22, 2011 at 2:29 pm
GSquared (3/22/2011)
I could be wrong (been a while since I used it), but I seem to remember that xp_cmdshell needs UNCs, instead of mapped drives. Is "G" a mapped network drive?
I think that's what's happening too.
ALZDBA (3/22/2011)
IMO it is better to use a sqlagent for this purpose because you can easily define a proxy account that has all needed windows auth for your xcopy.
Agreed!
newdb, Resist the temptation to use xp_cmdshell to turn SQL Server into a sys admin scripting platform. Disable xp_cmdshell on all your systems.
Use SQL Agent as suggested to call a PowerShell script that does what you need to do.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 24, 2011 at 6:46 am
Actually, avoiding xp_cmdshell is a good enough idea that I'll chime in on that one too. I use CLR procs for that kind of thing these days.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 28, 2011 at 9:14 am
Thank you everyone for your help and suggestion. Production issue over the last few days and now finally have time to concentrate on this. I will try the suggestion above and see if I can incorporate into my existing job.
Thank you
March 28, 2011 at 9:14 am
Thank you everyone for your help and suggestion. Production issue over the last few days and now finally have time to concentrate on this. I will try the suggestion above and see if I can incorporate into my existing job.
Thank you
March 28, 2011 at 7:29 pm
A different country heard from... With the proper proxy in place, I embrace xp_CmdShell. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 8:36 pm
opc.three (3/22/2011)
GSquared (3/22/2011)
I could be wrong (been a while since I used it), but I seem to remember that xp_cmdshell needs UNCs, instead of mapped drives. Is "G" a mapped network drive?I think that's what's happening too.
ALZDBA (3/22/2011)
IMO it is better to use a sqlagent for this purpose because you can easily define a proxy account that has all needed windows auth for your xcopy.Agreed!
newdb, Resist the temptation to use xp_cmdshell to turn SQL Server into a sys admin scripting platform. Disable xp_cmdshell on all your systems.
Use SQL Agent as suggested to call a PowerShell script that does what you need to do.
The most likely reason that the XCOPY was failing is because the drive mappings are specific to the user who mapped them. When you use xp_cmdshell, this is actually the user that the SQL Server service is running as (you can see this in CONTROL PANEL --> Services).
By changing to UNC's instead of drive letters, you avoid this problem.
March 28, 2011 at 11:50 pm
happycat59 (3/28/2011)
....The most likely reason that the XCOPY was failing is because the drive mappings are specific to the user who mapped them. When you use xp_cmdshell, this is actually the user that the SQL Server service is running as (you can see this in CONTROL PANEL --> Services).
By changing to UNC's instead of drive letters, you avoid this problem.
Indeed, UNC for everything but a real local drive.
good point.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 29, 2011 at 11:57 am
I agree with Jeff on the xp_CmdShell issue. With proper server security (proxy account) its not an issue.
xp_CmdShell can be very handy when processing data, however, DOS mode or Command Prompt (whatever you want to call it) has it's quirks. I've found these three recommendations go a long way toward keeping things working smoothly:
1) Call xp_CmdShell through a Stored Procedure
2) In the Stored Procedure construct a VarChar(8000) variable [can't use MAX here] with all the parameters for the call wrapping any file paths or parameters with special characters in double-quotes (you could overkill the situation and always wrap everything in double-quotes)
3) When you finally call xp_CmdShell wrap the above in double-quotes
i.e., Say you want to count all the records in "C:\Program Files\Common Files\System\Ole DB\oledbjvs.inc" that contain the word "BAD". You would execute the following command at the Command Prompt:
Find /C "BAD" "C:\Program Files\Common Files\System\Ole DB\oledbjvs.inc"
The following code exemplifies the above recommendations for use with T-SQL:
Declare @STR VarChar(8000);
Set @STR='Find /C "BAD" "C:\Program Files\Common Files\System\Ole DB\oledbjvs.inc"';
Print @STR;
Set @STR='"'+@Str+'"';
Print @STR;
Exec xp_CmdShell @STR;
You might say: well my version works just fine without your last step. You'll find that if the program to be executed requires double-quotes suddenly there are problems. My third recommendation fixes these problems and can be used in all circumstances.
My Stored Procedure takes 3 parameters (appropriately double-quoted as per recommendation 2):
1) the command to execute
2) parameters the command may need
3) an OUTPUT variable; if its NULL on input I let xp_CmdShell output to a rowset, else I capture the rowset and place it into the OUTPUT variable.
Hope this helps...
March 29, 2011 at 1:05 pm
Per the documentation on xp_cmdshell, the advice on using double-quotes may be flawed.
From MSDN (http://msdn.microsoft.com/en-us/library/ms175046(v=SQL.105).aspx):
' command_string '
Is the string that contains a command to be passed to the operating system. 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 required if any spaces are present in the file paths or program names referenced in command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.
(Emphasis added)
In my experience, xp_cmdshell will accept and run the command, but will treat it as if any second (and following) sets of double-quotes were not in it. But that's not in the documentation, it's just what I seem to remember from testing it.
With regard to security for xp_cmdshell, yes, it can be reasonably secure. It's more complex to secure properly than SQLCLR, unless you do something stupid like set a database to trusted, set an assembly to unsafe, and have the assembly do something like execute dynamic commands from input values. But the default settings for SQLCLR are safer than xp_cmdshell, even with a decent proxy account.
Does that mean "don't ever, under any circumstances, use xp_cmdshell"? Of course not. As with anything else, just make sure you know what you're doing before you turn it on.
However, just to annoy Jeff, I'll now assert that SQLCLR is much, much cooler than xp_cmdshell, and the people who use SQLCLR are sexier and have better taste in movies than those who use xp_cmdshell.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 29, 2011 at 1:46 pm
Microsoft says a lot of things about their Command Prompt/DOS emulation environment, some of which is conflicting... From my experience the code presented in my previous post will work under all circumstances where an executable and/or its parameters need double-quotes, or not, even for internal DOS commands (just don't wrap them with double-quotes)!
This code is not the result of any brilliance on my part but represents hours of torment trying various combinations of things as calling requirements changed over time; the final distillation of which is what I presented. Take it or leave it... I am just trying to help the community at large. 🙂
March 29, 2011 at 1:58 pm
Peter E. Kierstead (3/29/2011)
Microsoft says a lot of things about their Command Prompt/DOS emulation environment, some of which is conflicting... From my experience the code presented in my previous post will work under all circumstances where an executable and/or its parameters need double-quotes, or not, even for internal DOS commands (just don't wrap them with double-quotes)!This code is not the result of any brilliance on my part but represents hours of torment trying various combinations of things as calling requirements changed over time; the final distillation of which is what I presented. Take it or leave it... I am just trying to help the community at large. 🙂
Makes sense.
I'm not attacking your statements, just pointing out that it may not give 100% reliable results to use it that way.
There are a lot of places where MS documentation is either unclear or even provably wrong. It's just best to know what they say something will do and what it's intended for, so that if they "fix" something and make it match the documentation (which they've done before), it doesn't break anything critical to you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply