Posted by AlterEgo on 02/28/07 01:02

utah,

You should paste your stored procedure. One thing, how are you getting from
a one column table (#Dir) to a multiple column table (#files) based upon
your insert? You are going to have to do some parsing to get all this info
into multiple columns.

-- Bill

<Utahduck@hotmail.com> wrote in message
news:1172623203.275737.283660@v33g2000cwv.googlegroups.com...
>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]