create folders using t-sql

Question

Post reply

create folders using t-sql

sql_learner29

SSCrazy Eights

Points: 9107
More actions
December 9, 2011 at 12:18 pm

#391092

Hi,
I have a requirement to create a folder structure in a directory path say (c:\test) with the number of rows returned by my select statement...(like Select myname from mytable) if this query returns 10 rows then create 10 folders with names returned.
How can i create folders based on rows returened by a query .
Any help on this...
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

create them on the server, based on a query?

what if they already exist?

what would the query you run look like? (is it really Select myname from mytable?) what columns/data would it return?

what would the name of the directories be? one of those column names, or parse the results of some column?

can you use CLR, or are you limited to xp_cmdShell?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

HI,

The query is like this:

Select Name from Mytable

Returns:

ACCOUNT

FINANCE

PURCHASING

PERSONAL

COMPUTER

Now I will set a path as :

C:\test

So All these names folder should be created inside Test.

If already exists then don't create it..

I cannot use xp_cmdShell as (no permission) any other way to do this?

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Lowell SSC Guru Points: 323518 More actions · Answer 3

a very bassic example using the "md" command for make directory;

note in this example, the parent "Test" directory needs to already exist.

DECLARE @Results table(

ID int identity(1,1) NOT NULL,

TheOutput varchar(1000))

CREATE TABLE #MYTable(myName varchar(30) )

INSERT INTO #MYTable

Select 'Leonardo' UNION ALL

Select 'Brad' UNION ALL

Select 'Arnold' UNION ALL

Select 'Mark' UNION ALL

Select 'Matt' UNION ALL

Select 'Bruce'

declare

@icmd varchar(2000),

@dirname varchar(64)

declare c1 cursor for select myName from #MYTable where myName <> 'Bruce'

open c1

fetch next from c1 into @dirname

While @@fetch_status <> -1

begin

select @icmd = 'md C:\Test\' + @dirname

print @icmd

insert into @Results (TheOutput)

exec master..xp_cmdshell @icmd

fetch next from c1 into @dirname

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

just saw you said no xp_cmdshell;

in that case, unless you can install a CLR, theres no way to do this from TSQL. xp_cmdshell is the only thing i know that can get you access to the disk (besides backups), but a backup will not create a directory, only pace a file inside an existing.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 5

Thanks Lowell...

Your answer is perfect but I cannot use xp_cmdshell as No permission

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

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

Lowell (12/9/2011)
just saw you said no xp_cmdshell;
in that case, unless you can install a CLR, theres no way to do this from TSQL. xp_cmdshell is the only thing i know that can get you access to the disk (besides backups), but a backup will not create a directory, only pace a file inside an existing.

Actually, they can. They can create a new directory for each DB if the directory doesn't already exist. Take a look again, Lowell. 🙂

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

Learner1 (12/9/2011)
Thanks Lowell...
Your answer is perfect but I cannot use xp_cmdshell as No permission
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

You could create a job that uses an "Operating System (CMD Exec)" step to do the same thing (IIRC, can be done without enabling xp_cmdshell). Then, you'd just need to call the job (containing Lowell's code), wait a second or two, and the directories would be created.

Of course, there's more than one way to skin a cat... can you use sp_OA* procedures or OPENROWSET?

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

shew SSChampion Points: 11389 More actions · Answer 8

I ran across http://www.kodyaz.com/articles/create-folder-sql-sys-xp_create_subdir-extended-stored-procedure.aspx and thought it might be helpful for anyone else that may find this page. The Ola Hallengren backup code uses "xp_create_subdir."
EXEC master.sys.xp_create_subdir 'C:\SQLDatabases\Test\'