Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Create BAT File and execute the same in Trigger

Author  Topic 

daipayan
Posting Yak Master

181 Posts

Posted - 2013-05-09 : 07:47:22
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:
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:
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:
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


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.

Regards,
Daipayan
Software Analyst


Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-09 : 07:57:01
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
Go to Top of Page

daipayan
Posting Yak Master

181 Posts

Posted - 2013-05-09 : 08:00:39
Sir,

I am not well-versed with SSIS Package, can you please guide me to do the same, Please.

Please guide me, so that I can overcome my issue.

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




Regards,
Daipayan
Software Analyst


Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-09 : 08:05:19
Another way could be to use "xp_cmdShell" and wrap it into the stored procedure after insert statement.

Cheers
MIK
Go to Top of Page

daipayan
Posting Yak Master

181 Posts

Posted - 2013-05-09 : 08:08:00
Mike,

I am using xp_cmdshell command, but don't know what to write in the procedure/trigger which will do the rest of the process.

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



Regards,
Daipayan
Software Analyst


Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-09 : 08:11:32
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
Go to Top of Page

daipayan
Posting Yak Master

181 Posts

Posted - 2013-05-09 : 08:41:06
Dear Visakh,

My main problem was that I have the table which will create Folder in a separate network shared folder, path as follows - "\\10.0.0.35\Project\Folder".

But since the SQL Server was not getting permission to do so with sa login, so I chose other way to do so.

I check with the following T-SQL about the permission:
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


And I got following result:
TheOutput
-------------
nt authority\system
NULL
NULL
Access is denied.
NULL
--------------

Now, if I get certain way to provide the SQL Server the permission, so that it can go ahead and create folder in the network shared folders, my all problem will be solved.

Can you guide me on this?

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




Regards,
Daipayan
Software Analyst


Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-09 : 10:01:25
I think you should think about doing it differently.

How about a shell script or simple program that checks the database, then decides whether to create the folder / batch file or not.

Making the database do things like this is not a good idea -- the db is good at search and managing data, and using it to modify the file system is pretty dirty.

Powershell etc would be able to hook into .net components to interact with the database...

Are there other developers where you work that could help you out?

Charlie.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

daipayan
Posting Yak Master

181 Posts

Posted - 2013-05-10 : 02:24:48
I solved the problem.

Last night I did a immense search google and atlast, I got my result.

All I did as follows:
  • 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


Thank You all for all your guidance..

Regards,
Daipayan
Software Analyst


Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F
Go to Top of Page
   

- Advertisement -