Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 using xp_cmdshell

Author  Topic 

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-10-13 : 11:10:47
Im running this statement.
CREATE TABLE #fileList(line varchar(2000))
INSERT INTO #fileList
EXEC xp_cmdshell 'dir C:\Company /B'
SELECT * FROM #fileList WHERE line IS NOT NULL ORDER BY 1
DROP TABLE #fileList

What i would like to get is the date modified

Thanks

Kristen
Test

22859 Posts

Posted - 2007-10-13 : 11:16:35
Use a SUBSTRING on the [line] column, and then convert that the DATETIME datatype.

Position within the line will vary depending on version of windows, and the locale will influence the presentation of the date.

Kristen
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-10-13 : 11:42:53
Not sure i understand how to do what your suggesting - can you advise thanks
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-10-13 : 11:44:39
When i run the command i do not get created date coming out just the filename
company1.txt and no dates for commands

say i have c:\company
company1.txt 10/1/2007
company2.txt 11/1/2007

When i run command i get company1
company2

Thanks
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-13 : 13:36:09
That's the "/B" on the end of your DIR command, that means to return it "bare" - i.e. just filenames.

dir C:\Company

will show you dates.

Kristen
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-13 : 21:56:06
How can you get modified date if only queried file name?
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-10-14 : 07:46:06
Removed the B Now i can do the substring on it....

For example im just querying C drive on one system here.
Just did dir c:\*.txt

Get the following:

0 Dir(s) 2,787,573,760 bytes free
1 File(s) 63 bytes
Directory of C: Volume in drive C has no label.
Volume Serial Number is 7C93-8DAC
03/16/2007 09:48 AM 63 sysinfo.txt

Is there a way just to get say
03/16/2007 09:48 AM 63 sysinfo.txt

Without all the volume information.

I tried to research the commands xp_cmdshell what the parameters were did not find much out there on net.

Thanks
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-10-14 : 08:10:21
I just found something
http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

There is one called spFileDetails it gives the date modified.

So i use a bit of both one to create the files in a table then loop around this procedure.

Thanks

Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-10-14 : 08:59:57
Got stuck on one part of coding.

Files on C are

01
te.txt
xp.txt
02
te.txt
xa.txt



--This gets me 01, 02
CREATE TABLE COMPANY(Company nvarchar(20))
INSERT INTO COMPANY
EXEC xp_cmdshell 'dir x:\*.* /B'

CREATE TABLE COMPANY_FILES(Company nvarchar(20), FILENAME nvarchar(20))
BEGIN
DECLARE @Company nvarchar(20)
DECLARE @CMD nvarchar(1000)
DECLARE ReadDirectory CURSOR FOR SELECT Company
FROM COMPANY

OPEN ReadDirectory

FETCH NEXT FROM ReadDirectory INTO @Company

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO COMPANY_FILES(COMPANY, FILENAME)
VALUES (@COMPANY, @FILENAME)
set @cmd = 'EXEC xp_cmdshell ' + 'dir xc:\' + @COMPANY + ' /B'
EXEC @cmd
----Got stuck on trying to do the dynamic bit s i can put in 01 te.txt and 01 xp.txt


print @FileName

FETCH NEXT FROM ReadDirectory INTO @Company

END

CLOSE ReadDirectory
DEALLOCATE ReadDirectory


END
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-10-14 : 09:56:51
Almost there...
DROP TABLE COMPANY
DROP TABLE COMPANY_FILES

CREATE TABLE COMPANY(Company nvarchar(20))
INSERT INTO COMPANY
EXEC xp_cmdshell 'dir x:\*.* /B'

CREATE TABLE COMPANY_FILES(Company nvarchar(20), FILENAME nvarchar(20))
BEGIN
DECLARE @Company nvarchar(20)
DECLARE @CMD nvarchar(1000)
DECLARE @FILENAME nvarchar(1000)
DECLARE ReadDirectory CURSOR FOR SELECT Company
FROM COMPANY

OPEN ReadDirectory

FETCH NEXT FROM ReadDirectory INTO @Company

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DIRECTORY NVARCHAR(100)
SET @DIRECTORY = 'x:\'
INSERT INTO COMPANY_FILES(COMPANY, FILENAME)
VALUES (@COMPANY, @cmd)
set @cmd = 'DIR ' + @DIRECTORY + @COMPANY + ' /B'
EXEC XP_CMDSHELL @cmd

When i do the print for @CMD it has all the files i want i just cannot get this to write into the COMPANY_FILES table as 01 te.txt etc

FETCH NEXT FROM ReadDirectory INTO @Company

END

CLOSE ReadDirectory
DEALLOCATE ReadDirectory


END




seLECT * FROM COMPANY_FILES
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-14 : 10:56:10
"Is there a way just to get say
...
Without all the volume information.
"

We DELETE them using a WHERE [Line] LIKE '%this%' OR [Line] LIKE '%that%' ...

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-14 : 10:57:34
Actually a WHERE NOT LIKE

"space space space ... [0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9] space ... space [^ ] ..."

would do - so you only let through the lines that have date-like values

Kristen
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-10-14 : 19:23:40
Thanks for replying do you know why

INSERT INTO COMPANY_FILES(COMPANY, FILENAME)
VALUES (@COMPANY, @cmd)
set @cmd = 'DIR ' + @DIRECTORY + @COMPANY + ' /B'
EXEC XP_CMDSHELL @cmd

When i do above part it would not write to the table if i do print it has everything correct just not updating table...the Company is coming out just not the txt names.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-14 : 19:27:34
You need to use something more like the syntax you had at the outset, i.e.:

INSERT INTO #fileList
EXEC xp_cmdshell 'dir C:\Company'

Kristen
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-10-17 : 14:05:31
I think im on my way....
Thanks for your help.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 14:32:03
OK, I'll keep my fingers crossed!
Go to Top of Page

edpreston76
Starting Member

1 Post

Posted - 2013-10-09 : 07:17:00
Hi,

I am also trying to get the date modified of a external file into a temp file and have got the following so far using your answers....any help would be appreciated. the delete line is where I am really struggling as I only want the date modified - doesnt even have to have the time..


CREATE TABLE #filedate(line varchar(2000))
INSERT INTO #filedate
EXEC xp_cmdshell 'dir \\FILELOCATION\FILENAME.dat'
DELETE FROM #filedate where line not like "[0-9][0-9/[09][0-9]/[0-9][0-9][0-9][0-9]"
SELECT * FROM #filedate WHERE line IS NOT NULL ORDER BY 1

--DROP TABLE #filedate
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 07:57:31
please dont hijack old threads. Post the question as a new thread and chances are that you'll get solution much faster.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -