Project Folder Creation Stored Procedure in SQL 2005

[davidsims]

I created the following stored procedure to create our project folder directories as part of a user initiated workflow on project creation.

First, the parameters are passed from Vision to the SP. Second, the xp_cmdshell calls the command mkdir and the path is a combination of the directory and the parameters passed from Vision. The problem is that nothing happens when this SP is initiated. I tried to also use the file path "H:/" to test it and that did not work either. Also, I am pretty sure I enabled xp_cmdshell.

Do I have a syntax error?

Code: Select all

ALTER PROCEDURE [dbo].[usp_createprojectfolders]



@group varchar(20),
@wbs1 varchar(20),
@name varchar(20)


AS

set nocount on
   
      declare @command varchar(1000)
      set @command = 'mkdir \\tg-nas02\volsharevision\projects\' + @group + '\' +@wbs1 + '_' +@name

      exec master..xp_cmdshell @command , no_output   

      set nocount off


[stevestolz]

I'm not sure what's wrong with your code - could be a permissions issue if the user under which SQL is running does not have permission to create folders in that location.

We use a custom DLL to create Windows folders for our clients, which allows us to give feedback to the user (success or failure). The tricky part is the security (in our case the process running IIS needs remote access).

Steve

[SStaats]

I am in the process of setting up a Stored Procedure to create project folders as well. Have you had any luck getting your procedure to work?

[sroth]

I have managed to get the script to work, but only when I set the folder permissions to allow "Everyone" write permissions. Can anyone tell me what user SQL uses to execute this stored procedure?

[mdobler]

you're probably better of with some vb.net code that you can call in the workflows. That will give you more control over security and folder creation than any stored procedure

[sroth]

SQL Server 2005 uses the "SQL Server (MSSQLSERVER)" service log on to run the stored procedures. So if you change the log on as properties to a user on the domain that has write access to your project folders, you will be able to use the workflow successfully.