|
Posted by Utahduck on 02/28/07 00:40
I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
The pivotal step in this is that i run a command that looks like:
CREATE TABLE #DIR (FileName varchar(100))
DECLARE @Cmd varchar(1050)
SET @Cmd = 'DIR "' + @Path + CASE WHEN RIGHT(@Path, 1) = '\' THEN ''
ELSE '\' END + @WildCard + '"'
INSERT INTO #DIR
EXEC master..xp_CmdShell @Cmd
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
....Until I try to put that information into a table while calling that
stored procedure:
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
INSERT INTO #Files
EXEC sp_GetFileNames @Path = '\\isoft2\ftp\Legacy\Billing\', @Wildcard
= '*.txt'
When I run this I get:
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
Because I use an INSERT EXEC to with the results from the @Cmd.
Anybody have any ideas how I can get that information into a table?
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
So, I would appreciate anybody who can help.
Thanks!
-utah
[Back to original message]
|