Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Location
    India
    Posts
    14

    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:
    Code:
    ------------------------------------------
    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:
    E:\ProjectFolder\AAA\AAA1
    E:\ProjectFolder\AAA\AAA2
    E:\ProjectFolder\ARC\ARC3
    E:\ProjectFolder\MIZ\MIZ4

    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:
    Code:
    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
    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:
    Code:
    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 + '" "\\10.0.0.35\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
    Location
    In front of the computer
    Posts
    15,579
    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!

    -PatP
    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
  •