Bug?-when using nested subreports in combination with list

Someone please help me with a workaround on this issue:
I have created three reports: (ReportA, ReportB and ReportC).
ReportA includes a subreport (ReportB). ReportB consists of one list. The list is bound to a dataset. The list consists of one subreport (ReportC). ReportC is an empty report.

What is the problem:
When running Report A,The report generation fails when the dataset that is used in ReportB returns zero rows and the report is rendered across two or more pages.
The same problem also occurs when the dataset returns one or more rows and every row are filtered away using a filter expression on the list in ReportB

To make sure that the report is rendered across two or more pages, just add some dummy data to reportA.

When reportB is run, the problem does not occur.

The first page is genereted correctly, but when I navigate to the second page, the error occur.

Anyone experienced the same issue?

This sounds similar to something I am experiencing. I am using SQL 2005.

I have 5 levels of nested reports.

The first page always displays as expected.|||Microsoft has confirmed to me that this issue is a bug and that this should be solved in Service Pack 1.|||

Wow, that's great.

My PM actually solved this on for us with a work around. It's a problem related to a common business issue "asymetrical heirarchy". If there is no data in the 3rd level, it affects the turn down control on the top level. We are currently re-writing our queries to always produce results, and then hide the display when the childID = parentID.

Microsoft SRS 2005 already handles this issue better than most others, but if they're going to fix it all the way, that's great!

|||

Hello,

when RS2005 SP1 wil be released?

Thanks in adavnce

Bug?-when using nested subreports in combination with list

Someone please help me with a workaround on this issue:
I have created three reports: (ReportA, ReportB and ReportC).
ReportA includes a subreport (ReportB). ReportB consists of one list. The list is bound to a dataset. The list consists of one subreport (ReportC). ReportC is an empty report.

What is the problem:
When running Report A,The report generation fails when the dataset that is used in ReportB returns zero rows and the report is rendered across two or more pages.
The same problem also occurs when the dataset returns one or more rows and every row are filtered away using a filter expression on the list in ReportB

To make sure that the report is rendered across two or more pages, just add some dummy data to reportA.

When reportB is run, the problem does not occur.

The first page is genereted correctly, but when I navigate to the second page, the error occur.

Anyone experienced the same issue?

This sounds similar to something I am experiencing. I am using SQL 2005.

I have 5 levels of nested reports.

The first page always displays as expected.|||Microsoft has confirmed to me that this issue is a bug and that this should be solved in Service Pack 1.|||

Wow, that's great.

My PM actually solved this on for us with a work around. It's a problem related to a common business issue "asymetrical heirarchy". If there is no data in the 3rd level, it affects the turn down control on the top level. We are currently re-writing our queries to always produce results, and then hide the display when the childID = parentID.

Microsoft SRS 2005 already handles this issue better than most others, but if they're going to fix it all the way, that's great!

|||

Hello,

when RS2005 SP1 wil be released?

Thanks in adavnce

sql

Bug?-when using nested subreports in combination with list

Someone please help me with a workaround on this issue:
I have created three reports: (ReportA, ReportB and ReportC).
ReportA includes a subreport (ReportB). ReportB consists of one list. The list is bound to a dataset. The list consists of one subreport (ReportC). ReportC is an empty report.

What is the problem:
When running Report A,The report generation fails when the dataset that is used in ReportB returns zero rows and the report is rendered across two or more pages.
The same problem also occurs when the dataset returns one or more rows and every row are filtered away using a filter expression on the list in ReportB

To make sure that the report is rendered across two or more pages, just add some dummy data to reportA.

When reportB is run, the problem does not occur.

The first page is genereted correctly, but when I navigate to the second page, the error occur.

Anyone experienced the same issue?

This sounds similar to something I am experiencing. I am using SQL 2005.

I have 5 levels of nested reports.

The first page always displays as expected.|||Microsoft has confirmed to me that this issue is a bug and that this should be solved in Service Pack 1.|||

Wow, that's great.

My PM actually solved this on for us with a work around. It's a problem related to a common business issue "asymetrical heirarchy". If there is no data in the 3rd level, it affects the turn down control on the top level. We are currently re-writing our queries to always produce results, and then hide the display when the childID = parentID.

Microsoft SRS 2005 already handles this issue better than most others, but if they're going to fix it all the way, that's great!

|||

Hello,

when RS2005 SP1 wil be released?

Thanks in adavnce

BUG with Join hints

I am having problems with doing what seams to be a very easy query. For some reason the SQL Server is trying to do nested loops instead of hash join. I tried to force the use of the hash join using the join hint.

Query 1

select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, getdate()), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current

This query gives an error:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN

Second query is not really what I want but it illustrate that it works fine when getdate() is not used.

Query 2

select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, A.date_enrolled), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current

Is there some problem with using function getdate() ? It works fine in SQL Server 2000

This problem occurs on the SQL Server 2005 SP2 ( 9.00.3050.00 (X64) )
and (9.00.2050)

This problem has to do with some subtle differences between the way SQL Server 2000 handles non-deterministic functions and the way SQL Server 2005 handles it. You can get around it by rewriting your query as follows:

Workaround

with CurrentDate(d) AS (select getdate() d)

select *

from DIM_DATE DD

inner hash join (

select A.student_key,

CONVERT(int, CONVERT(varchar, COALESCE(A.date_withdrawn, (select d from CurrentDate)), 112)) AS date_withdrawn_current

FROM FACT_STUDENT AS A

) SSE on DD.date_key= date_withdrawn_current

Hope that helps.

|||Thanks.

The workaround worked. Finally the query behaves the way it is supposed to. Instead of running for hours it finishes in 12 seconds.

I thought that it has something to do with non deterministic nature of the getdate() but still not sure why would it matter in this case. This is the probe of the hash join, it needs to be scanned only once anyway.

Would that be fixed in SP3 or it works like that by design?
|||

I'm glad that worked. We're digging into it further in the query optimizer team to see why exactly the original query failed. I personally doubt that this will change in SP3 (the risk of changing plans in a service pack is too great) but hopefully we will fix this in future releases.

Thanks for the feedback.

Breaking a nested trigger

Hi,
I have the following 2 tables:
tbl_CustomerEmail1
(
CustomerID1 int,
EmailAddress varchar(200),
Unsubscribe bit
)
tbl_CustomerEmail2
(
CustomerID2 int,
EmailAddress varchar(200),
Unsubscribe bit
)
On each table I have a trigger to subscribe/unsubscribe a customer in the
other table if it exists:
CREATE trigger trg_UnsubscribeCustomer1 on tbl_Customer2
for update as
declare @.unsubscribe tinyint
declare @.EMail varchar(200)
select @.unsubscribe = unsubscribe, @.Email = EmailAddress
from inserted
if update (unsubscribe)
begin
if exists (select email from tbl_Customer1 where EmailAddress = @.Email)
begin
update tbl_Customer1
set Unsubscribe = @.Unsubscribe
where EmailAddress = @.Email
return
end
end
and:
CREATE trigger trg_UnsubscribeCustomer2 on tbl_Customer1
for update as
declare @.unsubscribe tinyint
declare @.EMail varchar(200)
select @.unsubscribe = unsubscribe, @.Email = EmailAddress
from inserted
if update (unsubscribe)
begin
if exists (select email from tbl_Customer2 where EmailAddress = @.Email)
begin
update tbl_Customer2
set Unsubscribe = @.Unsubscribe
where EmailAddress = @.Email
return
end
end
These 2 triggers fire each other to the nested limit (32) if I try to
subscribe/unsubscribe a customer which exists in both tables yet I can't
disallow the nested trigger property on the server because it may break othe
r
applications in other DB's. The return statement used doesn't work. Is there
a way I can do this without disabling the nested trigger property on the
whole server?
Many thanks for your help.Elisabeth,
If the updates that fire these triggers are never
called from yet other triggers, you should be able
to handle this by putting the following line of code
at the very beginning of each trigger:
if trigger_nestlevel() > 2 return
A better solution is probably to redesign these tables
so that there is no need to store the same information
in two separate places, but for now, using the system
function trigger_nestlevel() may take care of things.
Steve Kass
Drew University
Elisabeth wrote:

>Hi,
>I have the following 2 tables:
>tbl_CustomerEmail1
>(
>CustomerID1 int,
>EmailAddress varchar(200),
>Unsubscribe bit
> )
>tbl_CustomerEmail2
>(
>CustomerID2 int,
>EmailAddress varchar(200),
>Unsubscribe bit
> )
>On each table I have a trigger to subscribe/unsubscribe a customer in the
>other table if it exists:
>
>CREATE trigger trg_UnsubscribeCustomer1 on tbl_Customer2
>for update as
>
>declare @.unsubscribe tinyint
>declare @.EMail varchar(200)
>select @.unsubscribe = unsubscribe, @.Email = EmailAddress
>from inserted
>if update (unsubscribe)
>begin
> if exists (select email from tbl_Customer1 where EmailAddress = @.Email)
> begin
> update tbl_Customer1
> set Unsubscribe = @.Unsubscribe
> where EmailAddress = @.Email
> return
> end
>end
>and:
>
>CREATE trigger trg_UnsubscribeCustomer2 on tbl_Customer1
>for update as
>
>declare @.unsubscribe tinyint
>declare @.EMail varchar(200)
>select @.unsubscribe = unsubscribe, @.Email = EmailAddress
>from inserted
>if update (unsubscribe)
>begin
> if exists (select email from tbl_Customer2 where EmailAddress = @.Email)
> begin
> update tbl_Customer2
> set Unsubscribe = @.Unsubscribe
> where EmailAddress = @.Email
> return
> end
>end
>
>These 2 triggers fire each other to the nested limit (32) if I try to
>subscribe/unsubscribe a customer which exists in both tables yet I can't
>disallow the nested trigger property on the server because it may break oth
er
>applications in other DB's. The return statement used doesn't work. Is ther
e
>a way I can do this without disabling the nested trigger property on the
>whole server?
>Many thanks for your help.
>
>
>|||worked a treat! cheers mate.
"Steve Kass" wrote:

> Elisabeth,
> If the updates that fire these triggers are never
> called from yet other triggers, you should be able
> to handle this by putting the following line of code
> at the very beginning of each trigger:
> if trigger_nestlevel() > 2 return
> A better solution is probably to redesign these tables
> so that there is no need to store the same information
> in two separate places, but for now, using the system
> function trigger_nestlevel() may take care of things.
> Steve Kass
> Drew University
>
> Elisabeth wrote:
>
>

both .net transaction and stored procedure transaction together

Hi ALL

I want to know that is it possible that i use the .net transaction and inside of that i use tha stored procedure transaction ,nested transaction with .net and stored procedure?

is it like the nested transaction in stored procedure that we use both 2 transactions in stored procedure or not ?

thanks

To use .NET code in SQL you need to enable CLR Integration via the Surface Area Configuration Utility. This need only be done once.

Next you need to compile your .NET assembly to a DLL. This DLL will be called into MS SQL.

From within MS SQL you load the assembly by using the statement:

CREATE ASSEMBLY <ass_name>
FROM '<path to dll>'

CREATE function <function name> (<any parameters>)
returns <any return value>
[with returns null on null input]
external name <ass_name>.[<dll ns.class>].<dll method>

The .NET assembly can be removed once it is pulled into MS SQL since the binary is from that point onwards referenced internally.

|||Shouldn't a transaction be atomic? Why would you use 2 transactions?|||

The transaction is atomic. Once you import the CLR code into the assembly (a one time processes) and you define the T-SQL function to call the imported code you have an atomic function.

The only difference is that MS SQL's internal .NET Framework will be running the code, rather than the T-SQL interpretur.

Hope this helps.

Al

|||

Thanks, but I was answering on the original question ;-)

|||SQL Server has no support for nested transactions. Nesting of transactions only increments @.@.TRANCOUNT and it is the final commit that has control over the outcome of the entire transaction. You should however take a look at the new TransactionScope class in .NET Framework 2.0 which has promotable transactions concept. It is not clear if you want to link the .NET transaction with the SQL transaction and this can be done by using the TransactionScope class i.e., if multiple resource managers are involved then transaction automatically becomes a distributed transaction.