how to create directorys in c drive based on column path please....

Question

Post reply

how to create directorys in c drive based on column path please....

asita

SSCarpal Tunnel

Points: 4144
More actions
April 14, 2010 at 4:15 pm

#223310

Hello All,.
in my c:\ drive no temp directory yet,
then now in my sql server table i have a column call directorypath with values as
c:\Temp\t1\midcap1\belzer1\
c:\Temp\t1\midcap1\belzer2\
c:\Temp\t1\midcap1\belzer3\
c:\Temp\t1\midcap1\belzer4\
c:\Temp\t1\midcap1\Delzer23\
c:\Temp\t1\midcap1\Celzer22\
c:\Temp\t1\midcap1\Celzer12\
c:\Temp\t1\midcap1\Celzer13\
c:\Temp\t1\midcap1\Celzer14\
c:\Temp\t1\midcap1\Celzer15\
c:\Temp\t1\midcap1\Celzer16\
c:\Temp\t1\midcap1\Eelzer22\
c:\Temp\t1\midcap1\Eelzer23\
c:\Temp\t1\midcap1\Eelzer24\
how can i make any sql statement so that it creates the above directories
presently what i am doing is
1)select distinct 'mkdir "' + RTRIM(DirectoryPath) + '"' from sa.storefiles
2)then copy all entries and save in a text file and save it as .bat(batch file)
3)then execute the batch file in command prompt
but now i would like to know is there any way that i can execute directly from sql server itself to create directories (any sql statement or t-sql stuff) please
Help me
Thanks alot in advance
asita

Viewing 7 posts - 1 through 6 (of 6 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Hi buddy,

as i dont have any DDLs i can directly use to attend to your request, i am giving u a vague and rogue code 😀

Please test it before putting it into use

DECLARE @sql VARCHAR(4000)

SET @sql = ''

SELECT @sql = @sql + 'EXEC MASTER.SYS.XP_CREATE_SUBDIR '''+ COLUMN_NAME +''''+ CHAR(10) FROM TABLE_NAME

PRINT @sql

--EXEC SP_EXECUTESQL @sql

Cheers!!

Edit : Fixed some parts of the code to iterate through all the rows.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Be sure taht the account who is going to execute that query had necessary and adequate permissions to create a new folder on the OS..

Please let us know if that worked for you.

Cheers!!

asita SSCarpal Tunnel Points: 4144 More actions · Answer 3

hello coldcoffee,

Thank you very much for yoyr help,

i tried it but it says this error

EXEC MASTER.SYS.XP_CREATE_SUBDIR 'c:\Temp\t1\midcap1\Eelzer24\'

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

please help me

Thanks in advance

asita

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Try this then buddy...

DECLARE @sql VARCHAR(4000)

SET @sql = ''

SELECT @sql = @sql + 'EXEC MASTER.SYS.XP_CREATE_SUBDIR N'''+ COLUMN_NAME +''''+ CHAR(10) FROM TABLE_NAME

PRINT @sql

Tell us if this code worked for you..

Cheers!!

Paul White SSC Guru Points: 150468 More actions · Answer 5

COldCoffee (4/14/2010)
...i am giving u a vague and rogue code...

Rogue code is right 😉

This extended stored procedure is undocumented and might stop working one day, format your hard drive, or set your shoes on fire. Never use it in a production system - in fact, it is better not to use it at all.

SQL Server is not the right place to be creating file system structures, but if you choose to ignore that, there are documented and supported ways to achieve the same thing.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Paul White NZ (4/16/2010)
COldCoffee (4/14/2010)
...i am giving u a vague and rogue code...
Rogue code is right ;-).

LOL, 😀

Yes there are other ways in doing it.. I will let u know some method when i reach office mate 🙂

And thanks,Paul!