05-09-13, 08:52 #1Registered User
- Join Date
- Feb 2009
Unanswered: Create BAT File and execute the same in Trigger
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 ------------------------------------------
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 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\[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
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.
05-09-13, 10:11 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
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:
- It keeps the trigger small and fast
- It allows you to easily mix Windows commands with Transact-SQL
- It allows you to separate context so the SQL client can continue to run after queing the job.
- It allows you to use a Windows account with higher privilege in the job if needed
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.