MultiTable Dataset

First off let me say that some of this is very confusing as it would
appear that the MS SQL group and the ADO.NET group need to get together
to mesh their termonology a bit.
I have a program that uses ReportViewer. To this program I load up an
ADO.NET dataset that contains serveral tables. I also load in an RDLC
report file and display the resulting report.
Up until now this has worked great and I have gotten every report I
need worked out. To keep this simple lets go with something we all
know all so well. My ADO.NET dataset contains three tables:
Customers, Orders, Parts. Each one of them is related to the other.
How does one go about displaying a report that would appear as follows:
Customer 1
-Order 1
--Part 1
--Part 2
--Part 3
-Order 2
--Part 4
--Part 5
Customer 2
-Order3
etc...
I have tried numerous things and read a ton of different posts, but all
to no avail. I know I must be doing something wrong when it comes to
setting this up. There do not seem to be many examples with multiple
tables in a single ADO dataset. Unless I am just missing something.
Also, as an aside, why did MS decide to call each table in a Report a
dataset while a dataset in ADO.NET contains many different tables?
Any help would be greatly appreciated.
Thanks,
AdamYou have to made a Select woth Joins of Customer, Order, Part
So for each part you have one record with the data Customer and Order within.
Then you can group this records in the report and so on.
So you will have only one Resultset for this kind of Report.
OK, The naming-conventions in ReportViewer differs a little bit.
Maybe they will sit together for the next release ;-)
--
LG HOLAN
"akeroo" wrote:
> First off let me say that some of this is very confusing as it would
> appear that the MS SQL group and the ADO.NET group need to get together
> to mesh their termonology a bit.
> I have a program that uses ReportViewer. To this program I load up an
> ADO.NET dataset that contains serveral tables. I also load in an RDLC
> report file and display the resulting report.
> Up until now this has worked great and I have gotten every report I
> need worked out. To keep this simple lets go with something we all
> know all so well. My ADO.NET dataset contains three tables:
> Customers, Orders, Parts. Each one of them is related to the other.
> How does one go about displaying a report that would appear as follows:
> Customer 1
> -Order 1
> --Part 1
> --Part 2
> --Part 3
> -Order 2
> --Part 4
> --Part 5
> Customer 2
> -Order3
> etc...
> I have tried numerous things and read a ton of different posts, but all
> to no avail. I know I must be doing something wrong when it comes to
> setting this up. There do not seem to be many examples with multiple
> tables in a single ADO dataset. Unless I am just missing something.
> Also, as an aside, why did MS decide to call each table in a Report a
> dataset while a dataset in ADO.NET contains many different tables?
> Any help would be greatly appreciated.
> Thanks,
> Adam
>|||Although you can do it, this gets quite messy. The clean way (but it is more
difficult when operating in local mode with the viewer control) is to use
subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"holan" <holan@.noemaol.noemail> wrote in message
news:FA17AF8F-0A21-4A92-BDB9-45A194502517@.microsoft.com...
> You have to made a Select woth Joins of Customer, Order, Part
> So for each part you have one record with the data Customer and Order
> within.
> Then you can group this records in the report and so on.
> So you will have only one Resultset for this kind of Report.
> OK, The naming-conventions in ReportViewer differs a little bit.
> Maybe they will sit together for the next release ;-)
> --
> LG HOLAN
>
> "akeroo" wrote:
>> First off let me say that some of this is very confusing as it would
>> appear that the MS SQL group and the ADO.NET group need to get together
>> to mesh their termonology a bit.
>> I have a program that uses ReportViewer. To this program I load up an
>> ADO.NET dataset that contains serveral tables. I also load in an RDLC
>> report file and display the resulting report.
>> Up until now this has worked great and I have gotten every report I
>> need worked out. To keep this simple lets go with something we all
>> know all so well. My ADO.NET dataset contains three tables:
>> Customers, Orders, Parts. Each one of them is related to the other.
>> How does one go about displaying a report that would appear as follows:
>> Customer 1
>> -Order 1
>> --Part 1
>> --Part 2
>> --Part 3
>> -Order 2
>> --Part 4
>> --Part 5
>> Customer 2
>> -Order3
>> etc...
>> I have tried numerous things and read a ton of different posts, but all
>> to no avail. I know I must be doing something wrong when it comes to
>> setting this up. There do not seem to be many examples with multiple
>> tables in a single ADO dataset. Unless I am just missing something.
>> Also, as an aside, why did MS decide to call each table in a Report a
>> dataset while a dataset in ADO.NET contains many different tables?
>> Any help would be greatly appreciated.
>> Thanks,
>> Adam
>>|||Thanks... I ended up going with subreports and it is working fine.
The joins were not quite going to work for me as my exact situation
wasn't an A to B to C relationship. It was an A-B A-C setup, which
means the joins would have ended up being A-B * C number of records and
that would have gotten really ugly.
thanks,
Adam

multiple values per table cell?

I am converting a site from asp to .net and the previus writer did something I never saw.(I am somewhat new to programming all together). He put multiple values in a single cell of the database. I have always learned to use a seperate column for each value.
Is this a classic asp thing and there are better ways now? Or is this something I should do myself? The columns type is text and the contents look like this:

<Details Expertise="bla bla" Description="We are a three-divisional company ...," WebSite="www.blabla.com" AccountLevel="Free" AccountStatus="Active" WorkHomeZIP="22222" ContractStartDate="01/01/2003" ContractEndDate="12/31/2003"><Address Location="Work" State="Oh" ZIP="22222"/><Email Location="Work" Value=""/><Phone Location="Fax" Code="9801" Ext=""/></Details>

Putting aside the question of the style being used for a moment, the contents of the column is not *really* multiple values, but a single XML string. This allows it to be easily read into a XmlDocument object and parsed.

As for the reason behind this approach, the only one I can thing of would be to store dynamic categories of information that is not known at design time. While none come to mind immediately, I'm sure that there might be times when this approach is quite valid. It is *not*, however, a classic ASP thing, but a database design decision. As to whether you should change the approach, I would if the attributes in the XML string were always the same. Better to use a database to do what databases were intended to do ;)

HTH

Multiple Stored Procedure Execute Together - How?

Hi,
I have multiple stored procedures (SP) running in multiple databases. The
output of all the databases has same column names, same number of columns an
d
column types.
I want to run all these different SP's as one SP and combine the output as
one result.
I tried creating one new SP as SPAll and calling all the SP's in SPAll.
eg.
Create SPAll @.Parameter int
AS
Exec SP1 @.Parameter
Exec SP2 @.Parameter
GO
But when I execute SPAll from 'sql server reporting services' (vs.net), it
executes and displays results from SP1 also. I want to display results from
both SP1 and SP2.
Thanks in advance.Hello GJ.
execute the stored procedures saving the result sets to a temporary table
and then select the results from the temporary table to give you a single
result set
IE:
use Northwind
GO
set nocount on
if object_id('tempdb..#results') is not null drop table #results
create table #results(
index_name sysname,/* Index name. */
index_description varchar(210),/* Index description. */
index_keys nvarchar(2078)/* Table or view column(s) upon which the index is
built. */
)
insert #results(index_name,index_description,in
dex_keys)
exec sp_helpindex @.objname = 'dbo.Categories'
insert #results(index_name,index_description,in
dex_keys)
exec sp_helpindex @.objname = 'dbo.Customers'
select * from #results
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"GJ" wrote:

> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results fro
m
> both SP1 and SP2.
> Thanks in advance.|||Best way, based on what you are telling us:
Create SPAll @.Parameter int
AS
create table #spAllReturn
(
<columns that match procs>
)
insert into #spAllReturn
Exec SP1 @.Parameter
insert into #spAllReturn
Exec SP2 @.Parameter
select * from #spAllReturn
GO
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:AA68D163-4681-4794-B4F0-748E72C18760@.microsoft.com...
> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
> and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results
> from
> both SP1 and SP2.
> Thanks in advance.|||GJ
create table #test
(
col ...
....
....
)
insert into #test exec sp1
insert into #test exec sp2
select * from #test
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:AA68D163-4681-4794-B4F0-748E72C18760@.microsoft.com...
> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results
from
> both SP1 and SP2.
> Thanks in advance.