SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 using xp_cmdshell
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TRACEYSQL
Aged Yak Warrior

593 Posts

Posted - 10/13/2007 :  11:10:47  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 10/13/2007 :  11:16:35  Show Profile  Reply with Quote
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

593 Posts

Posted - 10/13/2007 :  11:42:53  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
Not sure i understand how to do what your suggesting - can you advise thanks
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

593 Posts

Posted - 10/13/2007 :  11:44:39  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 10/13/2007 :  13:36:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

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

TRACEYSQL
Aged Yak Warrior

593 Posts

Posted - 10/14/2007 :  07:46:06  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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

593 Posts

Posted - 10/14/2007 :  08:10:21  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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

593 Posts

Posted - 10/14/2007 :  08:59:57  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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

593 Posts

Posted - 10/14/2007 :  09:56:51  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 10/14/2007 :  10:56:10  Show Profile  Reply with Quote
"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

United Kingdom
22431 Posts

Posted - 10/14/2007 :  10:57:34  Show Profile  Reply with Quote
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

593 Posts

Posted - 10/14/2007 :  19:23:40  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 10/14/2007 :  19:27:34  Show Profile  Reply with Quote
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

593 Posts

Posted - 10/17/2007 :  14:05:31  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
I think im on my way....
Thanks for your help.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

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

edpreston76
Starting Member

United Kingdom
1 Posts

Posted - 10/09/2013 :  07:17:00  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/09/2013 :  07:57:31  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000