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:
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\[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.
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
I would not do file operations in a trigger, but what you have looks like it will basically work.
I think the issue might be no permissions for SQL server to get to the sharename \\10.0.0.35\Project\Folder
what results does this return?
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\Project\Folder' --can this user see the share?\\10.0.0.35\Project\Folder
SELECT * FROM @Results
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I am getting following result of the T-SQL
IDTheOutput
1nt authority\system
2NULL
3NULL
4Access is denied.
5NULL
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
mail2payan (5/9/2013)
I am getting following result of the T-SQLIDTheOutput
1nt authority\system
2NULL
3NULL
4Access is denied.
5NULL
that confirms my thoughts on permissions to the shared folder...
when you access any resource OUTSIDE of SQL server, like network shares, user folders local hard drives(ie C:\users\Lowell\Desktop) and folders, xp_cmdshell, bcp with a "trusted" connection, sp_OA type functions etc.
it doesn't matter what YOUR credentials are. Whether you are Domain Admin,Local Admin , logged in as sa, administrative login on a laptop, etc, because SQL will not carry those credentials to the "outside of SQL" security context.
SQL WILL pass your credentials to a linked server, but anything else is using an account you did not intuitively expect it to use.
SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:
or if the above was blank, the account in services:
That account is often an account which has never logged into the domain, and was never assigned permissions to get to the user folders on the local disk or any network share.
As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.
Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.
you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the external object you were trying to access/use works when SQL is run your credentials, so you'd know you need a domain account to access the resource.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Sir,
I Enable the SERVER PROXY ACCOUNT, but to understand the 2nd & 3rd screenshot. Can please elaborate the 2nd & 3rd screenshots?
I need to give the SQL Server the right to create Folder in the network shared Server, but since it does not have the access, it can't do it, so I tried to another way.
But now you have shown a light to do the same, can you please elaborate the steps.
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
Hello Lowell,
I did it.
Thanks for the help 😀
Now, I don't need any bat file, I can directly do it through the trigger itself to create folder in other network drive. :-):-)
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
Are you aware that your trigger will not work if someone inserts multiple rows into the Project table in a single transaction. For instance, try it with something like this:
INSERT INTO PROJECT(ID, ClientCode, ProjectName)SELECT ID, ClientCode, ProjectName
FROM
(
SELECT 5, 'MIZ', 'Project 1'
UNION ALL
SELECT 6, 'MIZ', 'Project 2'
UNION ALL
SELECT 7, 'MIZ', 'Project 3'
)
Unless you are restricting how the Project table can be built to ensure that entries are always added one at a time, you are going to have problems.
Also, I suspect that you will regret doing file operations in the trigger. For one thing, anything which might cause an error in the trigger is going to cause the INSERT operation on PROJECT to get rolled back (and if it was part of a larger operation, that might get rolled back, etc.). That may be how you want it to work, but I doubt it. The file operations are also likely to have a lot more things which might delay them than the database and anything which delays a file operation is going to delay your database transaction. Unless you absolutely need the database and file operations to be synchronous, I suspect that you would be a lot better off using the trigger to create a queue of file operations which are needed and have a separate process which periodically checks the queue for new work and, when it finds entries to process, creates the appropriate directories.
Even if you, ultimately, decide to keep doing the file operations as part of the trigger, you need to address the issue of the trigger not working with multirow inserts.
- Les
-
Dear InoLand,
Thank you for this valuable suggestion.
The insert part will one-by-one only, not multiple insert as this a very crucial part and that's why only one person will have the right to do so and that also one at a time.
Hope, now I may can overcome the issue to some extent?
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
mail2payan (5/10/2013)
Dear InoLand,Thank you for this valuable suggestion.
The insert part will one-by-one only, not multiple insert as this a very crucial part and that's why only one person will have the right to do so and that also one at a time.
Hope, now I may can overcome the issue to some extent?
OK -- if you can keep the input limited to one row at a time, your approach will work. Just keep in mind that it is a limitation of the design. As a compromise, you might want to consider having your trigger check INSERTED for more than one row and failing with an error message if it happens rather than having to track down later why there are entries in your Project table which do not have directories.
- Les
-
Dear Inoland,
I will surely keep the error message. Thanks for the Guidance
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply