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 4 7844
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
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
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
(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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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...
|
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...
|
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...
|
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. ...
|
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...
| |
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 :
|
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...
|
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...
|
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...
|
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....
|
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...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |