Create new folder and copy file using xp_cmdshell

Question

Create new folder and copy file using xp_cmdshell

indinfo2011

SSC Eights!

Points: 833
More actions
December 20, 2011 at 7:16 am

#391166

Hello All,
I have to copy file daily from SQL job to the file server. The SQL job should create a folder first day of each month as 'Jan', 'Feb' etc.. and upload the file accordingly.
If it's the last day of the month then it should create year folder '2012' and then month folder 'Jan' then copy the output of select table.
How do I do this in xp_cmdshell? Can anyone give me a example or sample code?
Thanks,

Viewing 13 posts - 1 through 12 (of 12 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

There is no need to use xp_CmdShell here. You can do this using PowerShell directly from within your Job. In SQL Server 2008 a PowerShell job step type is available in SQL Agent:

http://msdn.microsoft.com/en-us/library/cc280490.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

You should have xp_cmdshell disabled for security reasons

Andrew SQLDBA

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

You could look at:

http://nclsqlclrfile.codeplex.com/[/url]

I tend to disable xp_cmdshell..

CEWII

viiki.seth Ten Centuries Points: 1127 More actions · Answer 4

indinfo2011 (12/20/2011)
How do I do this in xp_cmdshell? Can anyone give me a example or sample code?

Here is sample code .

DECLARE @cmd nvarchar(500),@folderName varchar(100)

Declare @firstday int

Set @firstday =DATEPART(day, GETDATE())

If @firstday=1

Begin

SET @folderName = DATENAME(month, GETDATE())

SET @cmd = 'mkdir D:\test\' + @folderName

EXEC master..xp_cmdshell @cmd

End

Else

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

Cant you just write a 4 line code VBscript in a batch file to create a folder and schedule it using windows scheduler ?

Something like this

Dim folder

folder= "Yourpath"

set objFSO = createobject("Scripting.FileSystemObject")

if objFSO.FolderExists(folder) = False then

objFSO.CreateFolder strFolder

end if

I surely wouldnt enable xp_cmdshell on my server for such a simple thing .

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Sachin Nandanwar (12/22/2011)
Cant you just write a 4 line code VBscript in a batch file to create a folder and schedule it using windows scheduler ?

Just about anything would be better than using xp_cmdshell, but VBScript is pretty low on my list of potential replacements. If I'm going to write something new I 'm going to use a current, managed, scripting language like PowerShell.

Dim folder
folder= "Yourpath"
set objFSO = createobject("Scripting.FileSystemObject")
if objFSO.FolderExists(folder) = False then
objFSO.CreateFolder strFolder
end if

Here is your VBScript ported to PowerShell:

$folder= "Yourpath"

if(!(Test-Path $folder))

{

New-Item -Path $folder -ItemType Directory

}

I surely wouldnt enable xp_cmdshell on my server for such a simple thing .

I could not agree more.

indinfo2011 (12/20/2011)
Hello All,
I have to copy file daily from SQL job to the file server. The SQL job should create a folder first day of each month as 'Jan', 'Feb' etc.. and upload the file accordingly.
If it's the last day of the month then it should create year folder '2012' and then month folder 'Jan' then copy the output of select table.
How do I do this in xp_cmdshell? Can anyone give me a example or sample code?
Thanks,

This needs error handling and some unit testing to make sure it matches your requirements but here is the start of a PowerShell script:

Param([string]$SourceFile,[string]$FileDestination)

# make a new directory for the current month if one does not exist

$MonthDir = (Join-Path $FileDestination (Get-Date -format MMM))

if(!(Test-Path $MonthDir)) {New-Item -Path $MonthDir -ItemType Directory}

# copy our file

Copy-Item -Path $SourceFile -Destination $MonthDir

# on last day of month make new directory for year and subfir for current month

$LastDayOfThisMonth = (Get-Date -Year (Get-Date).Year -Month (Get-Date).Month -Day 1).AddMonths(1).AddDays(-1)

if ($LastDayOfThisMonth.Day -eq (Get-Date).Day)

{

$YearDir = (Join-Path $FileDestination (Get-Date).Year)

if(!(Test-Path $YearDir)) {New-Item -Path $YearDir -ItemType Directory}

$MonthDir = (Join-Path $YearDir (Get-Date -format MMM))

New-Item -Path $MonthDir -ItemType Directory

# copy our file

Copy-Item -Path $SourceFile -Destination $MonthDir

}

If you were to save the PowerShell above to C:\ScriptName.ps1 you could then call it from a PowerShell prompt like this:

PS C:\>ScriptName.ps1 -SourceFile "C:\Temp\SomeFile.txt" -FileDestination "\\someserver\somesharename"

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

personally i find this easier to create the path

exec xp_create_subdir 'G:\somepath\mynewfolder'

or

exec xp_create_subdir '\\myserver\myshare\mynewfolder'

If the folder doesn't exist it will create it 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

Perry Whittle (12/22/2011)
personally i find this easier to create the path
exec xp_create_subdir 'G:\somepath\mynewfolder'
or
exec xp_create_subdir '\\myserver\myshare\mynewfolder'
If the folder doesn't exist it will create it 😉

Sure...but how would you copy the file (without xp_cmdshell)?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

opc.three (12/22/2011)
Sure...but how would you copy the file (without xp_cmdshell)?

Via a VB script ran as a SQL agent job step!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

Perry Whittle (12/22/2011)
opc.three (12/22/2011)
Sure...but how would you copy the file (without xp_cmdshell)?
Via a VB script ran as a SQL agent job step!

OK 😛

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 11

Perry Whittle (12/22/2011)
opc.three (12/22/2011)
Sure...but how would you copy the file (without xp_cmdshell)?
Via a VB script ran as a SQL agent job step!

Not sure..But cant we just take SQL totally out of the picture and let windows scheduler do all this creation stuff ??

Not sure about the implications though.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

You could certainly do that. Either way, whether SQL Agent or Windows Task Scheduler, I would favor PowerShell over VBScript in every conceivable use case.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato