Reading a directory

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!

-utahWhat I have done in the past is create a global temp table (##Files) and
then in the called procedure(sp_GetFileNames) insert into the global temp
table directly.

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>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
>

|||Maybe I'm bot understanding your problem correctly ,, but if you did
CREATE TABLE #DIR (FileName varchar(100))

Quote:

Originally Posted by

>
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

INSERT INTO myTABLE
SELECT filename FROM #DIR

would that not do the job?

--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>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
>

|||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.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>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
>

|||(Utahduck@.hotmail.com) writes:

Quote:

Originally Posted by

INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
>= '*.txt'

Note that the sp_ prefix is reserved for system procedures, and SQL Server
will first look for these in the master database. Do not use it for your
own code.

Quote:

Originally Posted by

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 have an article on my web site that discusses a couple of alternatives:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx