-- part 2
WHILE (select top 1 Count(filename) from #CHKRPT) > 0
BEGIN
SELECT varLVC_DIR = (select top 1 filename from #CHKRPT)
SELECT varLVC_DIR_OLD = (select top 1 Rpt_ID from #CHKRPT)
IF SUBSTRING(varLVC_DIR,1,4) = '+---'
BEGIN
SELECT varLVC_SITE = SUBSTRING(varLVC_DIR,5,7)
END
IF SUBSTRING(varLVC_DIR,1,8) in ('| +---','| \---')
BEGIN
SELECT varLVC_RPT_CENTER = SUBSTRING(varLVC_DIR,9,64)
END
IF SUBSTRING(varLVC_DIR,1,12) in ('| | \---','|
\---','| +---','| | +---')
BEGIN
SELECT varLVC_RPT_NBR = SUBSTRING(varLVC_DIR,13,64)
INSERT INTO #CHKFILES VALUES (varLVC_SITE, varLVC_RPT_CENTER,
varLVC_RPT_NBR, NULL, varLVC_RPT_FileName, NULL)
--
SELECT varLVC_Path = varLVC_SITE
SELECT varLVC_Path = varLVC_Path + '\' + varLVC_RPT_CENTER
SELECT varLVC_Path = varLVC_Path + '\' + varLVC_RPT_NBR
SELECT varLVC_CMDSTMT = 'DIR /OD '+varLVC_RMT_PATH+'\'+ varLVC_Path
+ '\*.*'
Insert #CHKREPORTS Execute master.dbo.xp_cmdshell varLVC_CMDSTMT
--
DELETE from #CHKREPORTS where substring(filename,1,7) in ('
Volume',' ',' Direct')
DELETE from #CHKREPORTS where substring(filename,40,1) in ('.')
DELETE from #CHKREPORTS where filename is NULL
SELECT varLVC_Path = NULL
--
BEGIN
--
INSERT INTO #CHKRPTFILES (Site, RPT_Center, RPT_NBR, RPT_Date,
RPT_ID, RPT_File, sitename, Reptname,rptlongname )
(SELECT varLVC_SITE, varLVC_RPT_CENTER, varLVC_RPT_NBR,
SUBSTRING(filename,1, 10), SUBSTRING(filename,13, 15),
SUBSTRING(filename,40, 128), varLVC_Path, filename,NULL FROM
#CHKREPORTS)
--
DELETE #CHKREPORTS
END
--
IF SUBSTRING(varLVC_DIR,1,12) in ('| | \---','|
\---')
BEGIN
SELECT varLVC_RPT_NBR = NULL
END
END
DELETE #CHKRPT where Rpt_ID = varLVC_DIR_OLD
END

update #CHKRPTFILES set rptlongname=P.maslongname
from PMCVER240.dbo.SchedulerMasterList P
INNER JOIN #CHKRPTFILES C
on P.MASID=C.RPT_NBR


IF varIPVC_SITEID <>''
BEGIN
SELECT Site , RPT_Center , RPT_NBR , RPT_Date ,RPT_ID , RPT_File,
rptlongname
FROM #CHKRPTFILES
where RPT_Date between varIPVC_STARTDATE and varIPVC_ENDDATE
and site=varIPVC_SITEID
ORDER BY RPT_Date, RPT_ID
END
ELSE
BEGIN
SELECT Site , RPT_Center , RPT_NBR , RPT_Date ,RPT_ID , RPT_File ,
rptlongname
FROM #CHKRPTFILES
where RPT_Date between varIPVC_STARTDATE and varIPVC_ENDDATE
ORDER BY RPT_Date, RPT_ID
END

drop table #CHKPMC
drop table #CHKRPT
drop table #CHKFILES
drop table #CHKREPORTS
drop table #CHKRPTFILES



GO

Tad Huck
{Database Czar - " If you can't learn to do something well, learn to
enjoy doing it poorly. "}



This message is intended only for the use of the individual(s) or entity to which it is addressed and may contain information that is privileged, confidential, and/or proprietary to RealPage and its affiliated companies. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, forwarding or copying of this communication is prohibited without the express permission of the sender. If you have received this communication in error, please notify the sender immediately and delete the original message.

MS Sql Server LazyDBA home page