Help with xp_cmdshell folder creation

Question

Help with xp_cmdshell folder creation

CRMUK

SSC Veteran

Points: 221
More actions
January 31, 2016 at 7:15 am

#311231

I have the following script which works great. It creates the folder as required.
declare @md varchar(100), @Path varchar(100),@recordid varchar(10)
set @recordid = (select recordid from inserted)
SET@Path = 'C:\MyTestPath\'
SET@md =' mkdir ' + @Path +'REF-' + @recordid
EXECxp_cmdshell@md, no_output
PRINT @md
However, there is now a desire to also create subfolder structure in the newly created folder name
It will always be consistent and the names will be always the same.
e.g
REF-10001 (New Folder which above creates)
Sub Folder 1
Sub Folder 2
Sub Folder 3
Sub Sub Folder 1 (In Sub Folder 3)
Sub Sub Folder 2 (In Sub Folder 3)
Folder 4
Any help on how I can easily adapt the script to cater for this, would be greatly appreciated.

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

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

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

Whyy wouldn't you just change all your VARCHAR variable definitions to 8000 and do the same thing for the sub and sub sub folders?

--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)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

If memory serves (mobile so cant test) mkdir will make all dirs in the hierarchy if they are missing. So, just say

mkdir C:\Dir1\Dir2\Dir3

and if Dir1 and Dir2 do not exist those too will be created in addition to Dir3.

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

Orlando is correct, the MD command does create children. The only problem I can see you might run into is if the directory already exists, so you might want to encase the MD command in a check:

if not exist c:\temp\test1\test2\ md c:\temp\test1\test2

Edit: Oh yeah, if your directories can contain spaces, you'll want to enclose your paths in double quotes (") to be safe.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

CRMUK SSC Veteran Points: 221 More actions · Answer 4

Thanks for your input guys

I ended up doing the following

declare @md varchar(100), @SD varchar(100), @Path varchar(100), @recordid varchar(10)

set @recordid = (select recordid from inserted)

SET@Path = 'C:\MyTestPath\'

SET@md =' mkdir ' + @Path +'REF-' + @recordid + '\Folder1\Subfolder1'

SET@SD =' mkdir ' + @Path +'REF-' + @recordid + '\Folder2\Subfolder1'

EXECxp_cmdshell@md, no_output

EXECxp_cmdshell@SD, no_output

PRINT @md

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

It looks like you're creating a folder for every row that gets inserted into a table. Presumably, you're going to write some data to each one of these folders you create. If you're doing this on a production SQL Server, then at least consider the possibility of disk space on C: drive. You don't want to fill up your C: drive with a bunch of data and have no way to control it.

You'll want to make sure you have plenty of space available to handle whatever load you throw at it. At the very minimum, consider implementing some sort of maintenance that cleans things out older than N days.

If you have another drive you can move it to, that would be best. If you're in a clustered environment, it'll need to be a shared volume. You'll still need some type of maintenance routine, but at least you'll have the option of having the SAN volume expanded if you get into trouble with space.

If you implement this is a trigger, which is what it looks like, you'll also want to make sure the trigger performs well enough that if you insert 1000 rows or more at a time, that the required OS-level operations can complete in a timely fashion.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sergiy SSC Guru Points: 110206 More actions · Answer 6

Ed Wagner (2/1/2016)
If you implement this is a trigger, which is what it looks like, you'll also want to make sure the trigger performs well enough that if you insert 1000 rows or more at a time, that the required OS-level operations can complete in a timely fashion.

1000 inserted rows will still create a single folder referring one of the inserted rows, randomly selected:

set @recordid = (select recordid from inserted)

So, not performance issues here.

May be data loss, but not performance hit.:-)

_____________
Code for TallyGenerator

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

Sergiy (2/3/2016)
Ed Wagner (2/1/2016)
If you implement this is a trigger, which is what it looks like, you'll also want to make sure the trigger performs well enough that if you insert 1000 rows or more at a time, that the required OS-level operations can complete in a timely fashion.
1000 inserted rows will still create a single folder referring one of the inserted rows, randomly selected:
set @recordid = (select recordid from inserted)
So, not performance issues here.
May be data loss, but not performance hit.:-)

Are you sure you are not thinking of this query form?

select @recordid = recordid from inserted

The OP will find out soon enough what the problem is when they attempt their trigger with more than 1 row :w00t:

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

CRMUK SSC Veteran Points: 221 More actions · Answer 8

select @recordid = recordid from inserted

The OP will find out soon enough what the problem is when they attempt their trigger with more than 1 row :w00t:[/quote]

Thanks for the heads up everyone.

The front end interface should never insert more than one row at a time. 🙂

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

CRMUK (2/3/2016)
select @recordid = recordid from inserted
The OP will find out soon enough what the problem is when they attempt their trigger with more than 1 row :w00t:
Thanks for the heads up everyone.
The front end interface should never insert more than one row at a time. 🙂

What about production support, ETL or other backend activities? Never is a tough standard to live up to. Never gives me trouble sleeping...just something to consider preparing for. It is not much work to deal with it and it could save you or someone an urgent phone call at an inopportune time.

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

CRMUK SSC Veteran Points: 221 More actions · Answer 10

What about production support, ETL or other backend activities? Never is a tough standard to live up to.

Very true..

I did amend the insert value as suggested.

Many thanks

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 11

CRMUK (2/3/2016)
What about production support, ETL or other backend activities? Never is a tough standard to live up to.
Very true..
I did amend the insert value as suggested.
Many thanks

Great, now show us the code?

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