473,574 Members | 2,639 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_CmdS hell @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\L egacy\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

Feb 28 '07 #1
4 7845
What I have done in the past is create a global temp table (##Files) and
then in the called procedure(sp_Ge tFileNames) insert into the global temp
table directly.

<Ut******@hotma il.comwrote in message
news:11******** **************@ v33g2000cwv.goo glegroups.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_CmdS hell @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\L egacy\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

Feb 28 '07 #2
Maybe I'm bot understanding your problem correctly ,, but if you did
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_CmdS hell @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://www.itjobfeed.c om">UK IT Jobs</a>
<Ut******@hotma il.comwrote in message
news:11******** **************@ v33g2000cwv.goo glegroups.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_CmdS hell @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\L egacy\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

Feb 28 '07 #3
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

<Ut******@hotma il.comwrote in message
news:11******** **************@ v33g2000cwv.goo glegroups.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_CmdS hell @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\L egacy\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

Feb 28 '07 #4
(Ut******@hotma il.com) writes:
INSERT INTO #Files
EXEC sp_GetFileNames @Path = '\\isoft2\ftp\L egacy\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.
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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 28 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5288
by: Joel Goldstick | last post by:
I wanted to write a simple page to let me choose a directory and then list the files in it. The end goal was to make an easy way to copy all the file names in a directory. I tested with Opera7, Mozilla 1.4 and IE6 -- all on windows XP Pro Here is the code. In all three, when I select a file, the complete path is displayed in the file...
1
4369
by: Michael Palmer | last post by:
I'm reading xml from SQL Server 2K with VB.net using an XSD schema file and SQLXML 3.0. I have the below code working fine, but I'd like to change the code from reading the schema file from a directory to reading the schema as an embedded resource. Here's my current code, what would I need to change?? Dim strm As Stream Dim strmReader As...
3
3752
by: Nick | last post by:
Is it possible to read a list of files from a specified directory using VB.net We have company intranet and I have created a page that displays photos from different events. I have coded a page that display images from a particular directory and named the files Pic1.jpg, Pic2.jpg etc. My code then cycles through the images and displays...
4
1885
by: William Sullivan | last post by:
I have an application that pulls images from a physical drive directory, creates a PDF of the image, caches the image, and returns a link to the client (using ajax, but that's not the problem). I'm faced with having to do this without knowing where this PDF cache location is. It may be on the local drive, or it may be on a network share. ...
4
8360
by: Jason Kumpf | last post by:
OK I've been staring at this code all day and still with everything I have tried I cannot figure out two problems I am having. Once is why the space limit for the directory I create in the code fails. Second, why the data reader is reading every other record. Here is all of the source code for my little application followed by the contents...
4
1482
by: Craig Vermeer | last post by:
Hi All, I have a program that's using the file system as a queuing mechanism, and it's consuming an inordinate amount of CPU time when the file system queue gets all that large (any more than a thousand or so messages in the queue). I know why. It's because my code to grab a single message off the queue looks something like this :
1
1358
by: farseer | last post by:
Hi, I am storing certain files in the same directory as an asp page. I'd like for that page to be able to read read the names of the files in that directory with a certain extension and create a hyperlink to them. How can i do this. as an example... let's say my page resides at /mysite/dir1/default.aspx. now in /mysite/dir1 are the files...
4
20725
waynetheengineer
by: waynetheengineer | last post by:
Hello everyone :) I am trying to write VB code for reading filenames and file property values in a specific directory. For example, I have a directory called C:/Bears and in that directory are three files: Brown Bears.txt, Black Bears.txt, and Orange Bears.txt. Each of these files have text descriptions in both the Title property field...
2
3840
by: supra91 | last post by:
Hi, I have to read files from the parent directory and delete those files. This parent directory contains logs files. This directory also contains some sub directories and I do not want to delete files from these sub directories. I am using this code opendir(DIR, $lpath); @flist = readdir(DIR); closedir(DIR); when I do this @flist...
4
7261
by: Laharl | last post by:
My Operating Systems professor has assigned homework that basically boils down to implementing ls -lra, but with a different output format. In other words, list the files and subdirectories (and a bit of data about them, for the files) in the current directory and its subdirectories using system calls. Unfortunately, it isn't working quite right....
0
7813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8066
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8249
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7826
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
5305
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3755
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2251
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1350
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1066
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.