nested cursors in stored procedure

I have a stored procedure that attempts to process some stored data files
located in one or more directories. All the procedure tries to do is walk
through each directory and process (in this case, bulk insert) each file.
I've coded this to use 2 cursors, one to walk the list of directories, the
other to walk the list of files matching some specification. The problem is,
even though the number of files is identified correctly, the same filename
ends up in the cursor value for the file name each time through the loop.
It's as if FETCH NEXT has no affect. The value of @.@.CURSOR_ROWS is also
correct when the second (inner) cursor is opened. The result is the loop
executing the correct number of times, but always on the same filename. It's
as if the cursor is "stuck" on the first value fetched into it.
I realize that using cursors may not be optimal as far as performance, but I
am working with generally a small number of directories (1 - 4) with maybe a
handful of files in each to be processed.
I placed the guts of this procedure into query analyzer as a script and
removed some of the processing and it appears to work perfectly (it cycles
through all the filenames in the directory and then exits). As far as I can
tell the logic matches the examples provided in BOL.
The directory and file specification is retrieved from a table which already
exists in the database. Any advice as to why this isn't working would be
greatly appreciated.
Thanks.
-Gary
IF OBJECT_ID('idw_import_mbs_files') IS NOT NULL
BEGIN
PRINT 'idw_import_mbs_files...'
DROP PROCEDURE idw_import_mbs_files
END
PRINT 'Creating procedure idw_import_mbs_files...'
GO
CREATE PROCEDURE idw_import_mbs_files
@.arg_wideChar bit = 1, -- set to 0 to process ASCII files
@.arg_rows_per_batch int = 10000,
@.arg_max_errors int = 10,
@.debug_output bit = 1
AS
BEGIN
CREATE TABLE #tmpMBSExtractFiles (MBSFileName NVARCHAR(200))
CREATE TABLE #tmpMBSDirectories(mbsDirname NVARCHAR(400))
CREATE TABLE #tmpMBSExtractFileDetails
(
alternate_name CHAR(20),
[size] CHAR(20),
creation_date CHAR(20),
creation_time CHAR(20),
last_written_date CHAR(20),
last_written_time CHAR(20),
last_accessed_date CHAR(20),
last_accessed_time CHAR(20),
attributes CHAR(20)
)
CREATE TABLE #tmpMBSInsert
(
Identifier NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
AssetId INT NULL ,
Billable BIT NULL ,
ContentProvider NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
Genre NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
FF INT NULL ,
Pause INT NULL ,
[Rewind] INT NULL ,
PlayTime INT NULL ,
Price real NULL ,
PurchaseTime NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
RentalTime int NULL ,
ShortTitle NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
Title NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
BillingID INT NULL ,
HomeId INT NULL ,
PurchaseId INT NULL ,
SmartCardId INT NULL ,
EventId NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
PackageId INT NULL ,
Provider NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
)
CREATE TABLE #tmpProcessedFile([FileName] NVARCHAR(255), FileSize INT)
DECLARE @.currentDir NVARCHAR(255)
DECLARE @.currentFile NVARCHAR(255)
DECLARE @.tmpFile NVARCHAR(255)
DECLARE @.SQL NVARCHAR(2000)
DECLARE @.Path NVARCHAR(400)
DECLARE @.FileSpec NVARCHAR(400)
DECLARE @.filecount INT
DECLARE @.filesProcessed INT
DECLARE @.rowsProcessed INT
DECLARE @.status INT
DECLARE @.SQLInsert NVARCHAR(4000)
DECLARE @.useWideChar NVARCHAR(16)
DECLARE @.currentFileSize INT
DECLARE @.fileDateTime NVARCHAR(20)
DECLARE @.fileCreateTime NVARCHAR(8)
DECLARE @.lastAccessed NVARCHAR(20)
DECLARE @.lastAccessedTime NVARCHAR(8)
DECLARE @.processedFileName NVARCHAR(255)
DECLARE @.processedFileSize INT
-- initialize
--
SET @.filesProcessed = 0
SET @.status = 0
IF @.arg_wideChar <> 0
SET @.useWideChar = 'widechar'
ELSE
SET @.useWideChar = 'char'
-- get the export file path
--
SELECT @.Path = StrValue FROM WHA_Settings WHERE SettingName =
'MBSExportDirectory'
-- The directory may be a semi-colon separated list.
--
INSERT INTO #tmpMBSDirectories select * from dbo.idw_str_split(@.Path, ';')
-- get the filespec
--
SELECT @.FileSpec = StrValue FROM WHA_Settings WHERE SettingName =
'MBSExportFilespec'
PRINT 'Importing MBSExtract files from ' + @.currentDir + ' matching
filespec ' + @.FileSpec
-- replace * wildcard to use %
--
SET @.FileSpec = REPLACE(@.FileSpec,'*','%')
DECLARE fileDirCursor CURSOR STATIC FORWARD_ONLY
FOR SELECT * FROM #tmpMBSDirectories
OPEN fileDirCursor
FETCH NEXT FROM fileDirCursor into @.currentDir
WHILE @.@.FETCH_STATUS = 0
BEGIN
OPEN fileNameCursor
FETCH NEXT FROM fileNameCursor INTO @.currentFile
SELECT @.@.CURSOR_ROWS as CursorRows
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.rowsProcessed = 0
PRINT 'Processing file ' + @.currentFile
-- use undocumented extended stored procedure to get the file details
--
INSERT #tmpMBSExtractFileDetails EXEC master..xp_getfiledetails
@.currentFile
SELECT @.currentFileSize = size FROM #tmpMBSExtractFileDetails
SELECT @.fileDateTime = creation_date FROM #tmpMBSExtractFileDetails
SELECT @.fileCreateTime = creation_time FROM #tmpMBSExtractFileDetails
IF @.debug_output <> 0
BEGIN
PRINT 'FileSize: ' + convert(varchar(12), @.currentFileSize)
PRINT 'CreateDate: ' + @.fileDateTime
PRINT 'LastAccessed ' + @.lastAccessed
END
.
.
.
MORE PROCESSING HERE (REMOVED)
.
.
.
next_file:
FETCH NEXT FROM fileNameCursor INTO @.currentFile
IF @.debug_output <> 0
PRINT 'CurrentFile after FETCH NEXT: ' + @.currentFile
END
CLOSE fileNameCursor
DEALLOCATE fileNameCursor
next_dir:
FETCH NEXT FROM fileDirCursor INTO @.currentDir
END
PRINT convert(varchar(12), @.filesProcessed) + ' files processed'
PRINT '***** Process completed ' + CONVERT(VARCHAR(32),GETDATE()) + '
*****'
CLOSE fileDirCursor
DEALLOCATE fileDirCursor
RETURN @.status
END
GOOn Wed, 9 Nov 2005 15:34:43 -0500, Gary wrote:

>I've coded this to use 2 cursors, one to walk the list of directories, the
>other to walk the list of files matching some specification. The problem is
,
>even though the number of files is identified correctly, the same filename
>ends up in the cursor value for the file name each time through the loop.
>It's as if FETCH NEXT has no affect. The value of @.@.CURSOR_ROWS is also
>correct when the second (inner) cursor is opened. The result is the loop
>executing the correct number of times, but always on the same filename. It'
s
>as if the cursor is "stuck" on the first value fetched into it.
Hi Gary,
Your code didn't include the DECLARE CURSOR statement for the inner
cursor (fileNameCursor). If the SELECT statement for that cursor uses a
variable that's read from the outer cursor (fileDirCursor), then you
MUST re-define the inner cursor after each FETCH. The variables used in
a SELECT statement are replaced with their values when you DECLARE the
cursor; they are not re-evaluated when you OPEN the cursor.

>I realize that using cursors may not be optimal as far as performance, but
I
>am working with generally a small number of directories (1 - 4) with maybe
a
>handful of files in each to be processed.
Since file handling is done by the OS at the individual file level,
you'd have to use a cursor anyway. You might be able to rewrite this
with one cursor that joins the tables used as input, but I expect the
speed of accessing the physical files by the OS to be the limiting
factor in a procedure like this, not the speed of the cursor.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo,
I actually do have it in my original source file but when trying to simplify
the code for the post I ended up deleting it.
Just before the inner while loop I have:
DECLARE fileNameCursor CURSOR FORWARD_ONLY STATIC READ_ONLY
FOR SELECT * FROM #tmpMBSExtractFiles
OPEN fileNameCursor
FETCH NEXT FROM fileNameCursor INTO @.currentFile
WHILE @.@.FETCH_STATUS = 0
.
..
.
The fact that it works in a script but not in the stored procedure says to
me that it should work, but I must be doing something to break it...I tried
commented out all the processing in the inner while loop, and the behavior
is consistent...it finds the correct number of files (4), but then prints
out the same name four times, then exists.
Are there any limitations for using cursors in a stored procedure? I'm not
aware of any.
Thanks again,
-Gary
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:cjp4n110l26oha0eigft8nh2oss539rbn9@.
4ax.com...
> On Wed, 9 Nov 2005 15:34:43 -0500, Gary wrote:
>
> Hi Gary,
> Your code didn't include the DECLARE CURSOR statement for the inner
> cursor (fileNameCursor). If the SELECT statement for that cursor uses a
> variable that's read from the outer cursor (fileDirCursor), then you
> MUST re-define the inner cursor after each FETCH. The variables used in
> a SELECT statement are replaced with their values when you DECLARE the
> cursor; they are not re-evaluated when you OPEN the cursor.
>
> Since file handling is done by the OS at the individual file level,
> you'd have to use a cursor anyway. You might be able to rewrite this
> with one cursor that joins the tables used as input, but I expect the
> speed of accessing the physical files by the OS to be the limiting
> factor in a procedure like this, not the speed of the cursor.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 9 Nov 2005 16:45:54 -0500, Gary wrote:

