Pull xp_cmdShell results into Temp Table 1

[Catadmin]

Ack! I know this has been posted before, but when I search, I can't find it.

Can someone either answer this question or point me to the thread (if you can recall it) that shows how to use the xp_cmdshell to read a directory and put the results in a temp table?

Essentially I want a list of files from c:\My file path to be inserted in a temp table. I know the code for the xp_cmdshell part of it, but when I try an Insert statement, QA complains.

The code I'm using is (assume @Directory has been declared and set to the proper 'Dir c:\My file path':

Insert into #Backup(CreateDate, Type, Size, FileName)
(Select xp_CmdShell @Directory)

I've tried it with Exec and w/o Select and with both Exec & Select. Still won't work. Started re-writing it as a Select Into and ran into the same problem.

Help appreciated! Thanks,


Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[hmckillop]

I dont think you can put it all into seperate fields in temp table, as the "dir c:\" will return overall directory information and then file info. (you probably need to parse it after being put into a temp table)

But you can put it in a temp table like this:

create table #vTemp (Myfield varchar(1000))
Insert into #vTemp
exec master..xp_CmdShell 'dir C:\'

select * from #vTemp

drop table #vTemp

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Catadmin]

Exec Master..

That's what I forgot!!

Thanks so muchly, hmckillop! Star for you. @=)

But you're right. It pulls it in as one value instead of seperate columns. Hrmmm. Much work have I to do...



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Catadmin]

AHA!

Just a note for those who might want to do this, my mission was to delete backup files (copied from a production server to our development box) from a directory that were older than a day, so we wouldn't clutter up the hard drive and I wouldn't have to delete the backups manually. I created a job using the following code. The commented out line was my "double-check" to verify I was pulling the right date info and filename info (I commented out the IF statement when I was testing with the SELECT) before actually doing any deletes.

create table #Backup (Filename varchar(250))

Declare @Filename varchar(250), @RemoveFile varchar(100),
@Directory varchar(250), @MyDate datetime, @LongFileName varchar(200)

Set @Directory = '"dir e:\SQL_DB_BAK\MyDB_dev\Restore"'

Insert into #Backup
exec master..xp_cmdshell @Directory

declare BakDel CURSOR for select * from #backup 
where filename like '%MyDB_db_200%'

Open BakDel
fetch next from BakDel into @LongFileName
while @@fetch_Status = 0
Begin

Set @FileName = '"e:\SQL_DB_BAK\myDB_dev\Restore\' + (Select substring(@longFilename, 40, 50) ) + '"'
Set @MyDate = (Select Cast(Substring(@longFileName, 1, 10) as datetime) )

--Select @FileName, @MyDate, DateDiff(dd,@myDate,GetDate())

If DateDiff(dd,@MyDate,GetDate()) > 1
Begin
  Set @RemoveFile = 'Del ' + @FileName
  exec master..xp_cmdShell @RemoveFile
END

Fetch Next from BakDel into @LongFileName

END
Close BakDel
Deallocate BakDel

Drop table #backup

Hope that helps someone in the future.

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???