Cursor not Fetching all the rows?
-
Hi, I am using SQL 2000 query analyzer.
I have created a cursor selecting from a Temp table #A that has 200 rows. In the cursor main body I do not have any joined tables just hard coded syntax + the variables value that the cursor feed in and insert into another temp table #B. After the cursor finish running I only have 95 rows of data in my temp table #B. Do you know why some rows are missing?
-
Please could you post the code here.
-
PCQ,
It's kinda like asking us to fix your car without seeing the car. Please post the offending code and table structure along with maybe some sample data
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is my code below.. #TableA got 200 rows. After I finished the cursor #TableB got only 90 rows. Please advise. Thanks.
DECLARE X_Curs CURSOR LOCAL FAST_FORWARD for
SELECT Pcode,Prtl, StNo,cID,ScID,TARM,
SOV,MKID,SjStat,SDate,FTX,CUID
FROM #TableA --where MKID =158
order by Pcode,mtype,MKID
Open X_Curs
FETCH NEXT FROM X_Curs into @strPcode,@strPrtl,@StNo,@cID,@ScID,@strTARM,
@strSOV,@MKID,@strSjStat,@strSDate,@strFTX,@CUID
WHILE @@FETCH_stat = 0 BEGIN
---CREATE SFRea CHILD ELEMENT
Select top 1 @TUID = Re.LU_UID from #TableA T ,#X_REAS Re
where Re.Pcode = T.Pcode and Re.VID = T.MKID and Re.VCode ='SCF'
and Re.Pcode = @strPcode and Re.VID = @MKID
SELECT @strXText =
CASE Re.LU_UID WHEN @TUID THEN
'<SFRea>'+ISNULL(RTRIM(Re.CT_REA), '')+'</SFRea>'
ELSE
@strXText + '<SFRea>'+ISNULL(RTRIM(Re.CT_REA), '')+'</SFRea>'
END
from #X_REAS Re, #TableA T
where Re.Pcode = T.Pcode and Re.VID = T.MKID and Re.VCode ='SCF'
and Re.Pcode = @strPcode and Re.VID = @MKID
Group by Re.Pcode,Re.VID,Re.LU_UID,Re.CT_REA ORDER BY Re.LU_UID ASC
insert into #X_REAS2( Pcode,MKID,X_SCRF)
select Re.Pcode,T.MKID, @strXText
from #X_REAS Re, #TableA T
where Re.Pcode = T.Pcode and Re.VID = T.MKID and Re.VCode ='SCF'
and Re.Pcode = @strPcode and Re.VID = @MKID
---CREATE withReas CHILD ELEMENT
Select top 1 @TUID = Re.LU_UID from #TableA T ,#X_REAS Re
where Re.Pcode = T.Pcode and Re.VID = T.MKID and Re.VCode ='DIS'
and Re.Pcode = @strPcode and Re.VID = @MKID
SELECT @strXText =
CASE Re.LU_UID WHEN @TUID THEN
'<withRea>'+ISNULL(RTRIM(Re.CT_REA), '')+'</withRea>'
ELSE
@strXText + '<withRea>'+ISNULL(RTRIM(Re.CT_REA), '')+'</withRea>'
END
from #X_REAS Re, #TableA T
where Re.Pcode = T.Pcode and Re.VID = T.MKID and Re.VCode ='DIS'
and Re.Pcode = @strPcode and Re.VID = @MKID
Group by Re.Pcode,Re.VID,Re.LU_UID,Re.CT_REA ORDER BY Re.LU_UID ASC
insert into #X_REAS2( Pcode,MKID,X_SCRF)
select Re.Pcode,T.MKID, @strXText
from #X_REAS Re, #TableA T
where Re.Pcode = T.Pcode and Re.VID = T.MKID and Re.VCode ='DIS'
and Re.Pcode = @strPcode and Re.VID = @MKID
if @strFTX IS NULL Begin set @strFTX ='''<fTx></fTx>''' end
Else if @strFTX IS NOT NULL Begin set @strFTX ='''<fTx>'+@strFTX+'</fTx>''' end
SET @strSQLX =
'INSERT INTO #TableB(Pcode,XText1,XText3,XText5,MKID,CUID) '
+ 'Select '+''''+@strPcode+''''+ ','
+''''+ '<SujInfo>'+ ''''+ '+'
+''''+ '<SujInfoKey>'+ ''''+ '+'
+''''+'<studyNumber>'+@strPrtl+'</studyNumber>'+''''+'+'
+''''+'<siteNumber>'+@StNo+'</siteNumber>'+''''+'+'
+''''+'<cID>'+@cID+'</cID>'+''''+'+'
+''''+'</SujInfoKey>'+''''+'+'
+''''+'<SujInfoAttributes>'+''''+'+'
+''''+'<ScID>'+@ScID+'</ScID>'+''''+'+'
+''''+'<TARM>'+@strTARM+'</TARM>'+''''+'+'
+''''+'<SOV>'+@strSOV+'</SOV>'+''''+'+'
+''''+'<SFReas>'+''''+','
+''''+'</SFReas>'+''''+'+'
+''''+'<withReas>'+''''+','
+''''+'</withReas>'+''''+'+'
+''''+'<stat>'+@strSjStat+'</stat>'+''''+'+'
+''''+'<SDate>'+@strSDate+'</SDate>'+''''+'+'
+''''+'</SujInfoAttributes>'+''''+'+'
+''''+'</SujInfo>'+''''+','+''''+@MKID+''''+','+''''+@CUID+''''
Print (@strSQLX)
EXEC (@strSQLX)
FETCH NEXT FROM X_Curs INTO @strPcode,@strPrtl,@StNo,@cID,@ScID,@strTARM,
@strSOV,@MKID,@strSjStat,@strSDate,@strFTX,@CUID
END
CLOSE X_Curs
DEALLOCATE X_Curs
-
Well, I count at least 6 inner joins in that cursor, and without the data, it's hard to know where they're being lost. Can you at least post the Print(@strSQLX) results from a few passes?
-
Here is the (@strSQLX) results below
INSERT INTO #TableB(Pcode,XText1,XText3,XText5,MKID,CUID)
Select 'BBBTTT','<SujInfo>'+'<SujInfoKey>'+'<studyNumber>ATP111</studyNumber>'+'
<siteNumber>350000</siteNumber>'+'<cID>3501</cID>'+'</SujInfoKey>'+'<SujInfoAttributes>
'+'<ScId>350000001</ScId>'+'<TARM>A</TARM>'+'<SOV>2</SOV>'+'<SRReas>'
,'</sRReas>'+'<withReas>','</withReas>'+'<stat>P</stat>'+'<sDate>2007-08-22</sDate>'+'<ftx></ftx>'+'</SujInfoAttributes>'+'</SujInfo>','382','380'
-
You're doing a lot of concatenations...
Could it be that some of the concatenated values are NULL, and you're session have CONCAT_NULL_YIELDS_NULL ON ...?
If so, that could explain the 'lost' rows.
/Kenneth
-
I agree with Kenneth on this one. What you might want to do is add a a counter to your loop, and print out the counter and the code for each pass. Since neither the cursor declaration nor the insert have a WHERE clause, and you stated that there are 200 rows in the source, this seems to be the most likely suspect.
-
I think I found my own problem. The problem is I worked from home using a wireless DSL and the cursor return only partial rows. Nevertheless when I worked from my office it did not have that problems. Blame it on the wireless DSL .I hope my answer will help others too.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply