Count Invisible ReportItems in Page Footer?

I have a textbox in my page footer with an expression like this: Sum(iif(
(ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
many times "Complete" comes up in a page. I also have a parameter which can
filter out any "Complete" values using the visibility of the detail row. My
problem is that I still want a count of the Complete values, even if they are
filtered out on the page. When I run the report with no filters, it shows me
exactly how many Completes there are per page. However, it will not count
the Completes when they are filtered out by page. How can I get the page
footer to count them even when they are hidden?Sorry, this is not supported in RS 2000. The good news is that this will
change in RS 2005 and conditionally (expression-based) hidden items won't be
taken into account in page header/footer calculations.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:D32037E2-4E92-4BB0-B622-09794DEAF21F@.microsoft.com...
>I have a textbox in my page footer with an expression like this: Sum(iif(
> (ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
> many times "Complete" comes up in a page. I also have a parameter which
> can
> filter out any "Complete" values using the visibility of the detail row.
> My
> problem is that I still want a count of the Complete values, even if they
> are
> filtered out on the page. When I run the report with no filters, it shows
> me
> exactly how many Completes there are per page. However, it will not count
> the Completes when they are filtered out by page. How can I get the page
> footer to count them even when they are hidden?|||Though in simple terms its not possible. But there is work around-
Make custom dll which has a method which takes filter expression as input
and connects to same datasource using ado.net and fetches the same records.
Then calculate no of "complete"s in table and return the value to report.
Please see using custom assemblies in Reporting Services. Its very powerful!
Suneet Moha
-----
"dachrist" wrote:
> I have a textbox in my page footer with an expression like this: Sum(iif(
> (ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
> many times "Complete" comes up in a page. I also have a parameter which can
> filter out any "Complete" values using the visibility of the detail row. My
> problem is that I still want a count of the Complete values, even if they are
> filtered out on the page. When I run the report with no filters, it shows me
> exactly how many Completes there are per page. However, it will not count
> the Completes when they are filtered out by page. How can I get the page
> footer to count them even when they are hidden?

Could not complete cursor operation because the table schema changed

Microsoft SQL Server 2000 - 8.00.2039

Got this error:

Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000

Is this a known issue? I suspect the application logic may cause this error. Please advise.

Thanks a lot!

Is the message accurate?

Does your app change the table(s) that the cursor is using?

/Kenneth

|||

I am running into the same issue. The table is not being altered. Here is an outline of what I am doing.

A cursor loop is running in a stored procedure (looping through server and drive letters). In the loop it calls a stored procedure that does the following:

DBCC CHECKIDENT (zstblACLSStage,RESEED,0)

SET @.strSQL = 'INSERT INTO zstblACLSStage(LN) SELECT LN = RTRIM(LTRIM(REPLACE(F1,CHAR(9),'''')))
FROM OPENROWSET
(
''Microsoft.Jet.OLEDB.4.0''
,''Text;Database=' + @.strDirectory + ';HDR=NO''
,''SELECT * FROM ' + @.strFile + '''
)
WHERE ISNULL(REPLACE(F1,CHAR(9),''''),'''') != '''''
PRINT @.strSQL
EXECUTE (@.strSQL)

More processing here transfers some data from zstblACLSStage to tblACLS

DELETE FROM zttblACLSStage

This is all run from a scheduled task in SQL. If I kick the job off manually, it will run and complete. If it runs scheduled for overnight, the EXECUTE will get the error message. It happens the 7th time through the loop and it always happens. I am suspecting the DBCC so I am commenting that out to give it another try, any suggestions are welcome.

|||

Can you give the select statement that creates your cursor?

|||

Hi All,

Even I am getting the same error while querying the SQL 2000 database table from ASP. I use a select statement , containing outer joins, and sometimes this error pops up.

Thanks,|||

Could you also update me if you get an answer on this. I am also facing similar issue. My system is throwing the same error every once in a while and if we do the same operation again, it works fine. I verified with maintenance jobs also and they are not conflicting.

Please Help !!!!!

Could not complete cursor operation because the table schema changed

Microsoft SQL Server 2000 - 8.00.2039

Got this error:

Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000

Is this a known issue? I suspect the application logic may cause this error. Please advise.

Thanks a lot!

Is the message accurate?

Does your app change the table(s) that the cursor is using?

/Kenneth

|||

I am running into the same issue. The table is not being altered. Here is an outline of what I am doing.

A cursor loop is running in a stored procedure (looping through server and drive letters). In the loop it calls a stored procedure that does the following:

DBCC CHECKIDENT (zstblACLSStage,RESEED,0)

SET @.strSQL = 'INSERT INTO zstblACLSStage(LN) SELECT LN = RTRIM(LTRIM(REPLACE(F1,CHAR(9),'''')))
FROM OPENROWSET
(
''Microsoft.Jet.OLEDB.4.0''
,''Text;Database=' + @.strDirectory + ';HDR=NO''
,''SELECT * FROM ' + @.strFile + '''
)
WHERE ISNULL(REPLACE(F1,CHAR(9),''''),'''') != '''''
PRINT @.strSQL
EXECUTE (@.strSQL)

More processing here transfers some data from zstblACLSStage to tblACLS

DELETE FROM zttblACLSStage

This is all run from a scheduled task in SQL. If I kick the job off manually, it will run and complete. If it runs scheduled for overnight, the EXECUTE will get the error message. It happens the 7th time through the loop and it always happens. I am suspecting the DBCC so I am commenting that out to give it another try, any suggestions are welcome.

|||

Can you give the select statement that creates your cursor?

|||

Hi All,

Even I am getting the same error while querying the SQL 2000 database table from ASP. I use a select statement , containing outer joins, and sometimes this error pops up.

Thanks,|||

Could you also update me if you get an answer on this. I am also facing similar issue. My system is throwing the same error every once in a while and if we do the same operation again, it works fine. I verified with maintenance jobs also and they are not conflicting.

Please Help !!!!!

Could not complete cursor operation because the table schema changed

Microsoft SQL Server 2000 - 8.00.2039

Got this error:

Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000

Is this a known issue? I suspect the application logic may cause this error. Please advise.

Thanks a lot!

Is the message accurate?

Does your app change the table(s) that the cursor is using?

/Kenneth

|||

I am running into the same issue. The table is not being altered. Here is an outline of what I am doing.

A cursor loop is running in a stored procedure (looping through server and drive letters). In the loop it calls a stored procedure that does the following:

DBCC CHECKIDENT (zstblACLSStage,RESEED,0)

SET @.strSQL = 'INSERT INTO zstblACLSStage(LN) SELECT LN = RTRIM(LTRIM(REPLACE(F1,CHAR(9),'''')))
FROM OPENROWSET
(
''Microsoft.Jet.OLEDB.4.0''
,''Text;Database=' + @.strDirectory + ';HDR=NO''
,''SELECT * FROM ' + @.strFile + '''
)
WHERE ISNULL(REPLACE(F1,CHAR(9),''''),'''') != '''''
PRINT @.strSQL
EXECUTE (@.strSQL)

More processing here transfers some data from zstblACLSStage to tblACLS

DELETE FROM zttblACLSStage

This is all run from a scheduled task in SQL. If I kick the job off manually, it will run and complete. If it runs scheduled for overnight, the EXECUTE will get the error message. It happens the 7th time through the loop and it always happens. I am suspecting the DBCC so I am commenting that out to give it another try, any suggestions are welcome.

|||

Can you give the select statement that creates your cursor?

|||

Hi All,

Even I am getting the same error while querying the SQL 2000 database table from ASP. I use a select statement , containing outer joins, and sometimes this error pops up.

Thanks,|||

Could you also update me if you get an answer on this. I am also facing similar issue. My system is throwing the same error every once in a while and if we do the same operation again, it works fine. I verified with maintenance jobs also and they are not conflicting.

Please Help !!!!!

Could not complete cursor operation because the table schema changed

Microsoft SQL Server 2000 - 8.00.2039

Got this error:

Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000

Is this a known issue? I suspect the application logic may cause this error. Please advise.

Thanks a lot!

Is the message accurate?

Does your app change the table(s) that the cursor is using?

/Kenneth

|||

I am running into the same issue. The table is not being altered. Here is an outline of what I am doing.

A cursor loop is running in a stored procedure (looping through server and drive letters). In the loop it calls a stored procedure that does the following:

DBCC CHECKIDENT (zstblACLSStage,RESEED,0)

SET @.strSQL = 'INSERT INTO zstblACLSStage(LN) SELECT LN = RTRIM(LTRIM(REPLACE(F1,CHAR(9),'''')))
FROM OPENROWSET
(
''Microsoft.Jet.OLEDB.4.0''
,''Text;Database=' + @.strDirectory + ';HDR=NO''
,''SELECT * FROM ' + @.strFile + '''
)
WHERE ISNULL(REPLACE(F1,CHAR(9),''''),'''') != '''''
PRINT @.strSQL
EXECUTE (@.strSQL)

More processing here transfers some data from zstblACLSStage to tblACLS

DELETE FROM zttblACLSStage

This is all run from a scheduled task in SQL. If I kick the job off manually, it will run and complete. If it runs scheduled for overnight, the EXECUTE will get the error message. It happens the 7th time through the loop and it always happens. I am suspecting the DBCC so I am commenting that out to give it another try, any suggestions are welcome.

|||

Can you give the select statement that creates your cursor?

|||

Hi All,

Even I am getting the same error while querying the SQL 2000 database table from ASP. I use a select statement , containing outer joins, and sometimes this error pops up.

Thanks,|||

Could you also update me if you get an answer on this. I am also facing similar issue. My system is throwing the same error every once in a while and if we do the same operation again, it works fine. I verified with maintenance jobs also and they are not conflicting.

Please Help !!!!!

Could not complete cursor operation because the table schema changed

Microsoft SQL Server 2000 - 8.00.2039

Got this error:

Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000

Is this a known issue? I suspect the application logic may cause this error. Please advise.

Thanks a lot!

Hi,

are you doing schema modifications within your cursor ? If so the fetch of a new row will break as SQL Server cant validate the underlying existing schema at the beginning of the cursor declaration / opening. If you don't have any clue about the cursor run profiler to see what is executed (procedure or any code) on the server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Could not complete cursor operation because the set options have changed since the cursor was de

I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker

I get the following error can you please explain this problem to me so I can over come the issue.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:

Alter PROCEDURE [dbo].[sp_MSforeachsp]

@.command1 nvarchar(2000)

, @.replacechar nchar(1) = N'?'

, @.command2 nvarchar(2000) = null

, @.command3 nvarchar(2000) = null

, @.whereand nvarchar(2000) = null

, @.precommand nvarchar(2000) = null

, @.postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @.precommand and @.postcommand may be used to force a single result set via a temp table. */

declare @.retval int

if (@.precommand is not null) EXECUTE(@.precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @.whereand)

select @.retval = @.@.error

if (@.retval = 0)

EXECUTE @.retval = [dbo].sp_MSforeach_worker @.command1, @.replacechar, @.command2, @.command3, 0

if (@.retval = 0 and @.postcommand is not null)

EXECUTE(@.postcommand)

RETURN @.retval

GO

example useage:

EXEC sp_MSforeachsp @.command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

Does anyone have a solution to this problem? Please help......

Could not complete cursor operation !

Hi.
Following eror occurs in a stored procedure..
Could not complete cursor operation because the set options have
changed since the cursor was declared.
What r the possible reasons!
rgds
AjayMaybe you could post the code?

And why are you using a cursor?