Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009

    Unanswered: Create BAT File and execute the same in Trigger

    Dear All,

    I am situation, where we have a table named as Project, columns for the table as follows:
    ID | ClientCode | ProjectName
     1 |     AAA    | Dubai Airport Phase I
     2 |     AAA    | Dubai Airport Phase II
     3 |     ARC    | Salala
     4 |     MIZ    | UMBC Building
    Now my task was, whenever a project name and other details being created, then a Folder will be created in a server itself in the path E:\ProjectFolder\ in following way:

    You can see here Folder and sub-folder is being created with that following project - client code & ID

    I used following trigger to do the same:
    CREATE TRIGGER [dbo].[CreateFolderName]
    ON [dbo].[Project]
    after INSERT
    	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
            print 'Directory is not exists, creating new one'
            EXECUTE master.dbo.xp_create_subdir @chkdirectory
            print @chkdirectory +  ' created on ' + @@servername
        print 'Directory already exists'    
    This worked like a charm, now my next task is using same trigger, I have to create a BAT file inside that SubFolder - T-SQL for creation of BAT File as follows:
    DECLARE @FileName varchar(50),
            @bcpCommand varchar(2000)
    SET @FileName = REPLACE('E:\ProjectFolder\[red](select ClientCode from INSERTED)[/red]\[red](select ClientCode + cast(ID as varchar(10)) from INSERTED)[/red]\xcopy_'+ (SELECT cast(ID as varchar(10)) FROM INSERTED) +'.bat','/','-')
    SET @bcpCommand = 'bcp "[red]SELECT 'xcopy "E:\ProjectFolder\' + clientCode + '" "\\\Project\Folder" /T /E /I' FROM INSERTED[/red]" queryout "'
    SET @bcpCommand = @bcpCommand + @FileName + '" -U SQLServerUsername -P SQLServerPassword -c'
    EXEC master..xp_cmdshell @bcpCommand
    Here I am not understanding how to insert the above T-SQL in the Trigger as well as the above T-SQL is not right, what's wrong in this?

    Last query that will be included in the trigger is to execute the newly created bat file.

    Hope I am able to make you understand my query. I am sorry, I am bad in english, so maybe I was not able to make you understand my query in proper way. Please if you are unable to understand my query, please ask.

    I beg you all to solve this query. Please help.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    While your solution is intuitive, there is a MUCH better way to accomplish this task.

    Create a job in SQL Agent to do whatever tasks you need to do. By using job steps, you can mix Transact-SQL with Windows commands (just like a batch file), and depending on what version of SQL Server/Agent you are using you may have other choices too (like Perl or PowerShell).

    In your trigger, simply do any needed setup for your job (such as inserting data into a staging table), then EXECUTE sp_start_job to have the job run.

    This accomplishes several important things:
    1. It keeps the trigger small and fast
    2. It allows you to easily mix Windows commands with Transact-SQL
    3. It allows you to separate context so the SQL client can continue to run after queing the job.
    4. It allows you to use a Windows account with higher privilege in the job if needed
    This requires a bit of learning and changing how you think, but the end result is worth it!

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts