Multi-page report: February 2012

Saturday, February 25, 2012

Multiple Column Indexed Select?

There is a large table with a multiple-column index. What is the
proper way to search using these columns so the multiple-column index
is used?
Thanks.
Can you be a little more specific? You normally don't have to do anything
special for sql server to utilize an index as long as it is useful for the
query. Whether or not the index is used depends on the query, the index,
how many rows in the table, how selective the index / query is etc, etc. If
you post the DDL for the table and the query you are looking to issue maybe
we can make a suggestion.
Andrew J. Kelly SQL MVP
"localhost" <primpilus@.cohort.ces> wrote in message
news:jd4cr05eucf55m6kq2rq716e8kpkqb1069@.4ax.com...
> There is a large table with a multiple-column index. What is the
> proper way to search using these columns so the multiple-column index
> is used?
> Thanks.
>
|||localhost wrote:
> There is a large table with a multiple-column index. What is the
> proper way to search using these columns so the multiple-column index
> is used?
> Thanks.
Nothing really. You just need to be aware that in order to use the
index, you have to include columns in the query from left to right (as
they appear in the index). You don't need to include all columns in the
query, but once you skip a column, the index is only useful up to that
point.
For example:
Index: (col1, col2, col3)
Query: Where col1 = 5 and col2 = 6 and col3 = 7 (index used to its
fullest)
Query: Where col1 = 5 and col3 = 7 (index only uses col1)
Query: Where col2 = 5 and col1 = 6 (index uses col1 and col2)
Query: Where col3 = 7 (index not used at all)
Query: Where col2 = 7 (index not used at all)
Query: Where col1 = 7 (index uses col1)
But Andrew's comments are correct. Just because a query can make use of
an index, doesn't mean it will be used.
David Gugick
Imceda Software
www.imceda.com

Multiple Column in two pages

I have 20 columns in my report. I want 10 columns in odd pages and 10 columns in the even pages. In the section expert i have two page headers and two details. i used the suppress formula pagenumber mod 2 to find the odd and even pages. My header is displayed correctly but my detail is displaying only the odd pages. Please help me to display the records in the even pages.
thanks...fdd|||Can you post the formula used?|||fdd
What does it mean?|||i don't understand.

multiple column histograms in Yukon

Hi,
I have a simple query (sql 2000):
select * from table where feld1 = 'X' and feld2 <> 'Y'
Both fields are not very selective, but together they are
very selective and will find only 4 rows out of 1700000
rows. I have an multiple column index on both fields but
the optimizer chooses always a table scan. I heard Yukon
will handle such situations much better (multiple column
histograms) but I tried the version I got from PDC and
YUKON is still choosing a table scan too.
Any hints?
Andreas
Austria> the optimizer chooses always a table scan. I heard Yukon
> will handle such situations much better (multiple column
> histograms) but I tried the version I got from PDC and
> YUKON is still choosing a table scan too.
I wouldn't try to judge the delivery of all of Yukon's enhancements based
solely on the PDC build. I'm sure many of the optimizations are
forthcoming.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Multiple column full text search

Is there a way to specify which column weights more when doing full text
search..
For example, I have three column, filename, description and data. I did
full text index on all three column, but want to list the hits in filename
first, is there a way to do it sql 2005?
--Xin Chen
your might want to check out some of these postings for examples of how
to do this.
http://groups-beta.google.com/groups...ff&qt_s=Search
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

Multiple Collations in SQL Server 2000

Hi all.
Any assistance regarding this would be greatly appreciated.
I currently have my db's set up using the SQL_Latin1_general_CP1253_CI_AI
which is the Greek Character Code. We have a new business requirement where
we need to store Swedish characters as well. Both greek Characters and
Swedishc characters would need to be stored in the same table.
My question is this - could I use the 850 (multilingual) character code to
handle both the Greek and Swedish and also English Characters or would I have
to do something else?
Many thanks.I suggest you use Unicode datatypes for this. Note that the collation still determines some
nationalization aspects, namely sort order.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"xyberdobs" <xyberdobs@.discussions.microsoft.com> wrote in message
news:9DA9110A-2F37-4878-8702-CB865326FB3F@.microsoft.com...
> Hi all.
> Any assistance regarding this would be greatly appreciated.
> I currently have my db's set up using the SQL_Latin1_general_CP1253_CI_AI
> which is the Greek Character Code. We have a new business requirement where
> we need to store Swedish characters as well. Both greek Characters and
> Swedishc characters would need to be stored in the same table.
> My question is this - could I use the 850 (multilingual) character code to
> handle both the Greek and Swedish and also English Characters or would I have
> to do something else?
> Many thanks.|||Hi Tibor.
Many thanks for that.
Just another question - would the developers need to state the collation
when retrieving the values from the table, or would it be fine to do a std
SELECT statement?
Thanks
"Tibor Karaszi" wrote:
> I suggest you use Unicode datatypes for this. Note that the collation still determines some
> nationalization aspects, namely sort order.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "xyberdobs" <xyberdobs@.discussions.microsoft.com> wrote in message
> news:9DA9110A-2F37-4878-8702-CB865326FB3F@.microsoft.com...
> > Hi all.
> > Any assistance regarding this would be greatly appreciated.
> >
> > I currently have my db's set up using the SQL_Latin1_general_CP1253_CI_AI
> > which is the Greek Character Code. We have a new business requirement where
> > we need to store Swedish characters as well. Both greek Characters and
> > Swedishc characters would need to be stored in the same table.
> > My question is this - could I use the 850 (multilingual) character code to
> > handle both the Greek and Swedish and also English Characters or would I have
> > to do something else?
> >
> > Many thanks.
>
>|||> Just another question - would the developers need to state the collation
> when retrieving the values from the table, or would it be fine to do a std
> SELECT statement?
It depends... In what way would not the collation for the column(s) in question satisfy the
requirements? If all is OK, then fine. If, you for instance need some different sorting than what
the columns you sort over defines, then yes you would need to add COLLATE tothe ORDER BY clause.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"xyberdobs" <xyberdobs@.discussions.microsoft.com> wrote in message
news:92EC3872-AA0D-4B71-A5E9-FC8A123714E1@.microsoft.com...
> Hi Tibor.
> Many thanks for that.
> Just another question - would the developers need to state the collation
> when retrieving the values from the table, or would it be fine to do a std
> SELECT statement?
> Thanks
> "Tibor Karaszi" wrote:
>> I suggest you use Unicode datatypes for this. Note that the collation still determines some
>> nationalization aspects, namely sort order.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "xyberdobs" <xyberdobs@.discussions.microsoft.com> wrote in message
>> news:9DA9110A-2F37-4878-8702-CB865326FB3F@.microsoft.com...
>> > Hi all.
>> > Any assistance regarding this would be greatly appreciated.
>> >
>> > I currently have my db's set up using the SQL_Latin1_general_CP1253_CI_AI
>> > which is the Greek Character Code. We have a new business requirement where
>> > we need to store Swedish characters as well. Both greek Characters and
>> > Swedishc characters would need to be stored in the same table.
>> > My question is this - could I use the 850 (multilingual) character code to
>> > handle both the Greek and Swedish and also English Characters or would I have
>> > to do something else?
>> >
>> > Many thanks.
>>

Multiple Collations in SQL Server 2000

Multiple col ina single col

I getting trouble to combine two col into a sinle col how can I do that??You will have to use text concatenation.
(Field1 & Field2).|||Hi,

Select field1 + field2 from tabname

Thanks & Regards

(M. Nedu)
mneduu@.gmail.com

Multiple Clusters with shared passive nodes

Is it possible to take 5 servers and make 4 individual clusters and
share the 5th server amongst all of them as the failover node? These
clusters would be running SQL 2000 and attached to a SAN enviroment.
Sort of - I'd create all 5 servers as members of a single cluster, then
set up 4 instances of SQL Server with cluster server member 1 configured
to be able to run instance 1, member 2 to run instance 2, member 3 to
run instance 3, member 4 to run instance 4, and member 5 to run
instances 1 2 3 and 4.
While you are able to have cluster member 5 be the failover for all the
other instances, there might be a better way to set which member servers
can run each instance, but its not difficult to change this at a later
date - it might not be an optimal configuration if all the instances are
of different memory needs or if you want to handle a situation where
more than one cluster member might be unavailable at a time.
Good luck,
Tony Sebion
"onefastmustang" <onefastmustang@.gmail.com> wrote in message
news:1128006737.120461.270030@.g49g2000cwa.googlegr oups.com:

> Is it possible to take 5 servers and make 4 individual clusters and
> share the 5th server amongst all of them as the failover node? These
> clusters would be running SQL 2000 and attached to a SAN enviroment.
|||So in this architecture then I take it that each instance of SQL will
have to be in its own cluster group right?
So in cluster administrator I would have SQL1 Group, SQL2 Group, SQL3
Group and SQL4 Group? Each with its own SQL IP address and its own
drives? But also have a main Cluster Group that contains the Quorum
drive and Cluster management IP?
Thanks
|||Yup, that's exactly what I was thinking.
Tony
"onefastmustang" <onefastmustang@.gmail.com> wrote in message
news:1128010958.220046.38020@.g14g2000cwa.googlegro ups.com:

> So in this architecture then I take it that each instance of SQL will
> have to be in its own cluster group right?
> So in cluster administrator I would have SQL1 Group, SQL2 Group, SQL3
> Group and SQL4 Group? Each with its own SQL IP address and its own
> drives? But also have a main Cluster Group that contains the Quorum
> drive and Cluster management IP?
> Thanks
|||There are a couple of Got'chas. What Tony is referring to is called a
"Round Robin" multi-instanced cluster. The one you described is referred to
as an "N + I" multi-instanced cluster. Both are supported; however, only
SS2K 64-bit allows more than 4 nodes in a SQL Server cluster. Windows 2K
Advanced Server only allows 2-node clusters. 2K Datacenter Edition supports
4-node clusters. Both Windows 2K3 Enterprise and Datacenter Editions
support 8-node clusters, but 32-bit SQL Server 2K does not unless the you
use the IA-64 edition.
PRB: Virtual SQL Server 2000 installation fails on cluster that has eight
nodes
http://support.microsoft.com/default...b;en-us;811054
Windows Server 2003 Server Cluster Architecture
http://www.microsoft.com/windowsserv...ercluster.mspx
Good luck.
Sincerely,
Anthony Thomas

"Tony Sebion" <tony@.sebion.com> wrote in message
news:3q2j44Fctu5kU1@.individual.net...
Yup, that's exactly what I was thinking.
Tony
"onefastmustang" <onefastmustang@.gmail.com> wrote in message
news:1128010958.220046.38020@.g14g2000cwa.googlegro ups.com:

> So in this architecture then I take it that each instance of SQL will
> have to be in its own cluster group right?
> So in cluster administrator I would have SQL1 Group, SQL2 Group, SQL3
> Group and SQL4 Group? Each with its own SQL IP address and its own
> drives? But also have a main Cluster Group that contains the Quorum
> drive and Cluster management IP?
> Thanks

Multiple Client on the same ReportServer

Hi !

Once again i have a question . We have a Web application and lately we have install ReportBuilder to allow some of our Clients to create their own reports. I was wondering how to "hide" reports made by a Client from another client ? For exemple if a Client 'A' create a report and save it on his folder. When the client 'B' open reportBuilder and select the option OPEN, currently the Client 'B' can see the folder of the Client 'A'. So the Client 'B' could modify a report that belong to another client.

I was looking in the Report Manager but i couldn't find how to 'hide' a folder from user using the security option. Maybe we haven't configuer ReportServer propely. How can we solve this problem ? Is there a good website that explain how to do this ?I've forgot to ask something else. Currently we have made over 600 reports. All our Clients can use thoses reports but we don't want to allow them to modify them. Is it possible to hide a project from all our Client ?|||Anyone ? I really need to find a solution because we can let a Client have access to reports of another client.

Thanks ! Sorry about my bad English ^_^

Multiple Client Database Model

Does anyone have a sample of what a database would look like that would support multiple clients in a single ASP.net application?

Thank you,

Why would this be any different from any other database?
A database is meant to be shared amongst multiple clients. That'sone of the main reasons for using databases instead of storing data inflat files or other application-based structures. Databasessupport transactions and locking that keep clients from destroying eachothers' work. Perhaps I'm misunderstanding your question,though? Are you looking for an example of a specific type ofdatabase?

|||

Really what I was hoping was an example of a database that would be used on a web host that could maintain more than one client using it. For example two or more companies using the same hosted database. What is done to the data tables to allow for the separation of the companies and their clients, etc...

I hope this explains it better.

Thank you,

|||Okay, that makes a lot more sense Big Smile [:D]

This can be achieved using what's known as "row-based security". See this article:
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
Note that there are some serious caveats in a row-based securityscheme, and if you really need heavy security it's probably better todo a seperate database per client. Steve Kass (SQL Server MVP)has posted some interesting ways he's found to hack row-based securityschemes -- unfortunately, the SQL Server engine doesn't realize you'redoing row-based security so it doesn't know not to show certain data inerrors/etc if you give it the right inputs.

|||Thank you, I will check it out.

multiple charts based on employees in report

I have two charts that are subreports. I want to see if I can do one of two things.

Give the user the ability once the report processes to change the employee name and refresh the chart. Format the subreports to repeat the charts for as many employees that are in the main report. Sometimes there will be 8 other times there will be as many as 20.

Yes. For 1, you can pass the employee name through report parameter and let the user to change the parameter value. For 2, you can put the subreports into a list, and set the grouping on the list to be the employee name.

|||Could you give me a little more detail on the solution for option 2?|||Int he example of a table you place your chart in the details pane. For every employee listed (detailed grouping) or if you want to do it on a grouping level for every grouping value, none chart will be displayed on the based data for the group / detailed row.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

multiple charts

Can I combine two charts together?
For example, I want to have a line chart and a bar chart on the same graph.
Is it possible?
ThanksSQL RS2K doesn't currently support dual axis, but a few creative folks have
come up with a workaround to the problem.
Ivolves creating a report composed of two seperate graphs; 1st graph is a
bar chart; 2nd graph is a line chart. Place one directly above the other.
Then Set BackgroundColor property of each graph so it is transparent and
placed them both on top of a colored rectangle. This gives the appearance
of a single graph having dual axis.
BTW - dual Y axis graphs are planned for the next release (SQL 2005 RS).
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:04026512-0870-46F3-A42A-77CB86580986@.microsoft.com...
> Can I combine two charts together?
> For example, I want to have a line chart and a bar chart on the same
> graph.
> Is it possible?
> Thanks|||Thanks, will try it.
"John H. Miller" wrote:
> SQL RS2K doesn't currently support dual axis, but a few creative folks have
> come up with a workaround to the problem.
> Ivolves creating a report composed of two seperate graphs; 1st graph is a
> bar chart; 2nd graph is a line chart. Place one directly above the other.
> Then Set BackgroundColor property of each graph so it is transparent and
> placed them both on top of a colored rectangle. This gives the appearance
> of a single graph having dual axis.
> BTW - dual Y axis graphs are planned for the next release (SQL 2005 RS).
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:04026512-0870-46F3-A42A-77CB86580986@.microsoft.com...
> > Can I combine two charts together?
> > For example, I want to have a line chart and a bar chart on the same
> > graph.
> > Is it possible?
> >
> > Thanks
>
>|||The combination of bar and line charts is not supported; however, combining
column and line charts is supported.
Example RDL file is copy&pasted to the end of this message (in case your
newsreader cannot read
attachments). You might also search in BOL for "hybrid line-column chart" -
it is the "plot data as line" option in the chart value dialog.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
----
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="newChart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>20</Inclination>
<Shading>Real</Shading>
<WallThickness>25</WallThickness>
<DrawingStyle>Cylinder</DrawingStyle>
<Clustered>true</Clustered>
</ThreeDProperties>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Firebrick</Color>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Excel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!UnitsInStock.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!UnitsOnOrder.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
</DataLabel>
<Marker>
<Type>Square</Type>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Caption>Products</Caption>
<Style />
</Title>
<Style>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style>
<BorderWidth>
<Default>1.5pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<MajorInterval>5</MajorInterval>
<MinorInterval>1</MinorInterval>
<CrossAt>0</CrossAt>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<Type>Column</Type>
<Top>0.25in</Top>
<Title>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Title>
<Width>5.625in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup2">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!CategoryName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProductName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>5.25in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Stock</Label>
</StaticMember>
<StaticMember>
<Label>Order</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>OldLace</BackgroundColor>
</Style>
</PlotArea>
<Left>0.25in</Left>
<ValueAxis>
<Axis>
<Title>
<Caption>Amount</Caption>
<Style />
</Title>
<Style>
<BorderColor>
<Default>IndianRed</Default>
</BorderColor>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>Blue</Default>
</BorderColor>
<BorderStyle>
<Default>Dashed</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>CornflowerBlue</Default>
</BorderColor>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MinorTickMarks>Cross</MinorTickMarks>
<Min>0</Min>
<MajorInterval>20</MajorInterval>
<MinorInterval>5</MinorInterval>
<Interlaced>true</Interlaced>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>8.75in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierID">
<DataField>SupplierID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="QuantityPerUnit">
<DataField>QuantityPerUnit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="UnitsOnOrder">
<DataField>UnitsOnOrder</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ReorderLevel">
<DataField>ReorderLevel</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="Discontinued">
<DataField>Discontinued</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="NullUnits">
<DataField>NullUnits</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 *, NULL AS NullUnits
FROM [Alphabetical list of products]
WHERE (UnitsOnOrder > 0)</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||John,
I've already played a lot with multiple charts, using transparency. My
problem is, that the specified chart size always includes the label values,
the title, the legend, etc.
This means, that depending on the max. length of these items, the plot areas
are of different size, so that in most cases, I'm not able to have the plot
areas exactly on the same place. Do you have any solution for that?
Thanks
"John H. Miller" wrote:
> SQL RS2K doesn't currently support dual axis, but a few creative folks have
> come up with a workaround to the problem.
> Ivolves creating a report composed of two seperate graphs; 1st graph is a
> bar chart; 2nd graph is a line chart. Place one directly above the other.
> Then Set BackgroundColor property of each graph so it is transparent and
> placed them both on top of a colored rectangle. This gives the appearance
> of a single graph having dual axis.
> BTW - dual Y axis graphs are planned for the next release (SQL 2005 RS).
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:04026512-0870-46F3-A42A-77CB86580986@.microsoft.com...
> > Can I combine two charts together?
> > For example, I want to have a line chart and a bar chart on the same
> > graph.
> > Is it possible?
> >
> > Thanks
>
>

Multiple Chart styles

Hi all
Is there any way to include two different styles (bar & lines) i
one chart?
I know it might sound too "excel" but I really hope there is
workaround..
By the way, we can't add totals or subtotal into a bar chart, can'
we?
are these ideas too crazy or we encounter software limitations
perhaps we have been spoiled by excel..
Thanks for your helpThe combination of bar and line charts is not supported; however, combining
column and line charts is supported. A sample RDL file is copy&pasted to the
end of this message (in case your newsreader cannot read attachments). You
might also search in BOL for "hybrid line-column chart" - it is the "plot
data as line" option in the chart value dialog.
Regarding subtotals in charts: I'm not sure if this would be really useful
because if you draw your detail data and the subtotal along the same axis,
the subtotal bar is typically very large compared to the detail bars.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"lumaar" <luciano_arias@.yahoo-dot-com.no-spam.invalid> wrote in message
news:pr2dnfu98v9f4BDfRVn_vg@.giganews.com...
> Hi all,
> Is there any way to include two different styles (bar & lines) in
> one chart?
> I know it might sound too "excel" but I really hope there is a
> workaround...
> By the way, we can't add totals or subtotal into a bar chart, can't
> we?
> are these ideas too crazy or we encounter software limitations?
> perhaps we have been spoiled by excel...
> Thanks for your help!
> L
==================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="newChart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>20</Inclination>
<Shading>Real</Shading>
<WallThickness>25</WallThickness>
<DrawingStyle>Cylinder</DrawingStyle>
<Clustered>true</Clustered>
</ThreeDProperties>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Firebrick</Color>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Excel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!UnitsInStock.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!UnitsOnOrder.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
</DataLabel>
<Marker>
<Type>Square</Type>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Caption>Products</Caption>
<Style />
</Title>
<Style>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style>
<BorderWidth>
<Default>1.5pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<MajorInterval>5</MajorInterval>
<MinorInterval>1</MinorInterval>
<CrossAt>0</CrossAt>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<Type>Column</Type>
<Top>0.25in</Top>
<Title>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Title>
<Width>5.625in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup2">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!CategoryName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProductName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>5.25in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Stock</Label>
</StaticMember>
<StaticMember>
<Label>Order</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>OldLace</BackgroundColor>
</Style>
</PlotArea>
<Left>0.25in</Left>
<ValueAxis>
<Axis>
<Title>
<Caption>Amount</Caption>
<Style />
</Title>
<Style>
<BorderColor>
<Default>IndianRed</Default>
</BorderColor>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>Blue</Default>
</BorderColor>
<BorderStyle>
<Default>Dashed</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>CornflowerBlue</Default>
</BorderColor>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MinorTickMarks>Cross</MinorTickMarks>
<Min>0</Min>
<MajorInterval>20</MajorInterval>
<MinorInterval>5</MinorInterval>
<Interlaced>true</Interlaced>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>8.75in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierID">
<DataField>SupplierID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="QuantityPerUnit">
<DataField>QuantityPerUnit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="UnitsOnOrder">
<DataField>UnitsOnOrder</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ReorderLevel">
<DataField>ReorderLevel</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="Discontinued">
<DataField>Discontinued</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="NullUnits">
<DataField>NullUnits</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 *, NULL AS NullUnits
FROM [Alphabetical list of products]
WHERE (UnitsOnOrder > 0)</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>

Multiple character separation

Here's my situation, I have a database of stock trades that I need to separate into two lists. one containing trades in stocks that have 4 letters in thier symbols, and the other which has any trades in symbols that have 1,2,or 3 symbols.

I cant seem to figure this one out, can anyone offer any help?Can you give an example of what is stored in your database and what you want the output to be ?|||select * from trade_table where datalength(cast(stock_symbol as varchar(4))) = 4

select * from trade_table where datalength(cast(stock_symbol as varchar(4))) < 4|||thanks, that looks to be exactly what I needed.

-Chamber

Multiple Cascade Paths Error

Can anyone tell me why on earth you can't have two cascade paths to a table
? This essentially means we cannot use RI and have to maintain triggers
for our DB.
I see that they haven't fixed it in 2005 either.Hi
You probably want to use a superset, but without DDL your question not
clear. http://www.aspfaq.com/etiquett_e.asp?id=5006
Also check out:
http://tinyurl.com/486q3
If this does solve your issue then it is a design problem and not a problem
with the RDBMS.
John
"Adrian Parker" wrote:

> Can anyone tell me why on earth you can't have two cascade paths to a tabl
e
> ? This essentially means we cannot use RI and have to maintain triggers
> for our DB.
> I see that they haven't fixed it in 2005 either.
>
>|||Please read the following pages.. you'll understand the problem then
first Microsofts page (I love their workaround)
http://support.microsoft.com/defaul...&NoWebContent=1
Then the WindowsITPro page that explains the problem in more detail
http://www.windowsitpro.com/Article...5520/25520.html
We migrated to SQL Server from sybase and oracle, which both handle the
scenarios correctly.
And no, I doubt we're going to rewrite a 10 year old application with 600
tables just to cope with a limitation in SQL Server,we'll have to continue
using triggers, which is really annoying.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:FD2C115B-CCD3-4864-8CDC-F6402B62A863@.microsoft.com...
> Hi
> You probably want to use a superset, but without DDL your question not
> clear. http://www.aspfaq.com/etiquett_e.asp?id=5006
> Also check out:
> http://tinyurl.com/486q3
> If this does solve your issue then it is a design problem and not a
> problem
> with the RDBMS.
> John
> "Adrian Parker" wrote:
>|||Hi
The example can be re-modelled as
Use tempdb
go
create table table1 (user_ID integer not null primary key, user_name
char(50) not null)
go
create table table2 (author_ID integer not null primary key,
author_name char(50) not null )
go
create table table3 (author_ID integer not null primary key, Action
varchar(20) not null check (Action =3D 'Created' OR Action =3D 'Last
Modified'), User_id integer not null )
alter table table3 add constraint fk_one foreign key (User_id)
references table1 (user_ID) on delete cascade on update cascade
go
Creating a view may remove the need for some of the code changes.
John
Adrian Parker wrote:
> Please read the following pages.. you'll understand the problem then
> first Microsofts page (I love their workaround)
>
http://support.microsoft.com/defaul...rt.microsoft.c=
om:80/support/kb/articles/q321/8/43.asp&NoWebContent=3D1
> Then the WindowsITPro page that explains the problem in more detail
> http://www.windowsitpro.com/Article...5520/25520.html
> We migrated to SQL Server from sybase and oracle, which both handle
the
> scenarios correctly.
> And no, I doubt we're going to rewrite a 10 year old application with
600
> tables just to cope with a limitation in SQL Server,we'll have to
continue
> using triggers, which is really annoying.
>
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:FD2C115B-CCD3-4864-8CDC-F6402B62A863@.microsoft.com...
not
to a
triggers

Multiple Cascade Paths - Fails on SQL Server 2000

This is an old issue, which I chose to ignore, since it was for a temporary
app with a handful of users (famous last words, I know).
The "multiple foreign keys" post today got me thinking about it, and I
figured it is worth looking for a solution.
I will post DDL at the end, but the theory is the main point here, so I hope
my description will suffice.
Please forgive the camel case naming convention, and the silly tbl prefixes.
I do not like it either, but it is a standard in our department.
tblParameter has two FK constraints.
One references tblReport.strReportID
One references tblLookupProcedures.strLookupProcedure
Both tblReport and tblLookupProcedures have a FK reference to
tblDatabaseConnections.strConnectionName
Ideally, all of these constraints have ON UPDATE CASCADE, but the second
constraint on tblParameter will error out with this message:
****
Introducing FOREIGN KEY constraint 'FK_tblParameter_tblLookupProcedures' on
table 'tblParameter' may cause cycles or multiple cascade paths. Specify ON
DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
constraints.
****
I believe the error is caused because updates to the PK in
tblDatabaseConnections triggers updates to the FK in two tables, each of
which are referenced by tblParameter. Now the references are not on the
same columns, but SQL Server seems to think this could lead to circular
references.
The questions are:
1. Is this against RD theory, or is it just a quirk of SQL Server?
2. Is there a setting that will allow this?
3. What is the usual workaround? A trigger is the only thing that I could
think of.
Here is the DDL, scaled down to demonstrate the issue.
CREATE TABLE [dbo].[tblDatabaseConnections] (
[strConnectionName] [varchar] (10) PRIMARY KEY NOT NULL ,
[strDatabase] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLookupProcedures] (
[strLookupProcedure] [varchar] (50) PRIMARY KEY NOT NULL ,
[strProcedureDescription] [varchar] (100) NOT NULL ,
[strConnectionName] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblReport] (
[strReportID] [varchar] (10) PRIMARY KEY NOT NULL ,
[strReportDescr] [varchar] (50) NULL ,
[strConnectionName] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblParameter] (
[strReportID] [varchar] (10) NOT NULL ,
[strParamID] [varchar] (20) NOT NULL ,
[strLookupProcedure] [varchar] (50) NOT NULL
PRIMARY KEY ([strReportID],[strParamID])
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLookupProcedures] ADD
CONSTRAINT [FK_tblLookupProcedures_tblDatabaseConne
ctions] FOREIGN KEY
([strConnectionName]
) REFERENCES [dbo].[tblDatabaseConnections] ([strConnectionName]
) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblReport] ADD
CONSTRAINT [FK_tblReport_tblDatabaseConnections] FOREIGN KEY
([strConnectionName]
) REFERENCES [dbo].[tblDatabaseConnections] ([strConnectionName]
) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblParameter] ADD
CONSTRAINT [FK_tblParameter_tblReport] FOREIGN KEY
([strReportID]
) REFERENCES [dbo].[tblReport] ([strReportID]
) ON UPDATE CASCADE
GO
--This constraint fails due to a perceived cyclical reference
ALTER TABLE [dbo].[tblParameter] ADD CONSTRAINT
[FK_tblParameter_tblLookupProcedures] FOREIGN KEY
([strLookupProcedure]
) REFERENCES [dbo]. [tblLookupProcedures]([strLookupProcedur
e]
) on update cascade
GO
-- if we remove on update cascade, the constraint gets created
ALTER TABLE [dbo].[tblParameter] ADD CONSTRAINT
[FK_tblParameter_tblLookupProcedures_NOC
ASCADE] FOREIGN KEY
([strLookupProcedure]
) REFERENCES [dbo]. [tblLookupProcedures]([strLookupProcedur
e]
)
GO
drop table [dbo].[tblParameter];
drop table [dbo].[tblReport];
drop table [dbo].[tblLookupProcedures];
drop table [dbo].[tblDatabaseConnections];I sketched it all out on some scrap paper. I think the real issue is that th
e
row that would get deleted on tblParameter depends upon which path the
cascade took down the tree. I can see how deleting a value of
strConnectionName from tblDatabaseConnections could result in different
rows being deleted in tblParameter through the cascade depending on the path
,
because the foreign keys in tblParameter point to different columns in
tblLookupProcedures and tblReport. (I hope that made sense).
Another thing is that SQL will determine if any constraints would be
violated by the cascading action, and rollback any transactions that
initiated the cascade. What if one path violated some constraint on
tblParameter, but the other path didn't?
Just thinking out loud.
"Jim Underwood" wrote:

> This is an old issue, which I chose to ignore, since it was for a temporar
y
> app with a handful of users (famous last words, I know).
>|||Interesting points, but I am using cascade update, not cascade delete, so
the updates would not filter down through the levels, they would stop at the
first table, since different columns are affected.
Just for chuckles I tried adding
ON DELETE NO ACTION
to the constraints but the results were the same.
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:5C7F6B3D-DC1B-4F64-9F4A-16EE6F66D51C@.microsoft.com...
> I sketched it all out on some scrap paper. I think the real issue is that
the
> row that would get deleted on tblParameter depends upon which path the
> cascade took down the tree. I can see how deleting a value of
> strConnectionName from tblDatabaseConnections could result in different
> rows being deleted in tblParameter through the cascade depending on the
path,
> because the foreign keys in tblParameter point to different columns in
> tblLookupProcedures and tblReport. (I hope that made sense).
> Another thing is that SQL will determine if any constraints would be
> violated by the cascading action, and rollback any transactions that
> initiated the cascade. What if one path violated some constraint on
> tblParameter, but the other path didn't?
> Just thinking out loud.
>
> --
> "Jim Underwood" wrote:
>
temporary
>|||As far as relational set theory goes, an update is really just a delete /
insert pair. (There's no 'updated' virtual table you can use inside a
trigger).
"Jim Underwood" wrote:

> Interesting points, but I am using cascade update, not cascade delete, so
> the updates would not filter down through the levels, they would stop at t
he
> first table, since different columns are affected.
> Just for chuckles I tried adding
> ON DELETE NO ACTION
> to the constraints but the results were the same.
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:5C7F6B3D-DC1B-4F64-9F4A-16EE6F66D51C@.microsoft.com...
> the
> path,
> temporary
>
>|||Ahhhh...
Now that makes sense.
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:6F31E2E5-9B2F-4EF3-8D53-2B44F6403D0B@.microsoft.com...
> As far as relational set theory goes, an update is really just a delete /
> insert pair. (There's no 'updated' virtual table you can use inside a
> trigger).
> "Jim Underwood" wrote:
>
so
the
message
that
different
the|||Jim,
I have had discussions about this (in one of these newsgroups) when it
was introduced in SQL Server 2000. SQL Server really takes a very
simplistic view. It acts as if each table only has one column when it
determines if there is a potential circular reference. This is really
silly if you ask me, because it means there is hardly any real life
application that makes it worth your while to use it.
AFAIK there is not setting around this. You must avoid this supposed
circular reference. One way of doing that is to define the 'offending'
foreign key constraint as not cascading. But obviously that does not
achieve the desired cascading.
Of course triggers are always an options, but than means disabling the
foreign key constraint. So it is always a compromise...
By the way: has any of this changed in SQL Server 2005?
Gert-Jan
Jim Underwood wrote:
> This is an old issue, which I chose to ignore, since it was for a temporar
y
> app with a handful of users (famous last words, I know).
> The "multiple foreign keys" post today got me thinking about it, and I
> figured it is worth looking for a solution.
> I will post DDL at the end, but the theory is the main point here, so I ho
pe
> my description will suffice.
> Please forgive the camel case naming convention, and the silly tbl prefixe
s.
> I do not like it either, but it is a standard in our department.
> tblParameter has two FK constraints.
> One references tblReport.strReportID
> One references tblLookupProcedures.strLookupProcedure
> Both tblReport and tblLookupProcedures have a FK reference to
> tblDatabaseConnections.strConnectionName
> Ideally, all of these constraints have ON UPDATE CASCADE, but the second
> constraint on tblParameter will error out with this message:
> ****
> Introducing FOREIGN KEY constraint 'FK_tblParameter_tblLookupProcedures' o
n
> table 'tblParameter' may cause cycles or multiple cascade paths. Specify O
N
> DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
> constraints.
> ****
> I believe the error is caused because updates to the PK in
> tblDatabaseConnections triggers updates to the FK in two tables, each of
> which are referenced by tblParameter. Now the references are not on the
> same columns, but SQL Server seems to think this could lead to circular
> references.
> The questions are:
> 1. Is this against RD theory, or is it just a quirk of SQL Server?
> 2. Is there a setting that will allow this?
> 3. What is the usual workaround? A trigger is the only thing that I could
> think of.
>
[snip]|||DB2 had this problem in a very early version. If you had cascade
chains like A-->B, B-->C and A-->C, the final values in C would be
whoever got there last to overwrite the value. Today DB2 has a pretty
good cycle detector and allows some things that it did not before.
The relational rule is that all possible cascade paths must leave the
DB in the same state when they finish and that state has to be validate
under all the constraints.
This is hard to implement in theory -- remember graph theory? So real
products give up at some point, using a combination of a graph and
constraints. SQL Server happens to quit very early :)|||Thanks for the response. I don't have a 2005 environment to test in, so I
am not sure if the issue is the same or not.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:446A3C9E.BE700DFF@.toomuchspamalready.nl...
> Jim,
> I have had discussions about this (in one of these newsgroups) when it
> was introduced in SQL Server 2000. SQL Server really takes a very
> simplistic view. It acts as if each table only has one column when it
> determines if there is a potential circular reference. This is really
> silly if you ask me, because it means there is hardly any real life
> application that makes it worth your while to use it.
> AFAIK there is not setting around this. You must avoid this supposed
> circular reference. One way of doing that is to define the 'offending'
> foreign key constraint as not cascading. But obviously that does not
> achieve the desired cascading.
> Of course triggers are always an options, but than means disabling the
> foreign key constraint. So it is always a compromise...
> By the way: has any of this changed in SQL Server 2005?
> Gert-Jan
>
> Jim Underwood wrote:
temporary
hope
prefixes.
second
on
ON
could
> [snip]|||Thanks. At least I know that my logical model is ok, I just need to change
the implementation a bit.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147828111.648849.30010@.u72g2000cwu.googlegroups.com...
> DB2 had this problem in a very early version. If you had cascade
> chains like A-->B, B-->C and A-->C, the final values in C would be
> whoever got there last to overwrite the value. Today DB2 has a pretty
> good cycle detector and allows some things that it did not before.
> The relational rule is that all possible cascade paths must leave the
> DB in the same state when they finish and that state has to be validate
> under all the constraints.
> This is hard to implement in theory -- remember graph theory? So real
> products give up at some point, using a combination of a graph and
> constraints. SQL Server happens to quit very early :)
>

Multiple cascade paths

Hello,

I started using SQL Server 2005 and encountered a bug/missing feature.

The cascade behavior on updates and deletions is made in such a way 2 different fields in a given table cannot be accessed through 2 different cascade paths. This limitation is "explained", in fact, in the documentation where the engine enforces the reachable-on-update/delete tables to fit in a tree. A directed acyclic graph would have been appreciated. Or, if you are bound to using a tree, why not replacing table nodes by field ones.

That limitation raises implementation-dependent concerns up to the design phase. Whereas MS Access 2003 allows such behavior... couldn't you collaborate with the Access development team so you did not have to redo the job?

Finally, browsing other threads, I came across another similar limitation which is as restrictive as the previous one, according to me. It's impossible to self-reference a table... and Access supports this feature too.
I thought you were ready after 5 years, but I see very few enhancements in compliance with the SQL standard, not to say any. Besides that, SQL Server 2005 remains a nice tool to use.

Yours,
Intenion

The limitation is still there in June 2007 CTP of SQL Server 2008!!!!

Seems like a bad joke since Oracle and DB2 and probably all other databases has supported this for year (even decades?).

Makes all these nice features like "Delete Cascade" and "Delete Set Null" worthless in most real world relation databases.

Really hope they will fix this to the final SQL Server 2008...

/Andreas

|||

(The cascade behavior on updates and deletions is made in such a way 2 different fields in a given table cannot be accessed through 2 different cascade paths. This limitation is "explained", in fact, in the documentation where the engine enforces the reachable-on-update/delete tables to fit in a tree. A directed acyclic graph would have been appreciated. Or, if you are bound to using a tree, why not replacing table nodes by field ones.

It is not a bug but the correct implementation of ANSI SQL DRI(declarative referential integrity) rules which says if a references b b must exist. What that means primary key a becomes foreign key b you can use a to delete b Cascade delete, you can also use a to update b cascade update and a can set b to default SET Default and null SET NULL.

What is implemented in Access is not relational, I have not used DB2 for a while now but Oracle DRI is not different from SQL Server. Microsoft did not say you cannot have more than one but for that you need a trigger, a trigger can be used to delete and update as many as you want.

BTW that feature came to us from one of the most fabulous Algebras of the 20th century all twenty six pages of it.

Multiple cascade paths

Hello,

I started using SQL Server 2005 and encountered a bug/missing feature.

Yours,
Intenion

The limitation is still there in June 2007 CTP of SQL Server 2008!!!!

Seems like a bad joke since Oracle and DB2 and probably all other databases has supported this for year (even decades?).

Makes all these nice features like "Delete Cascade" and "Delete Set Null" worthless in most real world relation databases.

Really hope they will fix this to the final SQL Server 2008...

/Andreas

|||

BTW that feature came to us from one of the most fabulous Algebras of the 20th century all twenty six pages of it.

Multiple Cascade Path restriction

When using RI to do cascade deletes, why is there a restriction based on
multiple cascade paths ?
Yes I know how to get round the problem, but our application works on the
other major DBs so we're not going to change it just to work on one, so
we're forced to use triggers just one this one DB.. Can we log an
enhancement request to remove this ridiculous restriction ?
Thanks
AdrianIt's a matter of implementation. Before SQL Server 2000 cascading RI weren't
supported at all.
There are scenarios where multiple cascade paths make sense, of course. I
guess that you will see it in the product at some point in the future
(probably with the limit on cyclic relationships).
And yes, you can send your request to sqlwish@.microsoft.com. More requests
they get, better are the chances you will see it in the product at some
point.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Adrian Parker" <apparker@.nospam.nospam> wrote in message
news:uhp88p1PFHA.3076@.TK2MSFTNGP12.phx.gbl...
> When using RI to do cascade deletes, why is there a restriction based on
> multiple cascade paths ?
> Yes I know how to get round the problem, but our application works on the
> other major DBs so we're not going to change it just to work on one, so
> we're forced to use triggers just one this one DB.. Can we log an
> enhancement request to remove this ridiculous restriction ?
> Thanks
> Adrian
>
>|||Thanks for the reply.. request duly sent :)
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:O6z9fx1PFHA.1096@.TK2MSFTNGP12.phx.gbl...
> It's a matter of implementation. Before SQL Server 2000 cascading RI
> weren't supported at all.
> There are scenarios where multiple cascade paths make sense, of course. I
> guess that you will see it in the product at some point in the future
> (probably with the limit on cyclic relationships).
> And yes, you can send your request to sqlwish@.microsoft.com. More requests
> they get, better are the chances you will see it in the product at some
> point.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Adrian Parker" <apparker@.nospam.nospam> wrote in message
> news:uhp88p1PFHA.3076@.TK2MSFTNGP12.phx.gbl...
>

multiple cascade

Hello,
I need to do a relation between 2 tables with this request (under SQL server
2005)
ALTER TABLE "dbo"."PrmBox"
ADD CONSTRAINT "FK_OrgSet_PrmBox" FOREIGN KEY ("n_oset")
REFERENCES "dbo"."OrgSet" ("n_oset") on update cascade on delete cascade
But I have the error : ... may cause cycles or multiple cascade paths.
SQL sever thinks there is a problem, but really there is no problem.
1) Is it possible to disabled this check ?
2) if no, I want to do this relation with a trigger, for the delete there is
no problem, but for the update I don't know how to do the relation between
the deleted and inserted tables to find the old data and the new data in the
table OrgSet.
ThanksAlan
CREATE TABLE GF --GRANDFATHERS
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO GF VALUES (1,'A')
INSERT INTO GF VALUES (2,'B')
INSERT INTO GF VALUES (3,'C')
CREATE TABLE F --FATHERS
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES GF([ID])ON DELETE NO ACTION ON
UPDATE NO ACTION,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO F VALUES (1,1,'AA')
INSERT INTO F VALUES (2,1,'AA')
INSERT INTO F VALUES (3,2,'BB')
INSERT INTO F VALUES (4,2,'BB')
INSERT INTO F VALUES (5,2,'BB')
INSERT INTO F VALUES (6,3,'CC')
DELETE FROM GF WHERE [ID]=1--Cause the error to be thrown
ALTER TABLE F NOCHECK CONSTRAINT FK__F__GFID__54968AE5
--SP_HELPCONSTRAINT 'F'
DELETE FROM GF WHERE [ID]=1-- No error
SELECT * FROM F
DROP TABLE F
DROP TABLE GF
"Alan" <Alan@.discussions.microsoft.com> wrote in message
news:85A0DB79-CACA-4050-B80A-66015BC87D93@.microsoft.com...
> Hello,
> I need to do a relation between 2 tables with this request (under SQL
> server
> 2005)
> ALTER TABLE "dbo"."PrmBox"
> ADD CONSTRAINT "FK_OrgSet_PrmBox" FOREIGN KEY ("n_oset")
> REFERENCES "dbo"."OrgSet" ("n_oset") on update cascade on delete cascade
> But I have the error : ... may cause cycles or multiple cascade paths.
> SQL sever thinks there is a problem, but really there is no problem.
> 1) Is it possible to disabled this check ?
> 2) if no, I want to do this relation with a trigger, for the delete there
> is
> no problem, but for the update I don't know how to do the relation between
> the deleted and inserted tables to find the old data and the new data in
> the
> table OrgSet.
>
> Thanks|||There is a problem with the cascade, I need to change the value of the link
in the linked table (Father in your example).
"Uri Dimant" wrote:

> Alan
> CREATE TABLE GF --GRANDFATHERS
> (
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
> )
> INSERT INTO GF VALUES (1,'A')
> INSERT INTO GF VALUES (2,'B')
> INSERT INTO GF VALUES (3,'C')
> CREATE TABLE F --FATHERS
> (
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES GF([ID])ON DELETE NO ACTION ON
> UPDATE NO ACTION,
> [NAME]CHAR(2) NOT NULL
> )
>
> INSERT INTO F VALUES (1,1,'AA')
> INSERT INTO F VALUES (2,1,'AA')
> INSERT INTO F VALUES (3,2,'BB')
> INSERT INTO F VALUES (4,2,'BB')
> INSERT INTO F VALUES (5,2,'BB')
> INSERT INTO F VALUES (6,3,'CC')
> DELETE FROM GF WHERE [ID]=1--Cause the error to be thrown
> ALTER TABLE F NOCHECK CONSTRAINT FK__F__GFID__54968AE5
> --SP_HELPCONSTRAINT 'F'
> DELETE FROM GF WHERE [ID]=1-- No error
> SELECT * FROM F
> DROP TABLE F
> DROP TABLE GF
>
>
>
> "Alan" <Alan@.discussions.microsoft.com> wrote in message
> news:85A0DB79-CACA-4050-B80A-66015BC87D93@.microsoft.com...
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||On Thu, 2 Feb 2006 02:04:26 -0800, Alan wrote:

>Hello,
>I need to do a relation between 2 tables with this request (under SQL serve
r
>2005)
>ALTER TABLE "dbo"."PrmBox"
> ADD CONSTRAINT "FK_OrgSet_PrmBox" FOREIGN KEY ("n_oset")
> REFERENCES "dbo"."OrgSet" ("n_oset") on update cascade on delete cascade
>But I have the error : ... may cause cycles or multiple cascade paths.
>SQL sever thinks there is a problem, but really there is no problem.
>1) Is it possible to disabled this check ?
Hi Alan,
No. I think that this limitation is a result of some limitation in the
internal mechanism used to perform cascading operations.

>2) if no, I want to do this relation with a trigger,
Make sure to use an INSTEAD OF trigger. Since constraints are checked
*bofore* executing a trigger, an AFTER trigger would never even be fired
on a DELETE or UPDATE that would violate the constraint.

>for the delete there is
>no problem, but for the update I don't know how to do the relation between
>the deleted and inserted tables to find the old data and the new data in th
e
>table OrgSet.
You can't - unless you have some other column (or combinations of
columns) that is certain to be unique and not to change. (An IDENTITY
column with a UNIQUE constraint would be great).
In an UPDATE trigger, there is no way to distinguish these two UPDATE
statements, unless other columns in the rows can be used.
UPDATE MyTable SET KeyColumn = 3 - KeyColumn WHERE KeyColumn IN (1, 2)
UPDATE MyTable SET KeyColumn = KeyColumn WHERE KeyColumn IN (1, 2)
In both cases, both the inserted and the deleted table will hold two
rows, with KeyColumn equal to 1 in one of the rows and 2 in the other
one.
If you can't correlate the rows by other columns, you'll have to limit
update operations on the primary key to single-row operations - start
the trigger with
IF UPDATE(KeyColumn)
AND ROWCOUNT > 1
BEGIN
RAISERROR (...)
ROLLBACK TRAN
RETURN
END
Hugo Kornelis, SQL Server MVP

multiple calls to SP

Hi group,

I've got a performance issue.
Here's in global what the sp (Let's call it SP_A) does.
Step 1 Call a different SP (Lets call it SP_B) and store the output in a variable
Step 2 SP_B runs a select statement that returns 1 value
Step 3 SP_A uses this value as a parameter in a select statement.
Step 4 The result of the SP_A is the result of the select statement (744 rows (always))

All tables used in SP_A and SP_B are temp tables.
Total performance of SP_A is between 0.090 and 0.140 seconds.

The problem is that this SP is called 180 times from outside SQL server. That means that the total processing time is somewhere between 21 and 25 seconds.

When I move the entire processing to within SQL server I gain only 2 seconds. So I lose 2 seconds in connecting to the database 180 times.

Can someone give me some pointers on where to look for performance wins?

If you like I can add the SP's

Regards,

Sander

Call it one time instead of 180 times =;o)

But seriously, it does indeed look like a 'looping' symptom.
Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right.

Have you considered to - if possible - do fewer calls? Ideal would probably be just one instead of 180. It's a bit hard to come up with something tangible without knowing more. Why is it 180 calls? Are they all parts of something that is complete once 180 is done?

/Kenneth

|||"Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right."

Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.

I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.

Do you know what possibilities I've got for tuning the 180 calls?

|||If you could provide some details about what exactly is your SP doing, what are you calculating in general, and maybe the SP code and the caller code too, that would be nice-we could be more specific.|||Here is the source code, btw: SP_A and SP_B cannot be combined (technicly they can of course....)

This is SP_A (uspRetrieveHourlyFactor)
ALTER PROCEDURE uspRetrieveHourlyFactor
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.Normalise bit
AS
SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.RC as numeric(25,20)
DECLARE @.CurrDate as datetime
DECLARE @.WeightedAverage AS numeric(25,20)

SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType

IF @.Normalise = 0
BEGIN
--No normalisation required!
SET @.WeightedAverage = 1
END
ELSE
BEGIN
EXEC @.RC = uspCalcWeightedAverage @.StartDate2, @.EndDate2, @.InMarket, @.InProductType, 1, @.WeightedAverage OUTPUT
END

SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)

SET DATEFIRST 1

CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)

SET @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)

BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END
SELECT
DBI.DATE [DATE],
[PDF].[HOUR] [HOUR],
FLAG [FLAG],
ISNULL((HHF.Factor * flag) / @.WeightedAverage,0.0) [FACTOR]
FROM ##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] - 1
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(dw, DATEPART(D,[DBI].[DATE])) = [HHF].[DayID]
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,[DBI].[DATE]) = [HHF].[Month]
WHERE PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND
(([PDF].[WD-WE] = 1 AND DATEPART(dw, [DBI].[DATE] ) <= 5) OR
([PDF].[WD-WE] = 0 AND DATEPART(dw, [DBI].[DATE] ) > 5)
)
AND HHF.MARKETID = @.InMarketID
ORDER BY DBI.DATE
DROP TABLE #DatesBetweenInterval

This is SP_B (uspCalcWeightedAverage)
ALTER PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage numeric(25,20) OUTPUT
AS

SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.CurrDate as datetime
DECLARE @.helpfloat as numeric(25,20)

--Get ID's for selected parameters
SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType

SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)

SET DATEFIRST 1
--Create temp table
CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)
Set @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)

BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END

SELECT @.WeightedAverage = (SUM(HHF.FACTOR) / COUNT(PDF.FLAG))
FROM
##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND --[PDF].[WD-WE] = @.InWeekDay
(([PDF].[WD-WE] = 1 AND DATEPART(dw, DBI.DATE ) <= 5) or
([PDF].[WD-WE] = 0 AND DATEPART(dw, DBI.DATE ) > 5)
)
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG
DROP TABLE #DatesBetweenInterval

|||

SDerix wrote:

Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.
I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.
Do you know what possibilities I've got for tuning the 180 calls?

Hmmmm... I'm still not convinced that you have to do 180 calls, even though I don't doubt your word on it =;o)

On the other hand, it looks more or less like the overall is grouped by year and month, so it may be doable all at once anyway.. At least in theory. Depending on the datavolume, hardware may restrain the performance if resources aren't available for the 'full' set.

It seems like the proc itself isn't really a problem, since 0.14 sec exec time seems quite acceptable? Though, 180 * 0.14 = 25.2 seconds... And that's the problem.

Would it be possible to rethink the current 'single-month-at-a-time' strategy into something that involves the entire range all at once?

Perhaps you could consider replacing the temporary date-hour table that gets created and thrown away 360 times each run, for a permanent table to join against instead?

spA ends with an order by - is that necessary?
(it would only serve it's ordering purpose if the result is sent to the client, or inserted into a table with some other ordering attribute)

In any case, I believe that the best tuning would be to lower the number of calls from 180 to some lower number, but that would probably involve some rethinking/redesigning of what these procs does....

So... why just a single month each call for a 15 year period? Would it be possible to produce the same result for all 12 months within a year? Or for all months and years in just a single call?

/Kenneth

|||

I think you can do without this temp table #DatesBetweenInterval and use a between clause for the input start and end date.

Did you try creating indexes on the global temp tables?

Multiple Calls to ServerReport.SetParameters() With Varying Numbers Of Parameters

I'm new to programming with the ReportViewer object and this issue has me stumped: it appears if you have some optional parameters in your report, and a way to refresh that report with different parameter values, the report "remembers" parameter values from previous calls to SetParameters() on subsequent renderings of the report. If a parameter is included in a call to ServerReport.SetParameters() on the first rendering, but not included in a subsequent call and the report is re-rendered, the previous value of the parameter (rather than the default value) appears to be used.

Here's a snippet of some test code I wrote within an ASP.NET 2.0 test application:

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

this.rptViewer.ServerReport.ReportServerUrl = new Uri(this.txtReportServerUrl.Text);

this.rptViewer.ServerReport.ReportPath = this.txtReportPath.Text;

}

protected void btnViewRpt_Click(object sender, EventArgs e)

{

ReportParameter[] rptParams = GetReportParameters();

this.rptViewer.ServerReport.SetParameters(rptParams);

this.rptViewer.ServerReport.Refresh();

}

private ReportParameter[] GetReportParameters()

{

int paramCount = 0;

ReportParameter[] retVal;

string emptyVal = null;

if (txtName.Text != "") paramCount++;

if (txtAddress.Text != "") paramCount++;

if (txtZip.Text != "") paramCount++;

retVal = new ReportParameter[paramCount];

paramCount = 0;

if (txtName.Text != "")

retVal[paramCount++] = new ReportParameter("Name", txtName.Text);

if (txtAddress.Text != "")

retVal[paramCount++] = new ReportParameter("Address", txtAddress.Text);

if (txtZip.Text != "")

retVal[paramCount++] = new ReportParameter("Zip", txtZip.Text);

return retVal;

}

The test report was written to simply echo back the values of the parameters that are specified. The report definition allows NULL to be specified for the parameters.

The test app was written so if I enter a blank value for Name, Address or Zip, the corresponding parameter does not get created in C# and does not get sent to the report server. If I view the report with all three values (parameters) filled in, I see the parameters echoed back to me in my simple report as expected. If I clear the parameter values the first time the report is rendered, none are sent to the report server and I get no values echoed back in my report, also as expected. I can change the values and click on the View Report button and see the new values for the parameters as expected. However, if I clear any previously-specified parameters and click on View Report, the previously-specified values for the ones that are now cleared are still displayed by the report.

So my question is: once a parameter has been sent to the report, how does one "unsend" it on subsequent refreshes? I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.

Any suggestions?

I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.

Not meaning to be argumentative... just trying to figure out your situation...

What is the situation in which sending an explicit null "can cause errors"?

IAC, if you want to "render as though for the first time" (wasn't this a Madonna song <g>?) , you should probably use ReportViewer.Reset. You may need to re-specify the URL and path *after* that, though.

>L<

|||

Thanks so much for your reply! That's exactly what I was looking for. After applying ReportViewer.Reset() in our code, I got the behavior I was looking for.

Now, to address your other question (and to perhaps move away from "just get it working" toward "do it the right way"): I have a report that accepts a DateTime parameter (called ReportDate, surprisingly enough):

The parameter allows NULL values. But it also gets its set of available values from a query written for that purpose. It's also set to get the default value from the same query that provides the available values.|||

Well, that's just cr*ppy, if true. Can you share exactly the line you are using to pass the null, just in case this is supposed to work but you need to use a slightly different syntax?

>L<

|||

The code is a bit involved because we're scraping parameter values from screen controls that are automatically generated. However, I think you're on to something. I was just looking at how our parameters collection is constructed:

Code Snippet

for(int rowCount = 1; rowCount < parameterTable.Rows.Count; rowCount++)

{

TableRow tr = parameterTable.Rows[rowCount];

Control parameter = tr.Cells[1].Controls[0];

string parameterName = ((WebControl)parameter).Attributes["ParameterName"];

string parameterValue = "";

if (parameter isParameterDatePicker)

parameterValue = ((ParameterDatePicker)parameter).Text == string.Empty ? null : ((ParameterDatePicker)parameter).Text;

elseif (parameter isParameterDropDownList)

parameterValue = ((ParameterDropDownList)parameter).Value == string.Empty ? null : ((ParameterDropDownList)parameter).Value;

elseif (parameter isParameterTextBox)

parameterValue = ((ParameterTextBox)parameter).Text == string.Empty ? null : ((ParameterTextBox)parameter).Text;

else

thrownewException("Unable to determine parameter type.");

// Create the report parameter

ReportParameter reportParameter = newReportParameter();

reportParameter.Name = parameterName;

reportParameter.Values.Add(parameterValue);

reportParameters.Add(reportParameter);

}

Notice how every parameter is created with a string-type value, regardless of which type of value it represents. I'm wondering if, when actual values are specified, the code under the covers is able to successfully convert the strings into the DateTime type the report is expecting, but in the case of NULLs, the conversion/casting fails? I wonder if casting the nulls as specific data types would work?

greg

|||

Scratch that previous thought about data types. I realize now that the value(s) assigned to a ReportParameter are in fact arrays of strings. I discovered in a separate posting by Lisa that the way to create a ReportParameter with a NULL value is to simply create the parameter with the name only and not assign a value at all, like so:

Code Snippet

ReportParameter rptParam = new ReportParameter("ParamName");

Code Snippet

ReportParameter rptParam = new ReportParameter();

rptParam.Name = "ParamName";

But after some testing, it appears to me now that there's no difference between creating a ReportParameter object with no value assigned and simply not creating the ReportParameter object at all when you're putting together the collection you'll pass to the SetParameters() method.

So what I've determined works best here when viewing a report multiple times with optional parameters that may or may not be specified from one viewing to the next, is to simply not create any ReportParameters that don't have values specified and to use the ReportViewer.Reset() method between report renderings, as Lisa suggested, and I get the result I want. Optional parameters assume their default values as assigned in the report, and the ReportViewer object is induced to "forget" any previously-specified ReportParameter values in the current rendering of the report. If there's any downside to that I haven't discovered it yet. :-)

|||

Thank you for taking the time to check this out. I really thought this would be a better thing to do than Reset, if it worked. Why?

Simply because it goes against the inner grain, in terms of efficiency, to totally re-initialize if you don't have to. There are degrees of initialization in most processes that can be re-run, and one would like to use the right one <sigh>. Of course it has to *work* to be the right one. <double sigh>. There may be no downside, as you suggest, to doing extra initialization, but in some cases there is and you don't discover it until much, much later... Oh well...

Thanks again for checking this out,

>L<

Saturday, February 25, 2012

Multiple Column Indexed Select?

Multiple Column in two pages

multiple column histograms in Yukon

Multiple column full text search

Multiple Collations in SQL Server 2000

Multiple Collations in SQL Server 2000

Multiple Collations in SQL Server 2000

Multiple col ina single col

Multiple Clusters with shared passive nodes

Multiple Client on the same ReportServer

Multiple Client Database Model

multiple charts based on employees in report

multiple charts

Multiple Chart styles

Multiple character separation

Multiple Cascade Paths Error

Multiple Cascade Paths - Fails on SQL Server 2000

Multiple cascade paths

Multiple cascade paths

Multiple Cascade Path restriction

multiple cascade

multiple calls to SP

Multiple Calls to ServerReport.SetParameters() With Varying Numbers Of Parameters

Multi-page report

Blog Archive

About Me