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.
Author |
Topic |
ADL
Starting Member
1 Post |
Posted - 2012-01-27 : 03:57:04
|
Hi,What is the best way to: 1.Storer folder structure (tree) and values (e.g. user,access rights) against each folder and its cocntents in database?2.Retirve values against each folder and its contents along with associated values (e.g. access rights)My requirement is bit complex:1. I need to store directory structure and its contents along with user access rights.2. And then for each user retrieve values (access rights) for each folder/subfolder and its contents in a fastest way.3. Folder, its subfolder or its contents may have different access rights for user. For example:[Path][User][Read][Write][Upload][Download][C:\Users\Admin] [ADL] [Y][N][N][N] [C:\Users\Admin\Music] [ADL] [Y][N][N][Y] [C:\Users\Admin\Pictures] [ADL] [Y][N][Y][Y][C:\Users\Admin\Pictures\Company.jpg] [ADL] [N][N][N][N][C:\Documents] [ADL] [N][N][N][N] [C:\Documents\Proposal.docx] [ADL] [N][N][N][N] [C:\Documents\Design.docx] [ADL] [Y][N][N][N] [C:\Documents\Requirements.docx] [ADL] [Y][Y][Y][Y] [C:\Documents] [Admin] [Y][Y][Y][Y] [C:\Documents\Proposal.docx] [John] [Y][Y][Y][Y] [C:\Documents\Requirements.docx] [John] [Y][Y][Y][Y] [C:\Documents\Design.docx] [John] [Y][Y][Y][Y] [C:\Documents\Design.docx] [Alex] [Y][Y][Y][Y] Hope this makes it clear. |
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-01-27 : 10:57:42
|
Where do you suppose you would get the user access rights FROM?And I would not store the files in the database since they are already of the file serverHere's something to get directory structuresCREATE PROC [dbo].[isp_Dir] @path varchar(2000) , @Supress int = 0ASSET NOCOUNT ONIF OBJECT_ID('tempdb..#Directory_Contents_Stage') IS NOT NULL DROP TABLE #Directory_Contents_StageCREATE TABLE #Directory_Contents_Stage( [dir] varchar(255) , [dir_output] varchar(255))--TRUNCATE TABLE #Directory_Contents_Stage DECLARE @cmd varchar(4000) SELECT @cmd = 'Dir "' + @path + '"' INSERT INTO #Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd IF EXISTS (SELECT * FROM #Directory_Contents_Stage WHERE dir_output = 'The system cannot find the file specified.') BEGIN PRINT 'The system cannot find the file specified.' GOTO isp_Dir_Error END IF EXISTS (SELECT * FROM #Directory_Contents_Stage WHERE dir_output = 'File Not Found') BEGIN PRINT 'File Not Found' GOTO isp_Dir_Error END IF EXISTS (SELECT * FROM #Directory_Contents_Stage WHERE dir_output = 'The system cannot find the path specified.') BEGIN PRINT 'The system cannot find the path specified.' GOTO isp_Dir_Error END UPDATE #Directory_Contents_Stage SET [dir] = @path DELETE FROM Directory_Contents WHERE [dir] = @path INSERT INTO Directory_Contents (Create_Time, File_Size, [File_Name], [dir], [Struct_Type]) SELECT CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time] , CONVERT(int,LTRIM(RTRIM(REPLACE(SUBSTRING(dir_output,21,19),',','')))) AS [File_Size] , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name] , [dir] , 'FILE' FROM #Directory_Contents_Stage WHERE SUBSTRING(dir_output,1,1) <> ' ' AND (SUBSTRING(dir_output,1,1) <> ' ' AND SUBSTRING(dir_output,25,5) <> CHAR(60)+'Dir'+CHAR(62) ) INSERT INTO Directory_Contents (Create_Time, [File_Name], [dir], [Struct_Type]) SELECT CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time] , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name] , [dir] , CHAR(60)+'Dir'+CHAR(62) AS [Struct_Type] FROM #Directory_Contents_Stage WHERE SUBSTRING(dir_output,25,5) = CHAR(60)+'Dir'+CHAR(62) IF @Supress = 0 SELECT [dir], Create_Time, File_Size, [File_Name], [Struct_Type] FROM Directory_Contents WHERE [dir] = @path ORDER BY [Struct_Type] DESC, [Create_Time] DESCSET NOCOUNT OFFisp_Dir_Exit: IF OBJECT_ID('tempdb..#Directory_Contents_Stage') IS NOT NULL DROP TABLE #Directory_Contents_Stage SET NOCOUNT OFF RETURNisp_Dir_Error:-- Add Error Handling GOTO isp_Dir_ExitGO Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
sakthi87
Starting Member
1 Post |
Posted - 2013-07-31 : 00:31:24
|
Could you please Explain the code. i tried this code. but am getting file not found results.quote: Originally posted by X002548 Where do you suppose you would get the user access rights FROM?And I would not store the files in the database since they are already of the file serverHere's something to get directory structuresCREATE PROC [dbo].[isp_Dir] @path varchar(2000) , @Supress int = 0ASSET NOCOUNT ONIF OBJECT_ID('tempdb..#Directory_Contents_Stage') IS NOT NULL DROP TABLE #Directory_Contents_StageCREATE TABLE #Directory_Contents_Stage( [dir] varchar(255) , [dir_output] varchar(255))--TRUNCATE TABLE #Directory_Contents_Stage DECLARE @cmd varchar(4000) SELECT @cmd = 'Dir "' + @path + '"' INSERT INTO #Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd IF EXISTS (SELECT * FROM #Directory_Contents_Stage WHERE dir_output = 'The system cannot find the file specified.') BEGIN PRINT 'The system cannot find the file specified.' GOTO isp_Dir_Error END IF EXISTS (SELECT * FROM #Directory_Contents_Stage WHERE dir_output = 'File Not Found') BEGIN PRINT 'File Not Found' GOTO isp_Dir_Error END IF EXISTS (SELECT * FROM #Directory_Contents_Stage WHERE dir_output = 'The system cannot find the path specified.') BEGIN PRINT 'The system cannot find the path specified.' GOTO isp_Dir_Error END UPDATE #Directory_Contents_Stage SET [dir] = @path DELETE FROM Directory_Contents WHERE [dir] = @path INSERT INTO Directory_Contents (Create_Time, File_Size, [File_Name], [dir], [Struct_Type]) SELECT CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time] , CONVERT(int,LTRIM(RTRIM(REPLACE(SUBSTRING(dir_output,21,19),',','')))) AS [File_Size] , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name] , [dir] , 'FILE' FROM #Directory_Contents_Stage WHERE SUBSTRING(dir_output,1,1) <> ' ' AND (SUBSTRING(dir_output,1,1) <> ' ' AND SUBSTRING(dir_output,25,5) <> CHAR(60)+'Dir'+CHAR(62) ) INSERT INTO Directory_Contents (Create_Time, [File_Name], [dir], [Struct_Type]) SELECT CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time] , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name] , [dir] , CHAR(60)+'Dir'+CHAR(62) AS [Struct_Type] FROM #Directory_Contents_Stage WHERE SUBSTRING(dir_output,25,5) = CHAR(60)+'Dir'+CHAR(62) IF @Supress = 0 SELECT [dir], Create_Time, File_Size, [File_Name], [Struct_Type] FROM Directory_Contents WHERE [dir] = @path ORDER BY [Struct_Type] DESC, [Create_Time] DESCSET NOCOUNT OFFisp_Dir_Exit: IF OBJECT_ID('tempdb..#Directory_Contents_Stage') IS NOT NULL DROP TABLE #Directory_Contents_Stage SET NOCOUNT OFF RETURNisp_Dir_Error:-- Add Error Handling GOTO isp_Dir_ExitGO Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
|
|
|
|
|
|
|
|