create folders using t-sql

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    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]

  • Lowell

    SSC Guru

    Points: 323450

    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

    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: 323450

    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: 323450

    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

    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: 996475

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

  • Jeff Moden

    SSC Guru

    Points: 996475

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

  • shew

    SSChampion

    Points: 11310

    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\'

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

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