SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Create BAT File and execute the same in Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

daipayan
Posting Yak Master

India
181 Posts

Posted - 05/09/2013 :  07:47:22  Show Profile  Visit daipayan's Homepage  Send daipayan a Yahoo! Message  Reply with Quote
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

India
52325 Posts

Posted - 05/09/2013 :  07:57:01  Show Profile  Reply with Quote
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

India
181 Posts

Posted - 05/09/2013 :  08:00:39  Show Profile  Visit daipayan's Homepage  Send daipayan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/09/2013 :  08:05:19  Show Profile  Reply with Quote
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

India
181 Posts

Posted - 05/09/2013 :  08:08:00  Show Profile  Visit daipayan's Homepage  Send daipayan a Yahoo! Message  Reply with Quote
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

India
52325 Posts

Posted - 05/09/2013 :  08:11:32  Show Profile  Reply with Quote
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

India
181 Posts

Posted - 05/09/2013 :  08:41:06  Show Profile  Visit daipayan's Homepage  Send daipayan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/09/2013 :  10:01:25  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

India
181 Posts

Posted - 05/10/2013 :  02:24:48  Show Profile  Visit daipayan's Homepage  Send daipayan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000