DataSet Question (Should be easy :)

This should be simple. If I have a DataSet... lets call it ImageDataSet in a report and it is a collection of ImageObjects that have two properties, lets say Photo and Caption.

So if i were writing in c# to access the second image in the collection, I would say something like ImageObjectsCollection[1].Photo to access the second photo in the collection.

My question is how can I do that within the reportviewer. Just access a specific image.

Everything I have read only shows =Fields!Photo.Value in which it would get all of them

or =First(Fields!Photos.Value) which would get the first in the collection and =Last(Fields!Photos.Value) which would access the last one in the collection. So I ask how could I specificaly access an image in between. Thanks

anyone?|||wow nothing?|||I am sorry I cannot help, but my situation is similar so I am hoping you get an answer to this. I was actually hoping to be able to pass a dataset to the Report's Custom Code, but I do not think this can be done.|||

You can't randomly access rows in a datasource via RS. There isn't really a straightforward way to accomplish this, although I can imagine really convoluted means such as having a list and setting the visibility property based on the caption.

Really the way to accomplish this is to modify your query to return only the rows you want. What are you trying to accomplish that you need to randomly access returned rows?

|||

hopefully I am not misunderstanding this, but if each photo/caption is a row, could you create a dataset that gets the distinct photo names/captions, then use a multiselect parameter (bound to the distinct dataset) and return the photos based on the users selections? This would get ugly if you have a lot of photos as the multiselect parameter dropdown could get pretty large.

the other option is a textbox parameter where the user can enter a portion of the caption and you return the results via a dynamic query:

="select photos, caption from image_table where caption like %'" & Parameters!Caption.Value & "'%"

that way, when a user enters "dogs", the query returns all photos with the word dog in the caption.

by the way, the above is air code and may need tweaking...

DataSet Question (Should be easy :)

This should be simple. If I have a DataSet... lets call it ImageDataSet in a report and it is a collection of ImageObjects that have two properties, lets say Photo and Caption.

So if i were writing in c# to access the second image in the collection, I would say something like ImageObjectsCollection[1].Photo to access the second photo in the collection.

My question is how can I do that within the reportviewer. Just access a specific image.

Everything I have read only shows =Fields!Photo.Value in which it would get all of them

or =First(Fields!Photos.Value) which would get the first in the collection and =Last(Fields!Photos.Value) which would access the last one in the collection. So I ask how could I specificaly access an image in between. Thanks

anyone?|||wow nothing?|||I am sorry I cannot help, but my situation is similar so I am hoping you get an answer to this. I was actually hoping to be able to pass a dataset to the Report's Custom Code, but I do not think this can be done.|||

You can't randomly access rows in a datasource via RS. There isn't really a straightforward way to accomplish this, although I can imagine really convoluted means such as having a list and setting the visibility property based on the caption.

Really the way to accomplish this is to modify your query to return only the rows you want. What are you trying to accomplish that you need to randomly access returned rows?

|||

hopefully I am not misunderstanding this, but if each photo/caption is a row, could you create a dataset that gets the distinct photo names/captions, then use a multiselect parameter (bound to the distinct dataset) and return the photos based on the users selections? This would get ugly if you have a lot of photos as the multiselect parameter dropdown could get pretty large.

the other option is a textbox parameter where the user can enter a portion of the caption and you return the results via a dynamic query:

="select photos, caption from image_table where caption like %'" & Parameters!Caption.Value & "'%"

that way, when a user enters "dogs", the query returns all photos with the word dog in the caption.

by the way, the above is air code and may need tweaking...

dataset question

Using SS2000 SP4, VS2003 and RS2000. I copied a report, call it report1 and
created a new report, call it report2. When I first looked at the data tab in
VS, there was a dataset there from the original report1, call it dataset1.
Dataset1 used a different stored procedure. I created a new dataset, call it
dataset2 for the new report2.
I added the fields that I needed and put them on the report in place of the
original fields from report1. When I run the sp from the data tab all of the
correct data shows up. However, when I try to preview the report2 I get an
error message that says "The value expression for the query parameter
'@.ISRID' refers to a non-existing report parameter 'ISRID'. From the data
tab, with dataset2 showing, I click on the "..." to bring up the dataset
dialog box. I go to the "parameters" tab but '@.ISRID' isn't there. In the
dataset dropdown box, I can set the original dataset1. I'm wondering if when
I try to preview the report, it gets '@.ISRID' from the dataset1 dataset. If
so, can I safely delete dataset1? If I delete dataset1 is it deleted only for
report2 or will it also be deleted from report1 where it's still needed?
Thanks,
--
Dan D.Dan,
Here's how the parameters work. There are query parameters and report
parameters. Report parameters are what the user actually interacts with
during report execution. Most of the time, there will be a 1-to-1
relationship between the 2, but you might find a need to create a
report parameter that is not linked to a query parameter. If you wanted
to use a parameter for a report rendering option (like font color or
something), you could create a report parameter and the query would not
even know that parameter exists.
In order for the report parameter to pass its value to the query
parameter, they have to be "linked". The parameters tab when you click
'...' next to your dataset name is where you link them. On the left,
you will see the parameters used in the query, and on the right, you
assign report parameters to them.
To modify the report parameters, go to the Design view of your report,
click the Report menu, and click Report Parameters. Your query
parameter is trying to reference a report parameter that doesn't exist,
so double-check and make sure that everything is set up the way that it
should be. If there are no report parameters, create one called ISRID
and assign it the correct data type.
Hope this helps!
-Josh
Dan D. wrote:
> Using SS2000 SP4, VS2003 and RS2000. I copied a report, call it report1 and
> created a new report, call it report2. When I first looked at the data tab in
> VS, there was a dataset there from the original report1, call it dataset1.
> Dataset1 used a different stored procedure. I created a new dataset, call it
> dataset2 for the new report2.
> I added the fields that I needed and put them on the report in place of the
> original fields from report1. When I run the sp from the data tab all of the
> correct data shows up. However, when I try to preview the report2 I get an
> error message that says "The value expression for the query parameter
> '@.ISRID' refers to a non-existing report parameter 'ISRID'. From the data
> tab, with dataset2 showing, I click on the "..." to bring up the dataset
> dialog box. I go to the "parameters" tab but '@.ISRID' isn't there. In the
> dataset dropdown box, I can set the original dataset1. I'm wondering if when
> I try to preview the report, it gets '@.ISRID' from the dataset1 dataset. If
> so, can I safely delete dataset1? If I delete dataset1 is it deleted only for
> report2 or will it also be deleted from report1 where it's still needed?
> Thanks,
> --
> Dan D.|||Yes, that explanation does help. I figured out that I could delete the first
dataset without affecting the first report. I still have errors referring to
objects that were on the first report. I'm going to remove the existing
sections of the report and add them back and see if that solves the problem.
Thanks for the explanation Josh,
--
Dan D.
"Josh" wrote:
> Dan,
> Here's how the parameters work. There are query parameters and report
> parameters. Report parameters are what the user actually interacts with
> during report execution. Most of the time, there will be a 1-to-1
> relationship between the 2, but you might find a need to create a
> report parameter that is not linked to a query parameter. If you wanted
> to use a parameter for a report rendering option (like font color or
> something), you could create a report parameter and the query would not
> even know that parameter exists.
> In order for the report parameter to pass its value to the query
> parameter, they have to be "linked". The parameters tab when you click
> '...' next to your dataset name is where you link them. On the left,
> you will see the parameters used in the query, and on the right, you
> assign report parameters to them.
> To modify the report parameters, go to the Design view of your report,
> click the Report menu, and click Report Parameters. Your query
> parameter is trying to reference a report parameter that doesn't exist,
> so double-check and make sure that everything is set up the way that it
> should be. If there are no report parameters, create one called ISRID
> and assign it the correct data type.
> Hope this helps!
> -Josh
>
> Dan D. wrote:
> > Using SS2000 SP4, VS2003 and RS2000. I copied a report, call it report1 and
> > created a new report, call it report2. When I first looked at the data tab in
> > VS, there was a dataset there from the original report1, call it dataset1.
> > Dataset1 used a different stored procedure. I created a new dataset, call it
> > dataset2 for the new report2.
> >
> > I added the fields that I needed and put them on the report in place of the
> > original fields from report1. When I run the sp from the data tab all of the
> > correct data shows up. However, when I try to preview the report2 I get an
> > error message that says "The value expression for the query parameter
> > '@.ISRID' refers to a non-existing report parameter 'ISRID'. From the data
> > tab, with dataset2 showing, I click on the "..." to bring up the dataset
> > dialog box. I go to the "parameters" tab but '@.ISRID' isn't there. In the
> > dataset dropdown box, I can set the original dataset1. I'm wondering if when
> > I try to preview the report, it gets '@.ISRID' from the dataset1 dataset. If
> > so, can I safely delete dataset1? If I delete dataset1 is it deleted only for
> > report2 or will it also be deleted from report1 where it's still needed?
> >
> > Thanks,
> > --
> > Dan D.
>|||I can't get rid of the detail and group sections that refer to the original
report that I copied from. When I try to add a new section the default name
is "tbActivity" which is the name of the table in the original report. Any
idea how I can get rid of the reference to the original report so I can add
info from the new dataset and new report?
Thanks,
--
Dan D.
"Josh" wrote:
> Dan,
> Here's how the parameters work. There are query parameters and report
> parameters. Report parameters are what the user actually interacts with
> during report execution. Most of the time, there will be a 1-to-1
> relationship between the 2, but you might find a need to create a
> report parameter that is not linked to a query parameter. If you wanted
> to use a parameter for a report rendering option (like font color or
> something), you could create a report parameter and the query would not
> even know that parameter exists.
> In order for the report parameter to pass its value to the query
> parameter, they have to be "linked". The parameters tab when you click
> '...' next to your dataset name is where you link them. On the left,
> you will see the parameters used in the query, and on the right, you
> assign report parameters to them.
> To modify the report parameters, go to the Design view of your report,
> click the Report menu, and click Report Parameters. Your query
> parameter is trying to reference a report parameter that doesn't exist,
> so double-check and make sure that everything is set up the way that it
> should be. If there are no report parameters, create one called ISRID
> and assign it the correct data type.
> Hope this helps!
> -Josh
>
> Dan D. wrote:
> > Using SS2000 SP4, VS2003 and RS2000. I copied a report, call it report1 and
> > created a new report, call it report2. When I first looked at the data tab in
> > VS, there was a dataset there from the original report1, call it dataset1.
> > Dataset1 used a different stored procedure. I created a new dataset, call it
> > dataset2 for the new report2.
> >
> > I added the fields that I needed and put them on the report in place of the
> > original fields from report1. When I run the sp from the data tab all of the
> > correct data shows up. However, when I try to preview the report2 I get an
> > error message that says "The value expression for the query parameter
> > '@.ISRID' refers to a non-existing report parameter 'ISRID'. From the data
> > tab, with dataset2 showing, I click on the "..." to bring up the dataset
> > dialog box. I go to the "parameters" tab but '@.ISRID' isn't there. In the
> > dataset dropdown box, I can set the original dataset1. I'm wondering if when
> > I try to preview the report, it gets '@.ISRID' from the dataset1 dataset. If
> > so, can I safely delete dataset1? If I delete dataset1 is it deleted only for
> > report2 or will it also be deleted from report1 where it's still needed?
> >
> > Thanks,
> > --
> > Dan D.
>|||I was able to find where the reference was to the orginal report and replace
it.
--
Dan D.
"Josh" wrote:
> Dan,
> Here's how the parameters work. There are query parameters and report
> parameters. Report parameters are what the user actually interacts with
> during report execution. Most of the time, there will be a 1-to-1
> relationship between the 2, but you might find a need to create a
> report parameter that is not linked to a query parameter. If you wanted
> to use a parameter for a report rendering option (like font color or
> something), you could create a report parameter and the query would not
> even know that parameter exists.
> In order for the report parameter to pass its value to the query
> parameter, they have to be "linked". The parameters tab when you click
> '...' next to your dataset name is where you link them. On the left,
> you will see the parameters used in the query, and on the right, you
> assign report parameters to them.
> To modify the report parameters, go to the Design view of your report,
> click the Report menu, and click Report Parameters. Your query
> parameter is trying to reference a report parameter that doesn't exist,
> so double-check and make sure that everything is set up the way that it
> should be. If there are no report parameters, create one called ISRID
> and assign it the correct data type.
> Hope this helps!
> -Josh
>
> Dan D. wrote:
> > Using SS2000 SP4, VS2003 and RS2000. I copied a report, call it report1 and
> > created a new report, call it report2. When I first looked at the data tab in
> > VS, there was a dataset there from the original report1, call it dataset1.
> > Dataset1 used a different stored procedure. I created a new dataset, call it
> > dataset2 for the new report2.
> >
> > I added the fields that I needed and put them on the report in place of the
> > original fields from report1. When I run the sp from the data tab all of the
> > correct data shows up. However, when I try to preview the report2 I get an
> > error message that says "The value expression for the query parameter
> > '@.ISRID' refers to a non-existing report parameter 'ISRID'. From the data
> > tab, with dataset2 showing, I click on the "..." to bring up the dataset
> > dialog box. I go to the "parameters" tab but '@.ISRID' isn't there. In the
> > dataset dropdown box, I can set the original dataset1. I'm wondering if when
> > I try to preview the report, it gets '@.ISRID' from the dataset1 dataset. If
> > so, can I safely delete dataset1? If I delete dataset1 is it deleted only for
> > report2 or will it also be deleted from report1 where it's still needed?
> >
> > Thanks,
> > --
> > Dan D.
>|||It's been awhile but I have in the past edited the xml (the RDL file is just
XML). Be careful, you can easily destroy the report. Make a copy first. You
can edit the XML directly in VS. Layout mode, Menu View-> Code.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:856B3981-9335-42A4-BFDE-E287A29F767E@.microsoft.com...
>I can't get rid of the detail and group sections that refer to the original
> report that I copied from. When I try to add a new section the default
> name
> is "tbActivity" which is the name of the table in the original report. Any
> idea how I can get rid of the reference to the original report so I can
> add
> info from the new dataset and new report?
> Thanks,
> --
> Dan D.
>
> "Josh" wrote:
>> Dan,
>> Here's how the parameters work. There are query parameters and report
>> parameters. Report parameters are what the user actually interacts with
>> during report execution. Most of the time, there will be a 1-to-1
>> relationship between the 2, but you might find a need to create a
>> report parameter that is not linked to a query parameter. If you wanted
>> to use a parameter for a report rendering option (like font color or
>> something), you could create a report parameter and the query would not
>> even know that parameter exists.
>> In order for the report parameter to pass its value to the query
>> parameter, they have to be "linked". The parameters tab when you click
>> '...' next to your dataset name is where you link them. On the left,
>> you will see the parameters used in the query, and on the right, you
>> assign report parameters to them.
>> To modify the report parameters, go to the Design view of your report,
>> click the Report menu, and click Report Parameters. Your query
>> parameter is trying to reference a report parameter that doesn't exist,
>> so double-check and make sure that everything is set up the way that it
>> should be. If there are no report parameters, create one called ISRID
>> and assign it the correct data type.
>> Hope this helps!
>> -Josh
>>
>> Dan D. wrote:
>> > Using SS2000 SP4, VS2003 and RS2000. I copied a report, call it report1
>> > and
>> > created a new report, call it report2. When I first looked at the data
>> > tab in
>> > VS, there was a dataset there from the original report1, call it
>> > dataset1.
>> > Dataset1 used a different stored procedure. I created a new dataset,
>> > call it
>> > dataset2 for the new report2.
>> >
>> > I added the fields that I needed and put them on the report in place of
>> > the
>> > original fields from report1. When I run the sp from the data tab all
>> > of the
>> > correct data shows up. However, when I try to preview the report2 I get
>> > an
>> > error message that says "The value expression for the query parameter
>> > '@.ISRID' refers to a non-existing report parameter 'ISRID'. From the
>> > data
>> > tab, with dataset2 showing, I click on the "..." to bring up the
>> > dataset
>> > dialog box. I go to the "parameters" tab but '@.ISRID' isn't there. In
>> > the
>> > dataset dropdown box, I can set the original dataset1. I'm wondering if
>> > when
>> > I try to preview the report, it gets '@.ISRID' from the dataset1
>> > dataset. If
>> > so, can I safely delete dataset1? If I delete dataset1 is it deleted
>> > only for
>> > report2 or will it also be deleted from report1 where it's still
>> > needed?
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>|||Dan,
It sounds like you are already close to where you want to be, but I'll
mention a few things.
1. If the reports are so different, just start over. If it is a simple
report, the New Report Wizard does a pretty good job.
2. I was going to suggest modifying the XML as well. (Definitely be
careful if you do this; make a backup copy before you do anything.) If
the reports are VERY similar, you can use the Find and Replace feature
to swap out field names in the XML.
3. Another option would be to give the query output fields aliases. If
you look at the query properties by clicking the '...', you can modify
the aliases in the Fields tab. This would basically rename the new
fields so that they fit right into the old report's table. Does this
make sense? If a particular table column in your report was looking for
"Fields!customer.Value", you could give the "employee" field in your
query an alias of "customer" so that you don't have to change the
table. I don't know that I would recommend this option because it is
"messy" and might be hard for the next developer to maintain, but it is
a quick fix.
4. You probably know this, but I'll mention it just in case. The names
of the report items (like tables, text boxes, group names, etc.) do not
matter. Your group section that refers to "tbActivity" is probably
fine. What you have to worry about is the value, expression, or bound
data item behind each object. That might lower the number of items that
you have to change.
-Josh
Bruce L-C [MVP] wrote:
> It's been awhile but I have in the past edited the xml (the RDL file is just
> XML). Be careful, you can easily destroy the report. Make a copy first. You
> can edit the XML directly in VS. Layout mode, Menu View-> Code.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:856B3981-9335-42A4-BFDE-E287A29F767E@.microsoft.com...
> >I can't get rid of the detail and group sections that refer to the original
> > report that I copied from. When I try to add a new section the default
> > name
> > is "tbActivity" which is the name of the table in the original report. Any
> > idea how I can get rid of the reference to the original report so I can
> > add
> > info from the new dataset and new report?
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "Josh" wrote:
> >
> >>
> >> Dan,
> >>
> >> Here's how the parameters work. There are query parameters and report
> >> parameters. Report parameters are what the user actually interacts with
> >> during report execution. Most of the time, there will be a 1-to-1
> >> relationship between the 2, but you might find a need to create a
> >> report parameter that is not linked to a query parameter. If you wanted
> >> to use a parameter for a report rendering option (like font color or
> >> something), you could create a report parameter and the query would not
> >> even know that parameter exists.
> >>
> >> In order for the report parameter to pass its value to the query
> >> parameter, they have to be "linked". The parameters tab when you click
> >> '...' next to your dataset name is where you link them. On the left,
> >> you will see the parameters used in the query, and on the right, you
> >> assign report parameters to them.
> >>
> >> To modify the report parameters, go to the Design view of your report,
> >> click the Report menu, and click Report Parameters. Your query
> >> parameter is trying to reference a report parameter that doesn't exist,
> >> so double-check and make sure that everything is set up the way that it
> >> should be. If there are no report parameters, create one called ISRID
> >> and assign it the correct data type.
> >>
> >> Hope this helps!
> >>
> >> -Josh
> >>
> >>
> >> Dan D. wrote:
> >> > Using SS2000 SP4, VS2003 and RS2000. I copied a report, call it report1
> >> > and
> >> > created a new report, call it report2. When I first looked at the data
> >> > tab in
> >> > VS, there was a dataset there from the original report1, call it
> >> > dataset1.
> >> > Dataset1 used a different stored procedure. I created a new dataset,
> >> > call it
> >> > dataset2 for the new report2.
> >> >
> >> > I added the fields that I needed and put them on the report in place of
> >> > the
> >> > original fields from report1. When I run the sp from the data tab all
> >> > of the
> >> > correct data shows up. However, when I try to preview the report2 I get
> >> > an
> >> > error message that says "The value expression for the query parameter
> >> > '@.ISRID' refers to a non-existing report parameter 'ISRID'. From the
> >> > data
> >> > tab, with dataset2 showing, I click on the "..." to bring up the
> >> > dataset
> >> > dialog box. I go to the "parameters" tab but '@.ISRID' isn't there. In
> >> > the
> >> > dataset dropdown box, I can set the original dataset1. I'm wondering if
> >> > when
> >> > I try to preview the report, it gets '@.ISRID' from the dataset1
> >> > dataset. If
> >> > so, can I safely delete dataset1? If I delete dataset1 is it deleted
> >> > only for
> >> > report2 or will it also be deleted from report1 where it's still
> >> > needed?
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >>
> >>|||Thanks Bruce. I'll remember that for next time. I have it working now.
--
Dan D.
"Bruce L-C [MVP]" wrote:
> It's been awhile but I have in the past edited the xml (the RDL file is just
> XML). Be careful, you can easily destroy the report. Make a copy first. You
> can edit the XML directly in VS. Layout mode, Menu View-> Code.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:856B3981-9335-42A4-BFDE-E287A29F767E@.microsoft.com...
> >I can't get rid of the detail and group sections that refer to the original
> > report that I copied from. When I try to add a new section the default
> > name
> > is "tbActivity" which is the name of the table in the original report. Any
> > idea how I can get rid of the reference to the original report so I can
> > add
> > info from the new dataset and new report?
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "Josh" wrote:
> >
> >>
> >> Dan,
> >>
> >> Here's how the parameters work. There are query parameters and report
> >> parameters. Report parameters are what the user actually interacts with
> >> during report execution. Most of the time, there will be a 1-to-1
> >> relationship between the 2, but you might find a need to create a
> >> report parameter that is not linked to a query parameter. If you wanted
> >> to use a parameter for a report rendering option (like font color or
> >> something), you could create a report parameter and the query would not
> >> even know that parameter exists.
> >>
> >> In order for the report parameter to pass its value to the query
> >> parameter, they have to be "linked". The parameters tab when you click
> >> '...' next to your dataset name is where you link them. On the left,
> >> you will see the parameters used in the query, and on the right, you
> >> assign report parameters to them.
> >>
> >> To modify the report parameters, go to the Design view of your report,
> >> click the Report menu, and click Report Parameters. Your query
> >> parameter is trying to reference a report parameter that doesn't exist,
> >> so double-check and make sure that everything is set up the way that it
> >> should be. If there are no report parameters, create one called ISRID
> >> and assign it the correct data type.
> >>
> >> Hope this helps!
> >>
> >> -Josh
> >>
> >>
> >> Dan D. wrote:
> >> > Using SS2000 SP4, VS2003 and RS2000. I copied a report, call it report1
> >> > and
> >> > created a new report, call it report2. When I first looked at the data
> >> > tab in
> >> > VS, there was a dataset there from the original report1, call it
> >> > dataset1.
> >> > Dataset1 used a different stored procedure. I created a new dataset,
> >> > call it
> >> > dataset2 for the new report2.
> >> >
> >> > I added the fields that I needed and put them on the report in place of
> >> > the
> >> > original fields from report1. When I run the sp from the data tab all
> >> > of the
> >> > correct data shows up. However, when I try to preview the report2 I get
> >> > an
> >> > error message that says "The value expression for the query parameter
> >> > '@.ISRID' refers to a non-existing report parameter 'ISRID'. From the
> >> > data
> >> > tab, with dataset2 showing, I click on the "..." to bring up the
> >> > dataset
> >> > dialog box. I go to the "parameters" tab but '@.ISRID' isn't there. In
> >> > the
> >> > dataset dropdown box, I can set the original dataset1. I'm wondering if
> >> > when
> >> > I try to preview the report, it gets '@.ISRID' from the dataset1
> >> > dataset. If
> >> > so, can I safely delete dataset1? If I delete dataset1 is it deleted
> >> > only for
> >> > report2 or will it also be deleted from report1 where it's still
> >> > needed?
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >>
> >>
>
>|||I'm over that hurdle now. And thanks for the additional information.
I'm having trouble now doing some counts. If you have a chance I have
another thread called "counting with a filter" in this same forum. I'm trying
to count a couple of different things but not having much luck. I've tried
counting in the table footer and in a group but I can't get it like I need
it. Is it possible to use a filter in a count expression (i.e.
Count(Fields!Item.Value) where Fields!Item.Value = 'FecEx')?
Thanks again for your help,
--
Dan D.
"Josh" wrote:
> Dan,
> It sounds like you are already close to where you want to be, but I'll
> mention a few things.
> 1. If the reports are so different, just start over. If it is a simple
> report, the New Report Wizard does a pretty good job.
> 2. I was going to suggest modifying the XML as well. (Definitely be
> careful if you do this; make a backup copy before you do anything.) If
> the reports are VERY similar, you can use the Find and Replace feature
> to swap out field names in the XML.
> 3. Another option would be to give the query output fields aliases. If
> you look at the query properties by clicking the '...', you can modify
> the aliases in the Fields tab. This would basically rename the new
> fields so that they fit right into the old report's table. Does this
> make sense? If a particular table column in your report was looking for
> "Fields!customer.Value", you could give the "employee" field in your
> query an alias of "customer" so that you don't have to change the
> table. I don't know that I would recommend this option because it is
> "messy" and might be hard for the next developer to maintain, but it is
> a quick fix.
> 4. You probably know this, but I'll mention it just in case. The names
> of the report items (like tables, text boxes, group names, etc.) do not
> matter. Your group section that refers to "tbActivity" is probably
> fine. What you have to worry about is the value, expression, or bound
> data item behind each object. That might lower the number of items that
> you have to change.
> -Josh
>
> Bruce L-C [MVP] wrote:
> > It's been awhile but I have in the past edited the xml (the RDL file is just
> > XML). Be careful, you can easily destroy the report. Make a copy first. You
> > can edit the XML directly in VS. Layout mode, Menu View-> Code.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:856B3981-9335-42A4-BFDE-E287A29F767E@.microsoft.com...
> > >I can't get rid of the detail and group sections that refer to the original
> > > report that I copied from. When I try to add a new section the default
> > > name
> > > is "tbActivity" which is the name of the table in the original report. Any
> > > idea how I can get rid of the reference to the original report so I can
> > > add
> > > info from the new dataset and new report?
> > >
> > > Thanks,
> > > --
> > > Dan D.
> > >
> > >
> > > "Josh" wrote:
> > >
> > >>
> > >> Dan,
> > >>
> > >> Here's how the parameters work. There are query parameters and report
> > >> parameters. Report parameters are what the user actually interacts with
> > >> during report execution. Most of the time, there will be a 1-to-1
> > >> relationship between the 2, but you might find a need to create a
> > >> report parameter that is not linked to a query parameter. If you wanted
> > >> to use a parameter for a report rendering option (like font color or
> > >> something), you could create a report parameter and the query would not
> > >> even know that parameter exists.
> > >>
> > >> In order for the report parameter to pass its value to the query
> > >> parameter, they have to be "linked". The parameters tab when you click
> > >> '...' next to your dataset name is where you link them. On the left,
> > >> you will see the parameters used in the query, and on the right, you
> > >> assign report parameters to them.
> > >>
> > >> To modify the report parameters, go to the Design view of your report,
> > >> click the Report menu, and click Report Parameters. Your query
> > >> parameter is trying to reference a report parameter that doesn't exist,
> > >> so double-check and make sure that everything is set up the way that it
> > >> should be. If there are no report parameters, create one called ISRID
> > >> and assign it the correct data type.
> > >>
> > >> Hope this helps!
> > >>
> > >> -Josh
> > >>
> > >>
> > >> Dan D. wrote:
> > >> > Using SS2000 SP4, VS2003 and RS2000. I copied a report, call it report1
> > >> > and
> > >> > created a new report, call it report2. When I first looked at the data
> > >> > tab in
> > >> > VS, there was a dataset there from the original report1, call it
> > >> > dataset1.
> > >> > Dataset1 used a different stored procedure. I created a new dataset,
> > >> > call it
> > >> > dataset2 for the new report2.
> > >> >
> > >> > I added the fields that I needed and put them on the report in place of
> > >> > the
> > >> > original fields from report1. When I run the sp from the data tab all
> > >> > of the
> > >> > correct data shows up. However, when I try to preview the report2 I get
> > >> > an
> > >> > error message that says "The value expression for the query parameter
> > >> > '@.ISRID' refers to a non-existing report parameter 'ISRID'. From the
> > >> > data
> > >> > tab, with dataset2 showing, I click on the "..." to bring up the
> > >> > dataset
> > >> > dialog box. I go to the "parameters" tab but '@.ISRID' isn't there. In
> > >> > the
> > >> > dataset dropdown box, I can set the original dataset1. I'm wondering if
> > >> > when
> > >> > I try to preview the report, it gets '@.ISRID' from the dataset1
> > >> > dataset. If
> > >> > so, can I safely delete dataset1? If I delete dataset1 is it deleted
> > >> > only for
> > >> > report2 or will it also be deleted from report1 where it's still
> > >> > needed?
> > >> >
> > >> > Thanks,
> > >> > --
> > >> > Dan D.
> > >>
> > >>
>

DataSet Performance

Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>sql

DataSet Performance

Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
--
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
--
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>

DataSet Performance

Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
Use a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
David Portas
SQL Server MVP
|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegr oups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>
|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
David Portas
SQL Server MVP
|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas

"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
|||You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor
|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas

"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>

DataSet Performance

Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>

Dataset Confusion in Reporting Services 2005

I'm creating a report that gets its data from a dataset returned from a
webservice call. I've supplied the URL for the webservice and in my Dataset
dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
Query String as follows:
<Query>
<SoapAction>Federation/GetAll</SoapAction>
<ElementPath />
</Query>
Where: 'Federation' is my namespace and 'GetAll' is the method which gets
consumed from the webservice.
When I click the Run button on the report Design screen, the webservice is
called correctly but what I get back is one row for each field in my dataset
instead of one row for each record in the dataset. IOW, my dataset has 20
fields in it and what I get returned is a table with 20 records in it, one
for each field, instead of the actual data.
Can someone tell me what I'm doing wrong?
Amos."Amos Soma" wrote:
> I'm creating a report that gets its data from a dataset returned from a
> webservice call. I've supplied the URL for the webservice and in my Dataset
> dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> Query String as follows:
> <Query>
> <SoapAction>Federation/GetAll</SoapAction>
> <ElementPath />
> </Query>
> Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> consumed from the webservice.
> When I click the Run button on the report Design screen, the webservice is
> called correctly but what I get back is one row for each field in my dataset
> instead of one row for each record in the dataset. IOW, my dataset has 20
> fields in it and what I get returned is a table with 20 records in it, one
> for each field, instead of the actual data.
> Can someone tell me what I'm doing wrong?
> Amos.
I am having the same problem. Any answers yet? I am messing around with the
Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
XML & Web Services the going is slow.|||Same problem!....please help!
Tito Perez wrote:
> "Amos Soma" wrote:
> > I'm creating a report that gets its data from a dataset returned from a
> > webservice call. I've supplied the URL for the webservice and in my Dataset
> > dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> > Query String as follows:
> >
> > <Query>
> > <SoapAction>Federation/GetAll</SoapAction>
> > <ElementPath />
> > </Query>
> >
> > Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> > consumed from the webservice.
> >
> > When I click the Run button on the report Design screen, the webservice is
> > called correctly but what I get back is one row for each field in my dataset
> > instead of one row for each record in the dataset. IOW, my dataset has 20
> > fields in it and what I get returned is a table with 20 records in it, one
> > for each field, instead of the actual data.
> >
> > Can someone tell me what I'm doing wrong?
> >
> > Amos.
> I am having the same problem. Any answers yet? I am messing around with the
> Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
> XML & Web Services the going is slow.|||Can someone please help with this?|||Try this... I was having the same problem but found another example. Here is
what I did and it worked for me - Notice the changes to the "ElementPath".
<Query>
<SoapAction>Federation/GetAll</SoapAction>
<ElementPath
IgnoreNamespaces="True">GetAllResponse/GetAllResult/diffgram/NewDataSet/Table</ElementPath>
</Query>
Note: If you only want certain columns returned, add your column name(s)
seperated by commas in brackets "{} " after "Table" (eg. <ElementPath
IgnoreNamespaces="True">GetAllResponse/GetAllResult/diffgram/NewDataSet/Table{column1_name,column2_name}</ElementPath>)
Hope this helps!
"Kays" wrote:
> Same problem!....please help!
> Tito Perez wrote:
> > "Amos Soma" wrote:
> > > I'm creating a report that gets its data from a dataset returned from a
> > > webservice call. I've supplied the URL for the webservice and in my Dataset
> > > dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> > > Query String as follows:
> > >
> > > <Query>
> > > <SoapAction>Federation/GetAll</SoapAction>
> > > <ElementPath />
> > > </Query>
> > >
> > > Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> > > consumed from the webservice.
> > >
> > > When I click the Run button on the report Design screen, the webservice is
> > > called correctly but what I get back is one row for each field in my dataset
> > > instead of one row for each record in the dataset. IOW, my dataset has 20
> > > fields in it and what I get returned is a table with 20 records in it, one
> > > for each field, instead of the actual data.
> > >
> > > Can someone tell me what I'm doing wrong?
> > >
> > > Amos.
> >
> > I am having the same problem. Any answers yet? I am messing around with the
> > Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
> > XML & Web Services the going is slow.
>

Dataflow error in lookup task : "Object was open.".

I Can't reproduce the error if I run the package stand-alone.

I'm using the same lookup call (same table, etc.) in 2 packages that are running in parallel (called by a parent package).

[LKP_UnderwriterId [72283]] Error: An OLE DB error has occurred. Error code: 0x80040E05. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E05 Description: "Object was open.".

Anyone seen this one?

Googling threw this up: http://www.sharepointu.com/forums/m_23343/tm.htm

Could it be that something isn't installed properly on the machine on which you are running the package stand-alone (N.B. Can you clarify what you mean by "stand-alone"?)

-Jamie

|||If the package is run serially (so no other packages are running using a lookup against the same table) or run in the debugger, then no error occurs.