>Thanks Hugo,
>I actually do have it in my original source file but when trying to simplif
y
>the code for the post I ended up deleting it.
>Just before the inner while loop I have:
> DECLARE fileNameCursor CURSOR FORWARD_ONLY STATIC READ_ONLY
> FOR SELECT * FROM #tmpMBSExtractFiles
> OPEN fileNameCursor
> FETCH NEXT FROM fileNameCursor INTO @.currentFile
> WHILE @.@.FETCH_STATUS = 0
Hi Gary,
LOL! Now I knopw how the cursor is defined, yet it doesn't bring me any
further. The script you posted does include a CREATE TABLE statement for
#tmpMBSExtractFiles, but nothing else. I'm sure there must be something,
otherwise you'd get no results at all.

>The fact that it works in a script but not in the stored procedure says to
>me that it should work, but I must be doing something to break it...I tried
>commented out all the processing in the inner while loop, and the behavior
>is consistent...it finds the correct number of files (4), but then prints
>out the same name four times, then exists.
What _exactly_ did you do to verify that it works in a script but not in
a stored procedure? Did you copy all the code and paste from the sp into
a script, then execute that without further modifications? Did you take
a working script, put a "CREATE PROCEDURE xxx AS" in front of it and run
it to create the procedure? Or were there other changes - changes that
might seem irrelevant to you, but that might have caused the stored
procedure to break?

>Are there any limitations for using cursors in a stored procedure? I'm not
>aware of any.
Neither am I.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> What _exactly_ did you do to verify that it works in a script but not in
> a stored procedure? Did you copy all the code and paste from the sp into
> a script, then execute that without further modifications? Did you take
> a working script, put a "CREATE PROCEDURE xxx AS" in front of it and run
> it to create the procedure? Or were there other changes - changes that
> might seem irrelevant to you, but that might have caused the stored
> procedure to break?
YES! One potential would be using a GO command somewhere in the standalone
script, and not realizing that when you put a CREATE PROCEDURE at the
beginning, that the procedure will end at the first GO (assuming the rest of
the code passes the syntax checker).
This can be avoided if you train yourself to use the following as a
template:
CREATE PROCEDURE dbo.name
AS
BEGIN
SET NOCOUNT ON;
-- code here
END
GO
Now if you mistakenly enter a batch separator or some other code that is not
valid inside a procedure, the create procedure will fail, because the BEGIN
and END will not match.|||I found the problem. The loop processing was wacking one of my temp tables
to include all the same values. Cursors work perfectly.
"Gary" <spam@.mail.com> wrote in message
news:evcyzbX5FHA.3312@.TK2MSFTNGP15.phx.gbl...
> Thanks Hugo,
> I actually do have it in my original source file but when trying to
> simplify the code for the post I ended up deleting it.
> Just before the inner while loop I have:
> DECLARE fileNameCursor CURSOR FORWARD_ONLY STATIC READ_ONLY
> FOR SELECT * FROM #tmpMBSExtractFiles
> OPEN fileNameCursor
> FETCH NEXT FROM fileNameCursor INTO @.currentFile
> WHILE @.@.FETCH_STATUS = 0
> .
> ..
> .
> The fact that it works in a script but not in the stored procedure says to
> me that it should work, but I must be doing something to break it...I
> tried commented out all the processing in the inner while loop, and the
> behavior is consistent...it finds the correct number of files (4), but
> then prints out the same name four times, then exists.
> Are there any limitations for using cursors in a stored procedure? I'm not
> aware of any.
> Thanks again,
> -Gary
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:cjp4n110l26oha0eigft8nh2oss539rbn9@.
4ax.com...
>