Reading a directory
in [Using MS SQL Server]
|
From: Utahduck on 27 Feb 2007 19: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
From: Russ Rose on 28 Feb 2007 00:18 What 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(a)hotmail.com> wrote in message news:1172623203.275737.283660(a)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 >
From: Jack Vamvas on 28 Feb 2007 05:16 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_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://www.itjobfeed.com">UK IT Jobs</a> <Utahduck(a)hotmail.com> wrote in message news:1172623203.275737.283660(a)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 >
From: AlterEgo on 27 Feb 2007 20: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(a)hotmail.com> wrote in message news:1172623203.275737.283660(a)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 >
From: Erland Sommarskog on 28 Feb 2007 17:37 (Utahduck(a)hotmail.com) writes: > 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. > 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(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: SQL Server Link Server Next: how much space to allow for log files?? |