July 21, 2021 at 7:09 pm
Hi,
If I run this in SSMS it returns 7000 file names (works great):
-- ##############################################################################
-- BEGIN - Dump entire file tree of the documents folder into a SQL table
-- ##############################################################################
IF OBJECT_ID(N'tempdb..#dirs') IS NOT NULL
BEGIN
DROP TABLE #dirs;
END;
IF OBJECT_ID(N'tempdb..#directoryTree') IS NOT NULL
BEGIN
DROP TABLE #directoryTree;
END;
DECLARE
@BasePath VARCHAR (1000)
, @Path VARCHAR (1000)
, @FullPath VARCHAR (2000)
, @Id INT;
--This is your starting point.
SET @BasePath = '\\balhblah.net\ns\public\Documents\Letter\LOAs\';
--Create a temp table to hold the results.
IF OBJECT_ID( 'tempdb.. #DirectoryTree' ) IS NOT NULL
DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (
id INT IDENTITY (1, 1) PRIMARY key
, fullpath VARCHAR (2000)
, subdirectory NVARCHAR (512)
, depth INT
, isfile BIT
, ParentDirectory INT --JIF 2020-09-11
, flag TINYINT DEFAULT(0)
);
--Populate the table using the initial base path.
INSERT #DirectoryTree ( subdirectory, depth , isfile )
-- jpw 2020 10 26 - trying nodeb... not reliable to use stageB
EXEC sqlnodeb.master.sys.xp_dirtree @BasePath, 0, 1;
PRINT '# rows from DirTree: ' + CAST(@@ROWCOUNT AS VARCHAR(500))
DELETE FROM servenameb.TestOutput
INSERT INTO sqlnodeb.pecDevSupport.jwenzlick.TestOutput
(
fullpath
, subdirectory
, depth
, isfile
, ParentDirectory
, flag
)
SELECT fullpath, subdirectory, depth, isfile, ParentDirectory, flag FROM #DirectoryTree
UPDATE #DirectoryTree
SET ParentDirectory = (
SELECT MAX(ID) FROM #DirectoryTree
WHERE DEPTH = d.Depth - 1 AND ID < d.Id)
FROM #DirectoryTree d;
-- SEE all with full paths
WITH dirs AS (
SELECT
ID,subdirectory,DEPTH,isfile,ParentDirectory,flag
, CAST (NULL AS NVARCHAR(MAX)) AS CONTAINER
, CAST([subdirectory] AS NVARCHAR(MAX)) AS dpath
FROM #DirectoryTree
WHERE ParentDirectory IS NULL
UNION ALL
SELECT
d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag
, dpath AS CONTAINER
, dpath +'\'+d.[subdirectory]
FROM #DirectoryTree AS d
INNER JOIN dirs ON d.ParentDirectory = dirs.id
)
SELECT * INTO #dirs FROM dirs;
UPDATE #DirectoryTree SET fullpath = @BasePath
FROM #DirectoryTree
JOIN #dirs ON #dirs.id = #DirectoryTree.id
WHERE #dirs.depth = 1 AND #dirs.isfile = 1 AND #dirs.ParentDirectory IS NULL;
UPDATE #DirectoryTree SET fullpath = @BasePath + dpath
FROM #DirectoryTree
JOIN #dirs ON #dirs.id = #DirectoryTree.id
WHERE #dirs.depth > 1 AND #dirs.isfile = 1 AND #dirs.ParentDirectory IS NOT NULL;
/*
UPDATE #DirectoryTree SET fullpath = @BasePath;
--Loop through the table as long as there are still folders to process.
WHILE EXISTS
(
SELECT id FROM #DirectoryTree WHERE isfile = 0
)
BEGIN
--Select the first row that is a folder.
SELECT
TOP(1)
@Id = id
, @FullPath = fullpath
, @Path = @BasePath + '' + subdirectory + '\'
FROM #DirectoryTree
WHERE isfile = 0;
IF @FullPath = @Path
BEGIN
--Do this section if the we are still in the same folder.
INSERT #DirectoryTree
(
subdirectory
, depth
, isfile
)
EXEC master.sys.xp_dirtree @Path, 0, 1;
UPDATE #DirectoryTree SET fullpath = @Path WHERE
fullpath IS NULL;
--Delete the processed folder.
DELETE FROM #DirectoryTree WHERE id = @Id;
END;
ELSE
BEGIN
--Do this section if we need to jump down into another subfolder.
SET @BasePath = @FullPath;
--Select the first row that is a folder.
SELECT
TOP(1)
@Id = id
, @FullPath = fullpath
, @Path = @BasePath + '' + subdirectory + '\'
FROM #DirectoryTree
WHERE isfile = 0;
INSERT #DirectoryTree
(
subdirectory
, depth
, isfile
)
EXEC master.sys.xp_dirtree @Path,0, 1;
UPDATE #DirectoryTree SET fullpath = @Path WHERE
fullpath IS NULL;
--Delete the processed folder.
DELETE FROM #DirectoryTree WHERE id = @Id;
END;
END;
*/
-- ##############################################################################
-- END - Dump entire file tree of the documents folder into a SQL table
-- ##############################################################################
IF OBJECT_ID(N'tempdb..#authDocs') IS NOT NULL
BEGIN
DROP TABLE #authDocs;
END;
-- now that we have the full file directory - lets find only documents that actually have auth numbers in the file name
SELECT
id, fullpath, subdirectory,
SUBSTRING(subdirectory, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][a-Z0-9][a-Z0-9][a-Z0-9][0-9][0-9][0-9][0-9]%', subdirectory), 16) authNum
INTO #authDocs
FROM
#DirectoryTree
WHERE
subdirectory LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][a-Z0-9][a-Z0-9][a-Z0-9][0-9][0-9][0-9][0-9]%'
AND
LEFT(subdirectory, 1) <> '~';
UPDATE #authDocs
SET authNum = REPLACE( REPLACE(authNum, '.',''), '-','')
FROM #authDocs
WHERE RIGHT(authNum,1) = '.' OR RIGHT(authNum,1) = '-';
-- jpw 2021 01 15 - pulling max value per ref ID now, rather than arbitrary assignment
--UPDATE pec.tReferralPreCalc
--SET rpcSCAFilePath = jFullFilePaths.fullFilePath --JIF 2020-09-15 removed + subdirectory (jpw 2021 01 15 - adding it back, conditionally)
SELECT jFullFilePaths.referral_id
FROM pec.tReferralPreCalc
JOIN dbo.referral ON
referral.referral_id = tReferralPreCalc.referral_id
JOIN pec_prod.dbo.referral_status ON
referral_status.referral_status_id = referral.referral_status_id
JOIN
(
SELECT
MAX(
IIF(RIGHT( fullpath, 1) = '\' AND subdirectory IS NOT NULL, fullpath + subdirectory, fullpath)
) fullFilePath,
referral.referral_id
FROM pec.tReferralPreCalc
JOIN dbo.referral ON
referral.referral_id = tReferralPreCalc.referral_id
JOIN pec_prod.dbo.referral_status ON
referral_status.referral_status_id = referral.referral_status_id
JOIN #authDocs ON
authNum = referral.referral_ud
GROUP BY referral.referral_id
) jFullFilePaths ON
jFullFilePaths.referral_id = tReferralPreCalc.referral_id
WHERE
rpcContractStatus = 'SCA'
AND
referral_status_ud = 'APPROVED'
AND
COALESCE(rpcSCAFilePath, 'x') <> COALESCE(jFullFilePaths.fullFilePath, 'x') --JIF 2020-09-15 changed, the full path has everything
--COALESCE(rpcSCAFilePath, 'x') <> COALESCE(fullpath + subdirectory, 'x')
I then setup a 'test' job and run it and get - zero records!!
Pretty sure it has something to do with permissions and using xp_dirtree but I'm stuck and not sure what to check next.
Thanks- this is driving me crazy.
July 21, 2021 at 7:57 pm
if it's sql agent job, then make sure the service account that is starting the SQL Agent service has the proper permissions. Also, try putting in a try/catch block to see what the error is
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply