June 16, 2011 at 11:17 pm
Hello all,
I am having a problem:
I have two tables:
MAIN_TABLE
---------------------------------------------------
ID | Title | Link
---------------------------------------------------
1 | air11 | \\server1\FILE_MANAGEMENT\air\air11
2 | air12 | \\server1\FILE_MANAGEMENT\air\air12
3 | air13 | \\server1\FILE_MANAGEMENT\air\air13
---------------------------------------------------
SUB_TABLE
--------------------------
ID | TitleID | FileName
--------------------------
1 | 1 | a1.dwg
2 | 1 | a2.dwg
3 | 2 | a5.dwg
4 | 3 | a3.dwg
--------------------------
Now let me explain my problem, in the MAIN_TABLE, whenever I'll to insert TITLE & LINK, a procedure or a trigger should run to fill up the SUB_TABLE with the list of files provided in the LINK column of MAIN_TABLE. The LINK column basically contains the path of a directory, where there contains multiple files.
I came to know how to generate directory listing from the following link:
http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-create-directory.html
But unable to understand to get file listing.
Hope I can make you understand my query!
Can you please help me on this?
Regards,
Daipayan
Software Programmer
June 17, 2011 at 3:58 am
June 17, 2011 at 4:06 am
Hello MidBar,
Am using MS SQL Server 2005 and I know about xp_cmdshell and sp_ListFiles:
exec xp_dirtree '\\server1\FILE_MANAGEMENT\air\air11', 0, 1
create table #BACKUPS
(
subdirectory varchar(255),
depth int,
isfile int
)
INSERT #BACKUPS exec xp_dirtree '\\server1\FILE_MANAGEMENT\air\air11', 0, 1
SELECT * FROM #BACKUPS
I know how to generate the File list, but here my problem is instead of typing the location manually, I want to pull the destination from the LINK column of MAIN_TABLE, which I am unable to understand how to do it and further also want to pull the MAIN_TABLE ID as a foreign key in the SUB_TABLE..:ermm:
June 17, 2011 at 5:13 am
Hi,
I have used the example metioned above. Hopefully, this will give you some idea to solve your problem. Ofcourse we can change/extend this simple logic further.
USE tempdb
GO
IF OBJECT_ID('#main_table') is not null DROP TABLE #main_table
IF OBJECT_ID('#sub_table') is not null DROP TABLE #sub_table
SET NOCOUNT ON
CREATE TABLE #main_table
(
main_id int identity(1,1),
title varchar(500),
link varchar(968),
command varchar(56),
is_processed bit,
exec_cmd as (command+link)
)
CREATE TABLE #sub_table
(
sub_id int identity(1,1),
main_id int,
sub_file_name varchar(1024)
)
INSERT INTO #main_table (title, link, command, is_processed) VALUES ('Title1', '\\server1\FILE_MANAGEMENT\air\air11', 'dir /b ', 0) -- Change Path Accordingly
INSERT INTO #main_table (title, link, command, is_processed) VALUES ('Title2', '\\server2\FILE_MANAGEMENT\air\air11', 'dir /b ', 0) -- Change Path Accordingly
INSERT INTO #main_table (title, link, command, is_processed) VALUES ('Title3', 'C:\WINDOWS\', 'dir /b ', 0)
DECLARE @FilesInAFolder TABLE (FileNamesWithFolder VARCHAR(500))
WHILE (select COUNT(1) from #main_table where is_processed=0) > 0
begin
DECLARE @Command VARCHAR(1024), @v_main_id int
select top 1 @v_main_id=main_id, @Command=exec_cmd from #main_table where is_processed = 0
INSERT INTO @FilesInAFolder
EXEC MASTER..xp_cmdshell @Command
INSERT INTO #sub_table(main_id, sub_file_name)
SELECT @v_main_id, FileNamesWithFolder
FROM @FilesInAFolder
WHERE FileNamesWithFolder is not null
UPDATE #main_table
SETis_processed = 1
WHERE main_id = @v_main_id
DELETE FROM@FilesInAFolder
END
GO
SELECT * FROM #main_table
SELECT * FROM #sub_table
DROP TABLE #main_table
DROP TABLE #sub_table
For the newbies, please use following code to enable xp_cmdshell.
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure xp_cmdshell,1
GO
RECONFIGURE
Cheers.
June 17, 2011 at 5:28 am
Thank You Sir.
I have 2 Query on this:
1: This T-SQL is not accessible in network, works only in Local Machine, if I tried with local path, I am getting "Access Denied" stored in sub_file_name column
2: Can I automate this process of T-SQL through trigger??
June 17, 2011 at 5:48 am
Hmmm...
1: I have tested this with network paths and on local and works fine with me. May be you dont have necessary permission to read the file from network locations.
2: Yes please give it a try in trigger.
Cheers.
June 18, 2011 at 10:01 am
mail2payan (6/17/2011)
Thank You Sir.1: This T-SQL is not accessible in network, works only in Local Machine, if I tried with local path, I am getting "Access Denied" stored in sub_file_name column
I don't know if this is your problem or not but just to be sure...
"Local Path" for the server is the physical drives on the server itself. If it needs to "see" something else, you have to use a UNC path and the account that SQL Server logs in as must have the privs to be able to "see" that UNC path.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2011 at 11:51 pm
Dear MidBar; Jeff Moden,
Thanks for quick reply.
I tried, but not working, but when am using the following procedure: exec xp_dirtree '\\server1\FILE_MANAGEMENT\air\air11', 0, 1, it's working properly, why is it so??
June 26, 2011 at 5:05 pm
mail2payan (6/19/2011)
Dear MidBar; Jeff Moden,Thanks for quick reply.
I tried, but not working, but when am using the following procedure:
exec xp_dirtree '\\server1\FILE_MANAGEMENT\air\air11', 0, 1, it's working properly, why is it so??
Apologies for the week long delay. I lot track of this post.
Can SQL Server actuall "see" the path you've used? If not, you may need to setup a share on \\server1 and grant SQL Server the rights to read whatever share you setup.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2011 at 10:26 pm
This is actual path of one of the sub-folder:
\\pinnacleserver1\FILE MANAGEMENT\asg\asg1
Root Folder is: \\pinnacleserver1\FILE MANAGEMENT
How to set share access policies in MS SQL Server??
June 27, 2011 at 5:52 am
mail2payan (6/26/2011)
How to set share access policies in MS SQL Server??
You don't. You have to set the share at the OS level and the share needs to be for whatever login SQL Server uses for a login.
If you have an OPs department, ask them for some help on this.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2011 at 7:16 am
Ok sir, I'll do it.
Thanks again!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply