------------------------------------------
ID | ClientCode | ProjectName
------------------------------------------
 1 |     AAA    | Dubai Airport Phase I
 2 |     AAA    | Dubai Airport Phase II
 3 |     ARC    | Salala
 4 |     MIZ    | UMBC Building
------------------------------------------

CREATE TRIGGER [dbo].[CreateFolderName]
ON [dbo].[Project]
after INSERT
AS
SET NOCOUNT ON
BEGIN
	declare @chkdirectory as nvarchar(4000),  @folderName varchar(100),  @mainfolderName varchar(100)
    declare @folder_exists as int
    SET @mainfolderName = (SELECT ClientCode AS Project FROM INSERTED) 
    SET @folderName = (SELECT (ClientCode + cast(ID as varchar(10))) AS Project FROM INSERTED) 
    set @chkdirectory = 'E:\ProjectFolder\' + @mainfolderName + '\' + @folderName
 
    declare @file_results table
    (file_exists int,
    file_is_a_directory int,
    parent_directory_exists int
    )
 
    insert into @file_results
    (file_exists, file_is_a_directory, parent_directory_exists)
    exec master.dbo.xp_fileexist @chkdirectory
     
    select @folder_exists = file_is_a_directory
    from @file_results
     
    --script to create directory       
    if @folder_exists = 0
     begin
        print 'Directory is not exists, creating new one'
        EXECUTE master.dbo.xp_create_subdir @chkdirectory
        print @chkdirectory +  ' created on ' + @@servername
     end       
    else
    print 'Directory already exists'    
END
SET NOCOUNT OFF
GO

DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000)
SET @FileName = REPLACE('E:\ProjectFolder\(select ClientCode from INSERTED)\(select ClientCode + cast(ID as varchar(10)) from INSERTED)\xcopy_'+ (SELECT cast(ID as varchar(10)) FROM INSERTED) +'.bat','/','-')
SET @bcpCommand = 'bcp "SELECT 'xcopy "E:\ProjectFolder\' + clientCode + '" "\\10.0.0.35\Project\Folder" /T /E /I' FROM INSERTED" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U SQLServerUsername -P SQLServerPassword -c'
EXEC master..xp_cmdshell @bcpCommand

quote:

---

Originally posted by visakh16

Not a recommended approach to do this on trigger.
I would have implemented this by means of ssis package. I would have added a step to call and execute this package inside procedure where you do insertion of project information. Inside package you can add file system task to create a folder and also add a script task to create the bat file also on the fly.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

---

quote:

---

Originally posted by MIK_2008

Another way could be to use "xp_cmdShell" and wrap it into the stored procedure after insert statement.

Cheers
MIK

---

DECLARE  @Results table(
ID int identity(1,1) NOT NULL,
TheOutput varchar(1000))

insert into @Results (TheOutput)
exec master..xp_cmdshell 'whoami' --nt authority\system for example

insert into @Results (TheOutput)
exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system  is not a user...command fails.

insert into @Results (TheOutput)
exec master..xp_cmdshell 'dir \\10.0.0.35\cad\files' --can this user see the share?\\10.0.0.35\Project\Folder

SELECT * FROM @Results

TheOutput
-------------
nt authority\system
NULL
NULL
Access is denied.
NULL
--------------

quote:

---

Originally posted by visakh16

you can use dtexec to execute ssis package from t-sql
see

http://www.codeproject.com/Articles/27156/How-to-Call-SSIS-Package-from-the-Stored-Procedure

Inside SSIS use file system task to create folder

http://sqlserversolutions.blogspot.com/2009/01/creating-directory-using-ssis.html

Use script task to generate bat file
the idea will be as below
http://dwhanalytics.wordpress.com/2011/03/17/ssiscreate-a-dynamic-file-using-script-task/



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

---

• First Enabled server proxy account
  



• Then go to services.msc and provided domain/username & password which have right to create folder to the MS SQL Service
  



• Last, trigger started working super fine, now I neither have to create bat file nor execute the bat on fly