January 8, 2014 at 8:51 am
I am working on a procedure that will check directories using `xp_DirTree` to confirm that the correct number of files have been generated by an earlier process.
Unfortunately my query (the last part) is giving incorrect results
declare @DateOffset int
set @DateOffset=6
-- Create a table variable to store user data
DECLARE @myTable TABLE
(
docID INT IDENTITY(1,1),
docRef VARCHAR(50),
YPTMPID varchar(3),
saveDir VARCHAR(4000),
totalLettersExpected int,
actualLetters int
);
insert @myTable SELECT docRef, YPTMPID,
saveDir=max(Save_Directory) + cast(YEAR(GETDATE()-@DateOffset) as varchar(4)) + '\' + datename(month, GETDATE()-@DateOffset) + '\'+SUBSTRING(CONVERT(CHAR(20), GETDATE()-@DateOffset, 101),4, 2) + '.' + LEFT(CONVERT(CHAR(20), GETDATE()-@DateOffset, 101), 2)
+ '.' + SUBSTRING(CONVERT(CHAR(20), GETDATE()-@DateOffset, 101),7, 4),
COUNT(*) as 'Total Letters', null
FROM [alpsMaster].[dbo].[uExtractMonitor]
group by docRef, YPTMPID
order by 1,2
select * from @myTable
-- Get the number of rows in the looping table
DECLARE @RowCount INT, @SQL nvarchar(4000), @LoopSQL nvarchar(4000), @Date varchar(20)
set @Date=rtrim(CONVERT( CHAR(12), getDate()-@DateOffset, 106)) --'29 Oct 2013'
SET @RowCount = (SELECT COUNT(docID) FROM @myTable)
-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1
-- Loop through the rows of table @myTable, and for each docRef, check the file directory for the correct number of files
WHILE (@I <= @RowCount)
BEGIN
DECLARE @docRef VARCHAR(10), @saveDir VARCHAR(500), @letterCount int
-- Get the data from table and set to variables
SELECT @docRef = docref FROM @myTable WHERE docID = @I
SELECT @saveDir = saveDir FROM @myTable WHERE docID = @I
update @myTable set actualLetters = 0 where docRef=@docRef
create table #files (subdirectory varchar(100), depth int, [file] int)
insert into #files EXEC master.sys.xp_dirtree @saveDir,0,1;
select @letterCount = COUNT(*) from #files
drop table #files
update @myTable set actualLetters = @letterCount where docRef=@docRef
-- Increment the iterator
SET @I = @I + 1
END
select * from @myTable
Unfortunately in my last WHILE loop something is going wrong. The numbers put in the actualLetters column should match the column totalLettersExpected
On the network the number of files does match the column *totalLettersExpected* so where have I gone wrong, and is there a better, more robust way of doing this?
thanks
January 8, 2014 at 12:15 pm
Do you have any DDL/Sample data for [alpsMaster].[dbo].[uExtractMonitor]? ... and some sample data for what will go into files? If you can provide that I am sure we can help you.
From what I can see thusfar, this can be done without all the t-sql date gymnastics and without a loop (which is certainly the way to go;-) )
-- Itzik Ben-Gan 2001
January 8, 2014 at 1:26 pm
xp_DirTree returns the names of files AND directories. If you want to count just the files, then you have to include the "secret" 3rd operand for xp_DirTree and then count only the "files" in the temp table.
insert into #files EXEC master.sys.xp_dirtree @saveDir,0,1[font="Arial Black"],1[/font];
Yeah... you're going to need to add an extra column to the temp table to do this.
--Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply