I have a cursor that is supposed to fetch all the records from a temporary
table, #temp, one at a time, do some manipulation on the data, insert the
results into another temporary table, #detailedTransRpt, then delete the
record from #temp. All of the records from #temp should be deleted before
@.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always aroun
d
the time 200 records have been fetched, thereby leaving some data in #temp.
I have tried different data sets, and have compared the records on which the
fetch fails, but these records have nothing in common and I can't seem to
figure out why the fetch is failing. It seems to have more to do with the
number of records, than anything else. Has anyone come across a similar
problem? Any advice?Keith wrote:
> I have a cursor that is supposed to fetch all the records from a temporary
> table, #temp, one at a time, do some manipulation on the data, insert the
> results into another temporary table, #detailedTransRpt, then delete the
> record from #temp. All of the records from #temp should be deleted before
> @.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always aro
und
> the time 200 records have been fetched, thereby leaving some data in #temp
.
> I have tried different data sets, and have compared the records on which t
he
> fetch fails, but these records have nothing in common and I can't seem to
> figure out why the fetch is failing. It seems to have more to do with the
> number of records, than anything else. Has anyone come across a similar
> problem? Any advice?
Without seeing the code it's anyone's guess. Based on your description
it's probably unlikely that you'll need a cursor at all. There are
usually better solutions so I suggest you post DDL, some sample data
and show what result you want if you need some help.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Why are you deleting the rows within your cursor? I think you can loop
through all the rows and delete them only when you are done with the entire
set of data. I don't know if this has any effect on the cursor or not, but
it sounds a little odd and might be worth checking. Basically, I am
wondering if updating the temp table that you are reading from might cause
strange behavior, although I would expect an error rather than lost rows if
this were the case.
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:44450145-CBC9-4AC0-B08C-DAF3F992C4B9@.microsoft.com...
> I have a cursor that is supposed to fetch all the records from a temporary
> table, #temp, one at a time, do some manipulation on the data, insert the
> results into another temporary table, #detailedTransRpt, then delete the
> record from #temp. All of the records from #temp should be deleted before
> @.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always
around
> the time 200 records have been fetched, thereby leaving some data in
#temp.
> I have tried different data sets, and have compared the records on which
the
> fetch fails, but these records have nothing in common and I can't seem to
> figure out why the fetch is failing. It seems to have more to do with the
> number of records, than anything else. Has anyone come across a similar
> problem? Any advice?|||by any chance, is rowcount set to 200?|||I'm posting all the variable declarations, and what I set the stored
procedure parameters to, for debugging purposes, as well as the two table
CREATE statements, and the single cursor that I know is failing. There is
too much code in the entire SP for me to paste here. A function splits the
string from @.strbankAccID into bank account IDs, and then the transactions
from a given bank account ID are put into #temp. The transactions from #tem
p
are manipulated then put into #detailedTransRpt, and then deleted from #temp
.
However, the cursor @.@.fetch_status = -1 before all the transactions are
removed from #temp, and the transactions end up being mixed up between the
bank accounts on the report that this produces. Why was it done this way,
you may ask? Because some other developer (who is long gone) did it this wa
y
and I am now responsible for fixing the report. Can you feel the
frustration? :) Thanks!
DECLARE @.Lodge_ID VARCHAR(9)
DECLARE @.begin VARCHAR(25)
DECLARE @.end VARCHAR(25)
DECLARE @.strbankAccID VARCHAR (8000)
DECLARE @.transTypeCD CHAR(3)
DECLARE @.transVersion CHAR(3)
DECLARE @.accountChosen VARCHAR(3)
DECLARE @.totalRecord INT
SET @.LODGE_ID = 'D60'
set @.begin = '12/21/2005'
set @.end = '12/31/2005'
set @.strbankAccID = '583,1373,1374,1380,1381,1384,1385,1397'
--D60
set @.transTypeCD = 'All'
set @.transVersion = 'All'
set @.accountChosen = 'All'
SET NOCOUNT ON
DECLARE @.beginDate DATETIME
DECLARE @.endDate DATETIME
DECLARE @.acctIDstring VARCHAR(1000)
DECLARE @.pos INT
DECLARE @.str1 VARCHAR (8)
DECLARE @.length INT
DECLARE @.strTransNum VARCHAR(50)
DECLARE @.transID INT
DECLARE @.bankAcctID BIGINT
DECLARE @.acctNameLabel VARCHAR(50)
DECLARE @.transTypeLabel VARCHAR(50)
DECLARE @.transVersionLabel VARCHAR(100)
DECLARE @.bankType AS CHAR(1)
DECLARE @.charBankType AS CHAR(1)
DECLARE @.flag BIT
DECLARE @.DepositTransID INT
DECLARE @.charDepositStatusCD CHAR(2)
DECLARE @.bankID INT
CREATE TABLE #temp (
tempDepTransID INT,
DepositID INT,
TransNumber VARCHAR(50),
TransAmt MONEY,
SplitInd CHAR(1) ,
CategoryID INT,
SubCategoryID INT,
TransStatusCD CHAR(1),
TransVersionCD CHAR(2),
PaymentType CHAR(2),
TransDate DATETIME,
TransType CHAR(2),
TransDesc VARCHAR(150),
class_cd varchar(50)
)
CREATE TABLE #detailedTransRpt (
BankAcctID BIGINT,
depTransID INT,
Header VARCHAR(255),
AcctNum VARCHAR(30),
BankName VARCHAR(50),
AcctType VARCHAR(50),
Status VARCHAR(20),
TransDate DATETIME,
Number VARCHAR (255),-- receipt # ... trans number in a string with ','
TransDesc VARCHAR(150),-- receipt number ...trans number in a string with
','
PaymentType VARCHAR(20),-- deposit for section 3
Receipt MONEY,
Reconciled VARCHAR(20),
Disbursement MONEY,
TransVersion VARCHAR(100),
Category VARCHAR (50),
SubCategory VARCHAR (50),
class_cd varchar(50)
)
declare @.classCd varchar(50)
declare @.depTransID INT
declare @.tempDepTransID INT
DECLARE @.bankName AS VARCHAR (50)
DECLARE @.acctNo AS VARCHAR(30)
DECLARE @.acctName AS VARCHAR(50)
DECLARE @.charStatus AS CHAR(1)
DECLARE @.strStatus AS VARCHAR(20)
DECLARE @.charAcctType AS CHAR(1)
DECLARE @.strAcctType AS VARCHAR(50)
DECLARE @.header AS VARCHAR(255)
DECLARE @.TempTransID INT
DECLARE @.depositID INT
DECLARE @.transactionNumber VARCHAR(50)
DECLARE @.transactionDesc VARCHAR(150)
DECLARE @.numberofreceipt INT
DECLARE @.transNum VARCHAR(50)
DECLARE @.transAmt MONEY
DECLARE @.transTotal MONEY
DECLARE @.Category INT
DECLARE @.subCategory INT
DECLARE @.categoryDesc VARCHAR(100)
DECLARE @.subCategoryDesc VARCHAR(100)
DECLARE @.SplitInd CHAR(1)
DECLARE @.charTransStatusCD CHAR(1)
DECLARE @.charTransVersionCD CHAR(1)
DECLARE @.charPaymentTypeCD CHAR(2)
DECLARE @.strPaymentType VARCHAR(20)
DECLARE @.strTransStatus VARCHAR(20)
DECLARE @.strTransVersion VARCHAR(100)
DECLARE @.transDate DATETIME
DECLARE @.TransType CHAR(2)
DECLARE @.TransDesc VARCHAR(150)
DECLARE @.DepositMemo VARCHAR(50)
DECLARE @.DepositNumber VARCHAR(20)
DECLARE @.intDeposit INT
SET @.transactionNumber = ''
DECLARE curTransNum CURSOR FOR
SELECT * FROM #temp WHERE (DepositID IS NULL) AND TransType <> 'TR'
OPEN curTransNum
FETCH NEXT FROM curTransNum INTO
@.depTransID,@.DepositID,@.transNum,@.transA
mt,@.splitInd,@.Category,
@.subCategory,@.charTransStatusCD,@.charTra
nsVersionCD,@.charPaymentTypeCD,
@.transDate,@.TransType,@.TransDesc,@.classC
D
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.categoryDesc = NULL
SET @.subCategoryDesc = NULL
set @.classCDdesc = null
IF @.splitInd ='Y'
BEGIN
SET @.categoryDesc = 'SPLIT'
SET @.subCategoryDesc = 'SPLIT'
set @.classCDdesc = 'SPLIT'
END
ELSE
BEGIN
SELECT @.categoryDesc = Category_Description FROM Category WHERE
Category_ID = @.Category
SELECT @.subCategoryDesc = Sub_Category_description FROM Sub_Category
WHERE Sub_Category_id = @.subCategory
IF @.TransType = 'RT'
select @.classCDdesc = description from lookup where Category LIKE
'CLASS_SCHEDULE_CAT_CD' AND Code LIKE @.classCD
else
select @.classCDdesc = description from lookup where Category LIKE
'CLASS_FUNCTIONAL_CAT_CD' AND Code LIKE @.classCD
END
SELECT @.strPaymentType = [Description]
FROM Lookup
WHERE Category LIKE 'PAYMENT_TYPE_CD' AND Code LIKE @.charPaymentTypeCD
IF @.charTransStatusCD = 'U'
SET @.strTransStatus = ''
ELSE
SET @.strTransStatus = @.charTransStatusCD
IF @.charTransVersionCD = '0'
SET @.strTransVersion = ''
ELSE IF @.charTransVersionCD = '1'
SET @.strTransVersion = 'Edited'
ELSE IF @.charTransVersionCD = '2'
SET @.strTransVersion = 'Adjusted'
ELSE IF @.charTransVersionCD = '3' OR @.charTransVersionCD = '5'
SET @.strTransVersion = 'Voided'
ELSE IF @.charTransVersionCD = '4' OR @.charTransVersionCD = '6'
SET @.strTransVersion = 'Unvoided'
IF @.TransType = 'RT'--undeposited receipts
BEGIN
INSERT INTO #detailedTransRpt
(bankAcctID,depTransID,Header,AcctNum,Ba
nkName,
AcctType,Status,TransDate,Number,
TransDesc,PaymentType,Receipt,Reconciled
,Disbursement,TransVersion,Category,
SubCategory,class_cd)
VALUES
(@.bankAcctID,@.depTransID,@.header,@.acctNo
,@.bankName,@.strAcctType,@.strStatus,@.
transDate,'Receipt #'+@.transNum,
@.TransDesc,@.strPaymentType,--CAST(@.transAmt AS VARCHAR),
@.transAmt,@.strTransStatus,NULL,@.strTrans
Version,@.categoryDesc,@.subCategoryDe
sc,@.classCDdesc)
--DELETE #temp WHERE @.TransType = 'RT'
DELETE #temp WHERE tempDepTransID = @.depTransID
END
ELSE --this is disbursement
BEGIN
IF @.charPaymentTypeCD = 'EF'
SET @.transactionNumber = 'EFT#' + @.transNum
ELSE IF @.charPaymentTypeCD = 'CH'
SET @.transactionNumber = 'Chk#' + @.transNum -- 7/18/2005
INSERT INTO #detailedTransRpt
(bankAcctID,depTransID,Header,AcctNum,Ba
nkName,
AcctType,Status,TransDate,Number,
TransDesc,PaymentType,Receipt,Reconciled
,Disbursement,TransVersion,Category,
SubCategory,class_cd)
VALUES
(@.bankAcctID,@.depTransID,@.header,@.acctNo
,@.bankName,@.strAcctType,@.strStatus,@.
transDate,@.transactionNumber,
@.TransDesc,@.strPaymentType,NULL,@.strTran
sStatus,@.transAmt,
@.strTransVersion,@.categoryDesc,@.subCateg
oryDesc,@.classCDdesc)
DELETE #temp WHERE tempDepTransID = @.depTransID --@.TransType = 'DI'
END
FETCH NEXT FROM curTransNum INTO
@.depTransID,@.DepositID,@.transNum,@.transA
mt,@.splitInd,@.Category,
@.subCategory,@.charTransStatusCD,@.charTra
nsVersionCD,@.charPaymentTypeCD,
@.transDate,@.TransType, @.TransDesc,@.classCD
END
CLOSE curTransNum
DEALLOCATE curTransNum
"David Portas" wrote:
> Keith wrote:
> Without seeing the code it's anyone's guess. Based on your description
> it's probably unlikely that you'll need a cursor at all. There are
> usually better solutions so I suggest you post DDL, some sample data
> and show what result you want if you need some help.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I'm a fairly new developer, so I'm not familiar with the ROWCOUNT setting.
How and where would I check and set this? Incidentally, I am working with
SQL Server 2000.
"Alexander Kuznetsov" wrote:
> by any chance, is rowcount set to 200?
>|||Watch where you are deleting records in the cursor...delete after the fetch
next..
We don't use cursors here, but table variables, with an indentity
column..and then use a while loop.
Instead of deleting on the fly, just set a flag. Then delete all at once at
the end, based on the flag.
Jeff
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:44450145-CBC9-4AC0-B08C-DAF3F992C4B9@.microsoft.com...
>I have a cursor that is supposed to fetch all the records from a temporary
> table, #temp, one at a time, do some manipulation on the data, insert the
> results into another temporary table, #detailedTransRpt, then delete the
> record from #temp. All of the records from #temp should be deleted before
> @.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always
> around
> the time 200 records have been fetched, thereby leaving some data in
> #temp.
> I have tried different data sets, and have compared the records on which
> the
> fetch fails, but these records have nothing in common and I can't seem to
> figure out why the fetch is failing. It seems to have more to do with the
> number of records, than anything else. Has anyone come across a similar
> problem? Any advice?|||The short answer is that it is being done this way because another developer
did it this way, and I am trying not to rewrite the stored procedure and
possibly create more bugs in the process of fixing this one. All the record
s
in #temp are transactions for a single bank account, so the records are
deleted from here as they are inserted into #detailedTransRpt, because later
a different cursor (this SP contains a billion nested cursors!) changes the
bank account that will appear in a different section of the report. If ther
e
are records still left in #temp, then all the bank account transactions get
mixed up. Like I said, this just seems to happen when there are more than
200 transactions in the report, so I was wondering if there was some
significance between the number 200 and something to do with cursors.
"Jim Underwood" wrote:
> Why are you deleting the rows within your cursor? I think you can loop
> through all the rows and delete them only when you are done with the entir
e
> set of data. I don't know if this has any effect on the cursor or not, bu
t
> it sounds a little odd and might be worth checking. Basically, I am
> wondering if updating the temp table that you are reading from might cause
> strange behavior, although I would expect an error rather than lost rows i
f
> this were the case.
> "Keith" <Keith@.discussions.microsoft.com> wrote in message
> news:44450145-CBC9-4AC0-B08C-DAF3F992C4B9@.microsoft.com...
> around
> #temp.
> the
>
>|||Now that would be an incredibly simple solution...
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1146590107.762359.308020@.y43g2000cwc.googlegroups.com...
> by any chance, is rowcount set to 200?
>|||"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:F26CC2DD-B066-48E7-BB41-0A6E0F88EB97@.microsoft.com...
> I'm posting all the variable declarations, and what I set the stored
> procedure parameters to, for debugging purposes, as well as the two table
> CREATE statements, and the single cursor that I know is failing. There is
> too much code in the entire SP for me to paste here. A function splits
> the
> string from @.strbankAccID into bank account IDs, and then the
> transactions
> from a given bank account ID are put into #temp. The transactions from
> #temp
> are manipulated then put into #detailedTransRpt, and then deleted from
> #temp.
> However, the cursor @.@.fetch_status = -1 before all the transactions are
> removed from #temp, and the transactions end up being mixed up between the
> bank accounts on the report that this produces. Why was it done this way,
> you may ask? Because some other developer (who is long gone) did it this
> way
> and I am now responsible for fixing the report. Can you feel the
> frustration? :) Thanks!
I would replace the whole cursor with two INSERTs and a DELETE. Here's my
effort at the first INSERT (untested and therefore not necessarily complete
or totally accurate).
INSERT INTO #detailedTransRpt
(bankAcctID, depTransID, Header, AcctNum, BankName, AcctType, Status,
TransDate, Number, TransDesc, PaymentType, Receipt, Reconciled,
Disbursement, TransVersion, Category, SubCategory, class_cd)
SELECT NULL, T.tempDepTransID, NULL, NULL, NULL, NULL, NULL,
T.TransDate, 'Receipt #'+T.TransNumber, T.TransDesc, L.Description,
NULL,
CASE WHEN TransStatusCD = 'U' THEN '' ELSE TransStatusCD END,NULL,
CASE TransVersionCD
WHEN '0' THEN ''
WHEN '1' THEN 'Edited'
WHEN '2' THEN 'Adjusted'
WHEN '3' THEN 'Voided'
WHEN '4' THEN 'Unvoided'
WHEN '5' THEN 'Voided'
WHEN '6' THEN 'Unvoided'
END, C.Category_description, S.Sub_Category_description, M.Description
FROM #Temp AS T
JOIN Lookup AS L
ON L.Category LIKE 'PAYMENT_TYPE_CD'
AND L.Code LIKE T.PaymentType
JOIN Lookup AS M
ON M.Category LIKE 'CLASS_SCHEDULE_CAT_CD'
AND L.Code LIKE T.PaymentType
JOIN Category AS C
ON T.CategoryID = C.Category_id
JOIN Sub_Category AS S
ON T.SubCategoryID = S.Sub_Category_id
WHERE TransType = 'RT' ;
DELETE #temp WHERE TransType = 'RT' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
. All day long I was looking