processing results from xp_cmdshell "dir /b

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

Import/Export (DTS) and Replication (2000)

processing results from xp_cmdshell "dir /b

Author

Topic

tross
Starting Member

20 Posts

Posted - 2009-03-13 : 08:05:41

I get files ftp'ed to me with date stamps.
I need to have an unattended import ( have built then for single files so I all set there)

I can get the following to return what I need.
declare @SQL_Command char(250)
SET @SQL_Command = 'exec Master.dbo.xp_cmdshell "dir /b \\cronus\y\ftp\inbound\NW_PLCYDTL*.dat "'
EXEC (@SQL_Command)

NW_PLCYDTL_20090305.dat
NW_PLCYDTL_20090310.dat
NW_PLCYDTL_20090311.dat
NULL

but not one at a time.

what do I need to code so the process would be:
1) get list of files to process one at a time ( can get list but not one at a time)
2) rename NW_PLCYDTL_20090305.dat to NW_PLCYDTL ( got this working)
3) import NW_PLCYDTL into sql database ( got this working)
4) repeat step 1,2, 3 till no files left. ( assume that this will be part of answer for step 1 - i.e. if eof ...)

or is there a better option?

TIA

tross
Starting Member

20 Posts

Posted - 2009-03-13 : 14:26:40

found what I needed

SET @SQL_Command = 'exec Master.dbo.xp_cmdshell "dir /b \\cronus\y\ftp\inbound\NW_PLCYDTL*.dat "'

CREATE TABLE #XPOutput (XPLineOut varchar(30))
INSERT INTO #XPOutput EXEC (@SQL_Command)

--- remove junk row
DELETE FROM #XPOutput WHERE XPLineOut IS NULL

.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources