Custom assembly call in footer looses property values.

Custom assembly call in footer looses property values.
I have a custom assembly that uses a method in the body to pass in a data
field. When I fetch the data in the body I get my data back. When I do the
same in the footer I don't get any data. I have an active call in with
Microsoft so will also post there. I thought
we had working code, I was declaring the module scoped variable as static,
but that ends up with other peoples data on occassion when several people
are running reports. MS Support told me don't use static variables, but if
I don't then I can't access my properties in the footer.
I built a sample report and custom assembly to try to figure this out.
In the report I have something like this:
Body:
=Code.DataInFooter_NonStatic.ExpSet_MyData(First(Fields!LoginID.Value))
=Code.DataInFooter_NonStatic.MyData()
Footer:
=Code.DataInFooter_NonStatic.MyData()
What is displayed:
Body:
expected value
Footer:
empty and this is the problem.
Is it an execution order issue?
Here's my sample code:
using System;
using System.Collections.Generic;
using System.Text;
namespace DataInFooter_NonStatic
{
[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
Unrestricted = true)]
public class DataInFooter_NonStatic
{
private string _MyData;
public string MyData
{
get
{
return _MyData;
}
set
{
_MyData = MyData;
}
}
public void WriteToFile(string Input)
{
//throw new System.NotImplementedException();
}
public string ExpSet_MyData(string Input)
{
_MyData = Input;
return _MyData;
}
}
}
Steve MunLeeuwDo this instead in your footer:
for the field that is displaying originally the
"=code.DataInFooter_NonStatic.MyData()" have it point to the
ReportItems!<name of control in body>.Value i.e. ReportItems!TextBox17.Value
(where textbox 17 in the body contains your call to
"=Code.DataInFooter_NonStatic.MyData()"
=-Chris
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:Opk5KuF9GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Custom assembly call in footer looses property values.
> I have a custom assembly that uses a method in the body to pass in a data
> field. When I fetch the data in the body I get my data back. When I do
> the
> same in the footer I don't get any data. I have an active call in with
> Microsoft so will also post there. I thought
> we had working code, I was declaring the module scoped variable as static,
> but that ends up with other peoples data on occassion when several people
> are running reports. MS Support told me don't use static variables, but
> if I don't then I can't access my properties in the footer.
> I built a sample report and custom assembly to try to figure this out.
> In the report I have something like this:
> Body:
> =Code.DataInFooter_NonStatic.ExpSet_MyData(First(Fields!LoginID.Value))
> =Code.DataInFooter_NonStatic.MyData()
> Footer:
> =Code.DataInFooter_NonStatic.MyData()
> What is displayed:
> Body:
> expected value
> Footer:
> empty and this is the problem.
> Is it an execution order issue?
> Here's my sample code:
> using System;
> using System.Collections.Generic;
> using System.Text;
> namespace DataInFooter_NonStatic
> {
> [System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
> Unrestricted = true)]
> public class DataInFooter_NonStatic
> {
> private string _MyData;
> public string MyData
> {
> get
> {
> return _MyData;
> }
> set
> {
> _MyData = MyData;
> }
> }
> public void WriteToFile(string Input)
> {
> //throw new System.NotImplementedException();
> }
> public string ExpSet_MyData(string Input)
> {
> _MyData = Input;
> return _MyData;
> }
> }
> }
> Steve MunLeeuw
>|||Ok, thanks I will try that.
I think this causes and exception when you try to access the ReportItems
collection in a footer when the page doesn't have any data fields. For
example if you have a page in the middle of the report that has static text
in a textbox that spans a full page then there are no data rows in the page
and accessing the ReportItems causes the report to fail. Either that or the
ReportItem!MyHiddenFooterData.Value would not show up for that page in
PDF...or something like that. I'll try and experiment around.
Steve MunLeeuw
"Chris Conner" <Chris.Conner@.NOSPAMPolarisLibrary.com> wrote in message
news:u7HPquG9GHA.1492@.TK2MSFTNGP02.phx.gbl...
> Do this instead in your footer:
> for the field that is displaying originally the
> "=code.DataInFooter_NonStatic.MyData()" have it point to the
> ReportItems!<name of control in body>.Value i.e.
> ReportItems!TextBox17.Value (where textbox 17 in the body contains your
> call to "=Code.DataInFooter_NonStatic.MyData()"
> =-Chris
> "Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
> news:Opk5KuF9GHA.5092@.TK2MSFTNGP04.phx.gbl...
>> Custom assembly call in footer looses property values.
>> I have a custom assembly that uses a method in the body to pass in a data
>> field. When I fetch the data in the body I get my data back. When I do
>> the
>> same in the footer I don't get any data. I have an active call in with
>> Microsoft so will also post there. I thought
>> we had working code, I was declaring the module scoped variable as
>> static,
>> but that ends up with other peoples data on occassion when several people
>> are running reports. MS Support told me don't use static variables, but
>> if I don't then I can't access my properties in the footer.
>> I built a sample report and custom assembly to try to figure this out.
>> In the report I have something like this:
>> Body:
>> =Code.DataInFooter_NonStatic.ExpSet_MyData(First(Fields!LoginID.Value))
>> =Code.DataInFooter_NonStatic.MyData()
>> Footer:
>> =Code.DataInFooter_NonStatic.MyData()
>> What is displayed:
>> Body:
>> expected value
>> Footer:
>> empty and this is the problem.
>> Is it an execution order issue?
>> Here's my sample code:
>> using System;
>> using System.Collections.Generic;
>> using System.Text;
>> namespace DataInFooter_NonStatic
>> {
>> [System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
>> Unrestricted = true)]
>> public class DataInFooter_NonStatic
>> {
>> private string _MyData;
>> public string MyData
>> {
>> get
>> {
>> return _MyData;
>> }
>> set
>> {
>> _MyData = MyData;
>> }
>> }
>> public void WriteToFile(string Input)
>> {
>> //throw new System.NotImplementedException();
>> }
>> public string ExpSet_MyData(string Input)
>> {
>> _MyData = Input;
>> return _MyData;
>> }
>> }
>> }
>> Steve MunLeeuw
>|||Turns out the custom assembly gets loaded once in the body, then again in
the footer, that's why they don't share data so well.
Steve MunLeeuw
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:Opk5KuF9GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Custom assembly call in footer looses property values.
> I have a custom assembly that uses a method in the body to pass in a data
> field. When I fetch the data in the body I get my data back. When I do
> the
> same in the footer I don't get any data. I have an active call in with
> Microsoft so will also post there. I thought
> we had working code, I was declaring the module scoped variable as static,
> but that ends up with other peoples data on occassion when several people
> are running reports. MS Support told me don't use static variables, but
> if I don't then I can't access my properties in the footer.
> I built a sample report and custom assembly to try to figure this out.
> In the report I have something like this:
> Body:
> =Code.DataInFooter_NonStatic.ExpSet_MyData(First(Fields!LoginID.Value))
> =Code.DataInFooter_NonStatic.MyData()
> Footer:
> =Code.DataInFooter_NonStatic.MyData()
> What is displayed:
> Body:
> expected value
> Footer:
> empty and this is the problem.
> Is it an execution order issue?
> Here's my sample code:
> using System;
> using System.Collections.Generic;
> using System.Text;
> namespace DataInFooter_NonStatic
> {
> [System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
> Unrestricted = true)]
> public class DataInFooter_NonStatic
> {
> private string _MyData;
> public string MyData
> {
> get
> {
> return _MyData;
> }
> set
> {
> _MyData = MyData;
> }
> }
> public void WriteToFile(string Input)
> {
> //throw new System.NotImplementedException();
> }
> public string ExpSet_MyData(string Input)
> {
> _MyData = Input;
> return _MyData;
> }
> }
> }
> Steve MunLeeuw
>|||Here's some sample code of a custom assembly that shows the instance is not
shared between the body of the report and the footer.
http://smunson.blogspot.com/2006/10/rs-custom-assembly-instance-gets-re.html
Steve MunLeeuw
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:Opk5KuF9GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Custom assembly call in footer looses property values.
> I have a custom assembly that uses a method in the body to pass in a data
> field. When I fetch the data in the body I get my data back. When I do
> the
> same in the footer I don't get any data. I have an active call in with
> Microsoft so will also post there. I thought
> we had working code, I was declaring the module scoped variable as static,
> but that ends up with other peoples data on occassion when several people
> are running reports. MS Support told me don't use static variables, but
> if I don't then I can't access my properties in the footer.
> I built a sample report and custom assembly to try to figure this out.
> In the report I have something like this:
> Body:
> =Code.DataInFooter_NonStatic.ExpSet_MyData(First(Fields!LoginID.Value))
> =Code.DataInFooter_NonStatic.MyData()
> Footer:
> =Code.DataInFooter_NonStatic.MyData()
> What is displayed:
> Body:
> expected value
> Footer:
> empty and this is the problem.
> Is it an execution order issue?
> Here's my sample code:
> using System;
> using System.Collections.Generic;
> using System.Text;
> namespace DataInFooter_NonStatic
> {
> [System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
> Unrestricted = true)]
> public class DataInFooter_NonStatic
> {
> private string _MyData;
> public string MyData
> {
> get
> {
> return _MyData;
> }
> set
> {
> _MyData = MyData;
> }
> }
> public void WriteToFile(string Input)
> {
> //throw new System.NotImplementedException();
> }
> public string ExpSet_MyData(string Input)
> {
> _MyData = Input;
> return _MyData;
> }
> }
> }
> Steve MunLeeuw
>

Cursors only fetching about 200 records?

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
--

Cursors in functions

Hi
When I want to loop through a result in sp I use Cursor
and Fetch. But that does not work in functions (atleast
not for me). Anyone that knows what to use instead'
/AnettMay be this helps
According to books online
Cursor operations referencing local cursors that are declared, opened,
closed, and deallocated in the function. Only FETCH statements that assign
values to local variables using the INTO clause are allowed; FETCH
statements that return data to the client are not allowed.
Thanks
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:1afe01c5005c$138a7740$a601280a@.phx.gbl...
> Hi
> When I want to loop through a result in sp I use Cursor
> and Fetch. But that does not work in functions (atleast
> not for me). Anyone that knows what to use instead'
> /Anett|||Anett wrote:
> Hi
> When I want to loop through a result in sp I use Cursor
> and Fetch. But that does not work in functions (atleast
> not for me). Anyone that knows what to use instead'
> /Anett
You should use a set-based approach if at all possible. Cursors in SQL
Server are extremely slow. In fact, using functions on columns in select
statements can be slow by itself, since SQL Server has to call the
function (just like it calls a procedure) for every row in the result
set. And if you can imagine returning a 1,000 rows from a stored
procedure call, you'd really have 1,001 SP calls if you attached a
function to a single column.
If you provide more details about what you are trying to accomplish, we
may be able to offer a set-based solution.
David Gugick
Imceda Software
www.imceda.com|||Hi, this is what I'm after
I got a view like this:
w item used
501 abc 10
502 abc 15
503 abc 5
504 abc 0
505 abc 30
What I want to do is to count the used column for each
w:
w item usedSum
501 abc 10
502 abc 35 (10+15)
503 abc 40 (35+5)
504 abc 40 (40+0)
505 abc 70 (40+30)
Hope you can help me.
thanks

>--Original Message--
>Anett wrote:
>You should use a set-based approach if at all possible.
Cursors in SQL
>Server are extremely slow. In fact, using functions on
columns in select
>statements can be slow by itself, since SQL Server has to
call the
>function (just like it calls a procedure) for every row
in the result
>set. And if you can imagine returning a 1,000 rows from a
stored
>procedure call, you'd really have 1,001 SP calls if you
attached a
>function to a single column.
>If you provide more details about what you are trying to
accomplish, we
>may be able to offer a set-based solution.
>
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Hi, this is what I'm after
> I got a view like this:
> w item used
> 501 abc 10
> 502 abc 15
> 503 abc 5
> 504 abc 0
> 505 abc 30
> What I want to do is to count the used column for each
> w:
> w item usedSum
> 501 abc 10
> 502 abc 35 (10+15)
> 503 abc 40 (35+5)
> 504 abc 40 (40+0)
> 505 abc 70 (40+30)
> Hope you can help me.
> thanks
>
Untested, but it should give you the idea:
SELECT w, item,
(SELECT SUM(used) FROM viewname WHERE w <= o.w
AND item = o.item) AS usedSum
FROM viewname o
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Do you want to see something really scary? mwaahahahahaaa
CREATE FUNCTION dbo.Totaller()
RETURNS int AS
BEGIN
DECLARE @.o int
EXECUTE sp_OACreate 'VBScript.RegExp', @.o OUT
EXECUTE sp_OASetProperty @.o, 'Pattern', ''
RETURN @.o
END
GO
CREATE FUNCTION dbo.Running_Total(@.o int, @.Val int)
RETURNS int AS
BEGIN
DECLARE @.Total int
EXECUTE sp_OAGetProperty @.o, 'Pattern', @.Total OUTPUT
SET @.Total = IsNull(@.Total,0) + IsNull(@.Val,0)
EXECUTE sp_OASetProperty @.o, 'Pattern', @.Total
RETURN @.Total
END
GO
DECLARE @.o int
SET @.o = dbo.Totaller()
SELECT w, item, used, dbo.Running_Total(@.o, used) FROM view
Mr Tea
http://mr-tea.blogspot.com
<anonymous@.discussions.microsoft.com> wrote in message
news:179401c50064$d7ff0070$a401280a@.phx.gbl...
> Hi, this is what I'm after
> I got a view like this:
> w item used
> 501 abc 10
> 502 abc 15
> 503 abc 5
> 504 abc 0
> 505 abc 30
> What I want to do is to count the used column for each
> w:
> w item usedSum
> 501 abc 10
> 502 abc 35 (10+15)
> 503 abc 40 (35+5)
> 504 abc 40 (40+0)
> 505 abc 70 (40+30)
> Hope you can help me.
> thanks
>
> Cursors in SQL
> columns in select
> call the
> in the result
> stored
> attached a
> accomplish, we|||Lee Tudor wrote:
> Do you want to see something really scary? mwaahahahahaaa
> CREATE FUNCTION dbo.Totaller()
> RETURNS int AS
> BEGIN
> DECLARE @.o int
> EXECUTE sp_OACreate 'VBScript.RegExp', @.o OUT
> EXECUTE sp_OASetProperty @.o, 'Pattern', ''
> RETURN @.o
> END
> GO
> CREATE FUNCTION dbo.Running_Total(@.o int, @.Val int)
> RETURNS int AS
> BEGIN
> DECLARE @.Total int
> EXECUTE sp_OAGetProperty @.o, 'Pattern', @.Total OUTPUT
> SET @.Total = IsNull(@.Total,0) + IsNull(@.Val,0)
> EXECUTE sp_OASetProperty @.o, 'Pattern', @.Total
> RETURN @.Total
> END
> GO
> DECLARE @.o int
> SET @.o = dbo.Totaller()
> SELECT w, item, used, dbo.Running_Total(@.o, used) FROM view
> Mr Tea
> http://mr-tea.blogspot.com
>
What's scary is that you posted code without any description of what it
does. And you used COM calls which is scarier.
So what does it do?
David Gugick
Imceda Software
www.imceda.com|||It uses a regexp object (could be anything really, the lighter the better)
to provide persistance of data beween function calls allowing you to add
things like string concatenation, running totals and row numbers (1,2,3,4)
to your select statements.
the example provided gives the requested result and a basis to extend this
to other applications ..

>I got a view like this:
>w item used
>501 abc 10
>502 abc 15
>503 abc 5
>504 abc 0
>505 abc 30

>What I want to do is to count the used column for each w:
>w item usedSum
>501 abc 10 502 abc 35 (10+15)
>503 abc 40 (35+5)
>504 abc 40 (40+0)
>505 abc 70 (40+30)
Mr Tea
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23UeajUPAFHA.3528@.tk2msftngp13.phx.gbl...
> Lee Tudor wrote:
> What's scary is that you posted code without any description of what it
> does. And you used COM calls which is scarier.
> So what does it do?
> --
> David Gugick
> Imceda Software
> www.imceda.com