How to create numerous directories on a server using data from sql table

Question

How to create numerous directories on a server using data from sql table

GF

Hall of Fame

Points: 3774
More actions
April 15, 2015 at 8:19 pm

#303037

SQL 2008
I have a table that has company id, attachment file name, folderexists columns.
First what I need to do is create a series of folder or directories on a networked server using the company id as the folder name where the folder name does not already exist.
Second I need to move files based on attachment file name and company id to the proper folder.
For those who want to know, this is a remediation project because of a bug in our application.
The application is supposed to created the folder based on company id and then put the attachment in that folder.
So does anyone have any scripts that they are willing to let me use for both the First and Second parts?
Always appreciate the help.
Gary

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

GF (4/15/2015)
SQL 2008
I have a table that has company id, attachment file name, folderexists columns.
First what I need to do is create a series of folder or directories on a networked server using the company id as the folder name where the folder name does not already exist.
Second I need to move files based on attachment file name and company id to the proper folder.
For those who want to know, this is a remediation project because of a bug in our application.
The application is supposed to created the folder based on company id and then put the attachment in that folder.
So does anyone have any scripts that they are willing to let me use for both the First and Second parts?
Always appreciate the help.
Gary

Quick thought, this should be straight forward with either xp_cmdshell or SSIS file system task.

😎

GF Hall of Fame Points: 3774 More actions · Answer 2

Thank you for the reply.

xp_cmdshell is disabled and our policy is to keep it disabled.

For SSIS I have no experience with SSIS.

Lowell SSC Guru Points: 323504 More actions · Answer 3

SQL server does not have easy ways to do anything outside of a database, like files and folders.

sql does not have any native way to play with files and folders.

xp_cmdshell, a custom CLR or something completely outside of SQL,like sqlcmd, bcp,a programming language or SSIS are the classic alternatives.

xp_cmdshell can be enabled just for your workload , and disabled again. that is the easiest solution.

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!

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 4

GF (4/16/2015)
Thank you for the reply.
xp_cmdshell is disabled and our policy is to keep it disabled.
For SSIS I have no experience with SSIS.

Too bad. It's an incredibly useful tool and having it disabled will not prevent its use by an attacker that gets in as SA and having it enabled doesn't mean that it can be used by anyone other than "SA".

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

What about a SQL Agent job that kicks off a script? Powershell perhaps?

If that's not an option, and if Lowell's solution (enable xp_cmdshell then disable when you're done) does not work for you, then perhaps you should consider not using SQL Server to drive this task at all. Perhaps you could create a PowerShell, WMI, VB or whatever script that connects to SQL Server, queries your table then uses a loop to do what you need.

You could try doing this with SSIS; you say you don't have any experience with it, here's your chance to learn. This would be a rather simple package to create.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

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

Alan.B (4/16/2015)
What about a SQL Agent job that kicks off a script? Powershell perhaps?
If that's not an option, and if Lowell's solution (enable xp_cmdshell then disable when you're done) does not work for you, then perhaps you should consider not using SQL Server to drive this task at all. Perhaps you could create a PowerShell, WMI, VB or whatever script that connects to SQL Server, queries your table then uses a loop to do what you need.
You could try doing this with SSIS; you say you don't have any experience with it, here's your chance to learn. This would be a rather simple package to create.

Heh... I use xp_CmdShell to call PowerShell. 😛

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 7

Jeff Moden (4/16/2015)
Alan.B (4/16/2015)
What about a SQL Agent job that kicks off a script? Powershell perhaps?
If that's not an option, and if Lowell's solution (enable xp_cmdshell then disable when you're done) does not work for you, then perhaps you should consider not using SQL Server to drive this task at all. Perhaps you could create a PowerShell, WMI, VB or whatever script that connects to SQL Server, queries your table then uses a loop to do what you need.
You could try doing this with SSIS; you say you don't have any experience with it, here's your chance to learn. This would be a rather simple package to create.
Heh... I use xp_CmdShell to call PowerShell. 😛

😛

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Lowell SSC Guru Points: 323504 More actions · Answer 8

Jeff Moden (4/16/2015)
Heh... I use xp_CmdShell to call PowerShell. 😛

...to run a TSQL command to start a SQL Agent Job which launches a Windows Scheduled Task?

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!

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 9

No...MUCH worse!

xp_CmdShell --> PowerShell --> WMI --> Return through OUTPUT of xp_CmdShell to table via persisted computed columns --> XML --> HTML --> SP_SendDBMail. 😀 I use it to build my "Enterprise Wide Disk Status Morning Report" with colors for the different "Percent Full". It also has a special section so that the folks in NetOps can find any CDs or ThumbDrives they may have left in the machines and lost track of.

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

GF Hall of Fame Points: 3774 More actions · Answer 10

Update on this adventure.

I have created all the folders I need to based on a list I generated from a SQL table.

FOR /F %i in (E:\documents\folderlist.txt) do md "%i"

Now I have the next challenge.

I have a table that list all the folders , filenames and path

example;

ID, Filename, Path

753554 , ATT_8262011_51454_PM.pdf , \intentionally not shown\753554\ATT_8262011_51454_PM.pdf

278136 , ATT_8262011_51712_PM.pdf , \intentionally not shown\278136\ATT_8262011_51712_PM.pdf

Now That I have convinced the powers that be to let me enable xp_cmdshell for this project.

I need to know how to parse the table and move the files to the correct folder.

Thanks

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 11

GF (5/19/2015)
Update on this adventure.
I have created all the folders I need to based on a list I generated from a SQL table.
FOR /F %i in (E:\documents\folderlist.txt) do md "%i"
Now I have the next challenge.
I have a table that list all the folders , filenames and path
example;
ID, Filename, Path
753554 , ATT_8262011_51454_PM.pdf , \intentionally not shown\753554\ATT_8262011_51454_PM.pdf
278136 , ATT_8262011_51712_PM.pdf , \intentionally not shown\278136\ATT_8262011_51712_PM.pdf
Now That I have convinced the powers that be to let me enable xp_cmdshell for this project.
I need to know how to parse the table and move the files to the correct folder.
Thanks

You're absolutely on the right path with the DOS "FOR" command but we could have done a little trick from T-SQL to make the folders. But you've got this task done so no sense going there for now.

There are more DOS commands to copy or move individual files but that requires too much work. Since you already know your way around DOS, look ROBOCOPY in DOS help. I think it's the perfect tool for this and it can, of course, be called by and controlled by T-SQL through xp_CmdShell.

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