Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create BAT File and execute the same in Trigger Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 5:49 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:12 AM
Points: 84, Visits: 399
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!
----------------------------------------
Post #1451053
Posted Thursday, May 9, 2013 5:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1451056
Posted Thursday, May 9, 2013 6:05 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:12 AM
Points: 84, Visits: 399
I am getting following result of the T-SQL
ID TheOutput
1 nt authority\system
2 NULL
3 NULL
4 Access is denied.
5 NULL


----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------
Post #1451062
Posted Thursday, May 9, 2013 6:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
mail2payan (5/9/2013)
I am getting following result of the T-SQL
ID TheOutput
1 nt authority\system
2 NULL
3 NULL
4 Access is denied.
5 NULL


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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1451066
Posted Thursday, May 9, 2013 6:32 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:12 AM
Points: 84, Visits: 399
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!
----------------------------------------
Post #1451074
Posted Friday, May 10, 2013 12:18 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:12 AM
Points: 84, Visits: 399
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!
----------------------------------------
Post #1451420
Posted Friday, May 10, 2013 10:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:31 PM
Points: 114, Visits: 548
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
Post #1451685
Posted Friday, May 10, 2013 12:35 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:12 AM
Points: 84, Visits: 399
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!
----------------------------------------
Post #1451714
Posted Friday, May 10, 2013 12:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:31 PM
Points: 114, Visits: 548
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
Post #1451716
Posted Friday, May 10, 2013 5:12 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:12 AM
Points: 84, Visits: 399
Dear Inoland,

I will surely keep the error message. Thanks for the Guidance


----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------
Post #1451785
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse