Interesting problem, Why did this happen with an index?

One of the developers here just came accross an interesting problem, I am
interested to know if there is anyone out there that can explain this to me
as to why the sql server internals are behaving this way:
Table A - > has a trigger for delete that does this: Delete FROM table B.
Table B - > has an FK to table A with On Cascade delete defined.
As is, DELETE FORM TableA works perfect, no problems.
Now, for performance reasons, a unique index is introduced on Table B that
includes the FK.
Now, DELETE FORM TableA causes an error indicating that the nesting of
triggers, stored procedures, etc, is greater than 32.
If anyone can explain why the introduction of an index in this case results
in the nesting limit being exceeded, it clearly created a never ending loop
some how, I am just unclear as to how this is happening.
Thanks,
Rick.Rick wrote:
> One of the developers here just came accross an interesting problem,
> I am interested to know if there is anyone out there that can explain
> this to me as to why the sql server internals are behaving this way:
> Table A - > has a trigger for delete that does this: Delete FROM
> table B.
> Table B - > has an FK to table A with On Cascade delete defined.
> As is, DELETE FORM TableA works perfect, no problems.
> Now, for performance reasons, a unique index is introduced on Table B
> that includes the FK.
> Now, DELETE FORM TableA causes an error indicating that the nesting of
> triggers, stored procedures, etc, is greater than 32.
> If anyone can explain why the introduction of an index in this case
> results in the nesting limit being exceeded, it clearly created a
> never ending loop some how, I am just unclear as to how this is
> happening.
> Thanks,
> Rick.
Is this a real production issue? If so, why do have a trigger on table A
to delete related rows from table B and a cascade FK on Table B to
delete related rows in Table A. Design-wise, it seems messy and
unmanageable. Or is this just a case of a developer trying to break SQL
Server.
Can you provide more details. What rows are being deleted from the
trigger? What is the actual relationship between the tables?
--
David Gugick
Imceda Software
www.imceda.com

interested in wombats

Can someone tell me whether Wombats live only in Australia,
or also on other continents?
Apart from zoos, of course.
23
--
Posted via a free Usenet account from http://www.teranews.comThey are also found in Bass Strait and Flinders Islands.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<wombat@.fancier.net> wrote in message news:m06102800431062@.4ax.com...
> Can someone tell me whether Wombats live only in Australia,
> or also on other continents?
> Apart from zoos, of course.
> 23
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>

Interested in learning more about SQL Server?

Attend PASS!
Attend the largest user-event dedicated to SQL Server! Join PASS for 100+
technical presentations by 80+ user-experts, MVPs and Microsoft developers.
And don't miss Pre-Conference Seminars focused on Microsoft SQL Server
"Yukon." PLUS!!! All PASS Summit attendees will receive a FREE copy of
Microsoft Windows Server 2003 Enterprise Edition! For more information,
including session descriptions and convenient, online registration, visit
www.sqlpass.org/events/seattle03.
This posting is provided "AS IS" with no warranties, and confers no rights.Whats the licensing policy that one is entitled for with that Free copy of
windows 2003 ?
"Tom Rizzo [MSFT]" <thomriz@.microsoft.com> wrote in message
news:ufidnw0jDHA.3172@.tk2msftngp13.phx.gbl...
> Attend PASS!
> Attend the largest user-event dedicated to SQL Server! Join PASS for 100+
> technical presentations by 80+ user-experts, MVPs and Microsoft
developers.
> And don't miss Pre-Conference Seminars focused on Microsoft SQL Server
> "Yukon." PLUS!!! All PASS Summit attendees will receive a FREE copy of
> Microsoft Windows Server 2003 Enterprise Edition! For more information,
> including session descriptions and convenient, online registration, visit
> www.sqlpass.org/events/seattle03.
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
>|||Does http://www.sqlpass.org/events/seattle03/software.cfm provide the
information that you're looking for?
Hope this helps,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer's security.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eU4DP16jDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Whats the licensing policy that one is entitled for with that Free copy of
> windows 2003 ?
> "Tom Rizzo [MSFT]" <thomriz@.microsoft.com> wrote in message
> news:ufidnw0jDHA.3172@.tk2msftngp13.phx.gbl...
> > Attend PASS!
> >
> > Attend the largest user-event dedicated to SQL Server! Join PASS for
100+
> > technical presentations by 80+ user-experts, MVPs and Microsoft
> developers.
> > And don't miss Pre-Conference Seminars focused on Microsoft SQL Server
> > "Yukon." PLUS!!! All PASS Summit attendees will receive a FREE copy of
> > Microsoft Windows Server 2003 Enterprise Edition! For more information,
> > including session descriptions and convenient, online registration,
visit
> > www.sqlpass.org/events/seattle03.
> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> >
> >
>

Integrating KPI in Reporting Services and drill down reports

I'm interested to implement KPIs from Analysis Services 2005 in Reporting Services 2005 in a graphical way as it is seen inside Analysis Services.

how can I archieve this?

and

what is the best way for drilldowns in reporting services?

Thanks for your help,

Roger

OK, my question is general - thats because I'm new to SSRS... So I did some reading and found out that the Matrix is a good way to display drill-downs.

I'm wondering: Do charts offer a possibility for drilldowns?

and I did not found a place to read anything about KPI and presenting them in Reporting Services in a easy and efficient way like you can do it in SSAS.

Anybody can give me a hint?

Thanks, Roger

|||

One way to do support AS2005 KPI's is to place an image control within your data region (e.g. table) and use separate images to represent each state the KPI could be in. Then use an expression to map each KPI image to the correct state based on your the value of an expression as follows:

=iif(Fields!Product_Gross_Profit_Margin_Value.Value = -1, "gauge_asc0",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < -0.5, "gauge_asc1",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < 0, "gauge_asc2",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < 0.5, "gauge_asc3",

"gauge_asc4"))))

Here, guage_ascn represents the various states the KPI could be in. While not 100% integrated to "absorb" AS2005 KPI images, should still let you get the job done.

Alternatively, might want to start from a Report Builder report since that tool offers native support for showing AS2005 KPI's. To see how to edit a Report Builder report using the report authoring tool in VS2005 see http://blogs.msdn.com/bimusings/archive/2005/09/23/473379.aspx

|||Thanks! Regards Tom|||

How to include SQL Server 2005 KPI’s in Reporting Services Reports

(The following is originally a description with screendumps, which unfortunately are not supported. Hopefully usefull anyway ...)

There seems to be an issue about including SQL Server 2005 KPI's in Reporting Services Reports (in Visual Studio) - especially the images associated with the generated parameters, e.g. "-1" leads to "red gauge / traffic light" and so on.

However it is possible to make reports in Report Builder including SQL Server 2005 KPI's.

It has been suggested to include a data source and a Report Server Model in the Visual Studio Project. This should make it possible to build a report including KPI’s. However there still seems to be a problem accessing the measures even though the metadata can be viewed. The Report Wizard does not have an option for including graphics for the indicators – only the numbers to generate the images (“1”, ”-1”, ”0” etc.). Therefore the indicators are not included in the dataset and cannot be included in the reports.

This has lead to the suggestion, that there is an issue with the Report Model functionality in Visual Studio.

There is a work around though ...

First you must associate your KPI’s to measure groups in the Visual Studio project …

Establish an Analysis Services data source on your Report Server …

Note: the Connection String must be typed when the data source is established. (See next-next screen dump.)

From the data sources on the reportserver a Report Model can be generated.

(Double-click the data source and click on generate model.)

Based on this model, reports can be build in Report Builder - including SQL Server 2005 KPI's, including images.

Open Report Builder from the Report Manager. The established data model(s) will be shown as possible data sources.

Build the report …

The reports can be exported as a .rdl-file from Report Builder. Click Save to file … not Save as …

If you choose Save as … the report is saved on the server.

Import the .rdl-file in your Visual Studio project …

The layout of the report can be edited in Visual Studio. The Data and Preview panes return an error when clicked.

Save and deploy the report … Right-click the project name / Properties to view where the report is deployed to by default (e.g. http://localhost/reportserver/reports).

The described solution is definitely a work-around – it is not an optimal solution. There are two major downsides with the solution: the reports cannot be previewed in Visual Studio and the dataset cannot be changed in Visual Studio.

Integrating KPI in Reporting Services and drill down reports

I'm interested to implement KPIs from Analysis Services 2005 in Reporting Services 2005 in a graphical way as it is seen inside Analysis Services.

how can I archieve this?

and

what is the best way for drilldowns in reporting services?

Thanks for your help,

Roger

OK, my question is general - thats because I'm new to SSRS... So I did some reading and found out that the Matrix is a good way to display drill-downs.

I'm wondering: Do charts offer a possibility for drilldowns?

and I did not found a place to read anything about KPI and presenting them in Reporting Services in a easy and efficient way like you can do it in SSAS.

Anybody can give me a hint?

Thanks, Roger

|||

One way to do support AS2005 KPI's is to place an image control within your data region (e.g. table) and use separate images to represent each state the KPI could be in. Then use an expression to map each KPI image to the correct state based on your the value of an expression as follows:

=iif(Fields!Product_Gross_Profit_Margin_Value.Value = -1, "gauge_asc0",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < -0.5, "gauge_asc1",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < 0, "gauge_asc2",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < 0.5, "gauge_asc3",

"gauge_asc4"))))

Here, guage_ascn represents the various states the KPI could be in. While not 100% integrated to "absorb" AS2005 KPI images, should still let you get the job done.

Alternatively, might want to start from a Report Builder report since that tool offers native support for showing AS2005 KPI's. To see how to edit a Report Builder report using the report authoring tool in VS2005 see http://blogs.msdn.com/bimusings/archive/2005/09/23/473379.aspx

|||Thanks! Regards Tom|||

How to include SQL Server 2005 KPI’s in Reporting Services Reports

(The following is originally a description with screendumps, which unfortunately are not supported. Hopefully usefull anyway ...)

There seems to be an issue about including SQL Server 2005 KPI's in Reporting Services Reports (in Visual Studio) - especially the images associated with the generated parameters, e.g. "-1" leads to "red gauge / traffic light" and so on.

However it is possible to make reports in Report Builder including SQL Server 2005 KPI's.

It has been suggested to include a data source and a Report Server Model in the Visual Studio Project. This should make it possible to build a report including KPI’s. However there still seems to be a problem accessing the measures even though the metadata can be viewed. The Report Wizard does not have an option for including graphics for the indicators – only the numbers to generate the images (“1”, ”-1”, ”0” etc.). Therefore the indicators are not included in the dataset and cannot be included in the reports.

This has lead to the suggestion, that there is an issue with the Report Model functionality in Visual Studio.

There is a work around though ...

First you must associate your KPI’s to measure groups in the Visual Studio project …

Establish an Analysis Services data source on your Report Server …

Note: the Connection String must be typed when the data source is established. (See next-next screen dump.)

From the data sources on the reportserver a Report Model can be generated.

(Double-click the data source and click on generate model.)

Based on this model, reports can be build in Report Builder - including SQL Server 2005 KPI's, including images.

Open Report Builder from the Report Manager. The established data model(s) will be shown as possible data sources.

Build the report …

The reports can be exported as a .rdl-file from Report Builder. Click Save to file … not Save as …

If you choose Save as … the report is saved on the server.

Import the .rdl-file in your Visual Studio project …

The layout of the report can be edited in Visual Studio. The Data and Preview panes return an error when clicked.

Save and deploy the report … Right-click the project name / Properties to view where the report is deployed to by default (e.g. http://localhost/reportserver/reports).

The described solution is definitely a work-around – it is not an optimal solution. There are two major downsides with the solution: the reports cannot be previewed in Visual Studio and the dataset cannot be changed in Visual Studio.