How to create a Folder using a SQL Query?

Hi

How can I create a Folder (eg. C:\MyNewFolder) using SQL Query?

May 15th, 2007 11:24am

EXEC xp_cmdshell 'MD C:\MyNewFolder'

Chris

Free Windows Admin Tool Kit Click here and download it now
May 15th, 2007 11:41am

hey ..u can create a file using FSO ..n operate further..

http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

May 15th, 2007 12:17pm

hey chris .

i tried this in my query analyzer of sql2000 its not wrking..

the error is..

Could not find stored procedure 'xp_cmdshell'. even i tried using dbo.xp_cmdshell not wrking..

reply plz..

thanks

Free Windows Admin Tool Kit Click here and download it now
May 15th, 2007 12:18pm

All Extended Procedures reside in the master database, so try this:

EXEC master.dbo.xp_cmdshell 'MD C:\MyNewFolder'

Chris

May 15th, 2007 12:24pm
Chris Howarth wrote:

EXEC xp_cmdshell 'MD C:\MyNewFolder'

Chris

Thanks Chris

It work perfect, Im using SQL EXPRESS 2005

Free Windows Admin Tool Kit Click here and download it now
May 15th, 2007 12:25pm

Is there a way to do this?

Code Snippet

declare @Location nvarchar(50)

set @Location = 'C:\mynewfolder'

EXEC master.dbo.xp_cmdshell 'MD ' + @Location

Im getting the following Error

Code Snippet

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '+'.

May 15th, 2007 2:03pm
I tried your code in my SQL Server, I think he doesn't like the concatenation as a parameter...

try to set your string to a variable

Code Snippet

declare @cmdpath nvarchar(60)
set @cmdpath = 'MD '+ @Location
exec master.dbo.xp_cmdshell @cmdpath


Regards


Free Windows Admin Tool Kit Click here and download it now
May 15th, 2007 2:31pm
celobateira wrote:
I tried your code in my SQL Server, I think he doesn't like the concatenation as a parameter...

try to set your string to a variable

Code Snippet

declare @cmdpath nvarchar(60)
set @cmdpath = 'MD '+ @Location
exec master.dbo.xp_cmdshell @cmdpath


Regards


Its working, thank you

May 15th, 2007 2:54pm

Hi,

I added some code in order to get the result from the xp_cmdshell command

This returns null if successfull, if an error occurs returns the error message. May be useful instead of getting an sql error

Code Snippet

declare @cmdpath nvarchar(60), @Location nvarchar(100), @message nvarchar(max)

set @Location = N'C:\Temp\Temp5'

set @cmdpath = 'MD '+ @Location

Create table #result

(

result nvarchar(255)

)

insert into #result (result) exec master.dbo.xp_cmdshell @cmdpath

select @message = ISNULL(@message + ' - ','') + result from #result where result is not null

select @message

drop table #result

Eralper

http://www.kodyaz.com

Free Windows Admin Tool Kit Click here and download it now
May 15th, 2007 5:13pm

thxx chris.. its working ....

thx a lott

May 17th, 2007 12:46pm

Hi,

How Can I verify folder existing or not  : if not exists , need to create the folder. can any one give code for that one.

Free Windows Admin Tool Kit Click here and download it now
June 11th, 2013 4:11pm

You can use xp_fileexist extended system procedure:

http://stackoverflow.com/questions/11740000/check-for-file-exists-or-not-in-sql-server

http://www.mssqltips.com/sqlservertip/1272/file-validation-in-sql-server-with-xpfileexist-stored-procedure/
June 11th, 2013 5:25pm

You can use 

declare @FilePath varchar(100)
set @FilePath = 'D:\FolderTest'
EXEC master.dbo.xp_create_subdir @FilePath

very simple and easy

Free Windows Admin Tool Kit Click here and download it now
February 7th, 2015 2:25am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics