Here is the first part of a script to do that sort of thing but it is a
bit more complex because of my environment but you can get the idea....

Part 1.

--
-- change using replace var with the at sign (shift 2)
--exec uspSiteReportHistory 1049965,'1069623','02/05/2005','03/29/2005'

CREATE PROCEDURE uspSiteReportHistory
(varIPVC_PMCID varchar (10),
varIPVC_SITEID varchar (10)='',
varIPVC_STARTDATE varchar (10)='',
varIPVC_ENDDATE varchar (10)='')
AS
DECLARE varLVC_LOCALPATH varchar (300)
DECLARE varLVC_CMDSTMT varchar (1000)
DECLARE varLVC_DIR varchar (25)
DECLARE varLVC_Path varchar (25)
DECLARE varLVC_DIR_OLD int
DECLARE varLVC_BAKDATE varchar (300)
DECLARE varRPT_Date varchar (300)
DECLARE varLVC_RPT_NBR varchar (300)
DECLARE varLVC_SITE varchar (300)
DECLARE varLVC_RPT_CENTER varchar (300)
DECLARE varLVC_RPT_File varchar (300)
DECLARE varLVC_RPT_FileName varchar (300)
DECLARE varLVC_RMT_PATH varchar (300)
DECLARE varLVC_MAP_PATH varchar (300)

SET NOCOUNT ON

IF varIPVC_STARTDATE IS NULL or varIPVC_STARTDATE=''
SET varIPVC_STARTDATE='01/01/1900'
IF varIPVC_ENDDATE IS NULL or varIPVC_ENDDATE=''
SET varIPVC_ENDDATE= (select convert (varchar (10),getdate(),101))


SELECT varLVC_RMT_PATH =
'\\RPIDALOTCFS.realpage.com\onesitereports\'+varIPVC_PMCID
SELECT varLVC_MAP_PATH = 'NET USE ' + varLVC_RMT_PATH + '
/u:rpidalotca\copybase copy1t'
Execute master.dbo.xp_cmdshell varLVC_MAP_PATH,no_output
--
create table #CHKPMC
(filename varchar (4000))
create TABLE #CHKRPT
(Rpt_ID INT IDENTITY,
filename varchar (4000))
create table #CHKFILES
(Site varchar (7),
RPT_Center varchar (128),
RPT_NBR varchar (128),
RPT_Date varchar (15),
RPT_File varchar (128),
sitename varchar (1000))
create table #CHKREPORTS
(filename varchar (4000))
create table #CHKRPTFILES
(Site varchar (7),
RPT_Center varchar (35),
RPT_NBR varchar (35),
RPT_Date varchar (15),
RPT_ID varchar (35),
RPT_File varchar (35),
sitename varchar (5),
Reptname varchar (4000),
rptlongname varchar (255))
--
select varLVC_CMDSTMT = 'TREE '+varLVC_RMT_PATH+' /A'
Insert #CHKPMC Execute master.dbo.xp_cmdshell varLVC_CMDSTMT
delete from #CHKPMC where substring(filename,1,6) in ('Folder','Volume')
delete from #CHKPMC where substring(filename,2,1) = ':'
delete from #CHKPMC where filename is null
INSERT INTO #CHKRPT SELECT * FROM #CHKPMC
--

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

REAL PAGE INC.
4000 International Parkway
Carrollton, Texas 75007-1913
(972) 820-3432.



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