Datasource error

Hi, i've a problem with calling a report created with reporting service; i get this error :System.Web.Services.Protocols.SoapException : Impossible find datasource...... ( i translate from italian ) I'm sure ip i wrote is right , and , moreover, any ip datasource i put ,i get the same error. Thank you in advance for your help.

Hi,

From your description, it seems that you are using WebService as your datasource of your report, right?

And the error "System.Web.Services.Protocols.SoapException" indicates that the WebService failed to return the result which your report expected.

So I suggest that you should try to check your WebService to see if it can work correctly, we suppose that it returns a dataset typed datasource, then you can just write a simple project, remove the reporting service part, try to see if you can retrieve the return properly.

Thanks.

|||Hi Nai-Dong,
I use the webservice as result, distribuiting report I've developed with SQL Server Development intelligence studio.
I'm doing this for the first time , I'm studying how to use SQLServer reporting service.
The webservice "seems" to me that works properly because I see exposed all the methods, and moreover ,
calling the other methods that don't involve datasource , the report is correctly called.
I'm using XP .
Thank you.|||

Hi,

The webservice "seems" to me that works properly because I see exposed all the methods, and moreover , calling the other methods that don't involve datasource , the report is correctly called.

Well, if so. I think you should try to check if the database is available from the machine which host the web service. Another problem is the account. You should try to check if the account which runs the webservice exist in the web service identity of the report server. Also, try to check if the account which runs the webservice (ASPNET or IUSR_MachineName) has to permission to access the reporting service database. If not, add the account into the users of that database.

Besides, I suggest you to refer the following link, it may be helpful to you.

http://msdn2.microsoft.com/en-us/library/aa179578(SQL.80).aspx

Thanks.

DataSet Update Problem

I know this is very very silly.
But I am stuck and don't know what to do.

I have a simple form which has 1 textbox and a 'Save' button.

I have created a Database connection and an Adapter with Dataset with
the help of Wizards.

I have binded by text box control to one of the database field.

When the form gets loaded it displays the field information correctly.
"Me.SqlDataAdapter1.Fill(DsBasicData1)"

However, when I press the save button it does not update the database
behind it.
I have given the following code to the click event of save button.
"Me.SqlDataAdapter1.Update(DsBasicData1)"

Strangely I don't know why the code does not work.patels (patels@.india.com) writes:
> I know this is very very silly.
> But I am stuck and don't know what to do.
> I have a simple form which has 1 textbox and a 'Save' button.
> I have created a Database connection and an Adapter with Dataset with
> the help of Wizards.
> I have binded by text box control to one of the database field.
> When the form gets loaded it displays the field information correctly.
> "Me.SqlDataAdapter1.Fill(DsBasicData1)"
> However, when I press the save button it does not update the database
> behind it.
> I have given the following code to the click event of save button.
> "Me.SqlDataAdapter1.Update(DsBasicData1)"
>
> Strangely I don't know why the code does not work.

And we don't know your database, or what's in that Update command.
Anyway, I think you should ask about this in a group devoted to
ADO .Net or Visual Studio .Net, as this is not really an
SQL Server issue per se.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Dataset Results

I have created adataset to make a drop down list for a parameter. As shown:

SELECT distinct [Table] FROM [Database]

The results i get back are A, B & C. What i am trying to do is show a result as a different word.

i.e instead of displaying A is shows in drop down list as Apple, B shows Banana. etc.

Can anyone help please.

Thanks

Lookup the usage of case(sql server). This should get you your desired result.|||The parameter can use two columns of the returned dataset, one for the value column and one for the display column, getting this back from the query and binding it to the appropiate properties in the parameter setting should help.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Ok got it working.

select distinct

case [COLUMN NAME]

when 'A' then 'Apple'

when 'B' then 'Banana'

else 'Unknown'end

from [TABLE NAME]

where [COLUMN NAME] in ('A', 'B', 'C')

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 manipulation questions

I have a dataset that is created by a Stored Procedure that is
cumulative of all values that I need to report against. The problem is
that I need DISTINCT results of certain columns. To do it within the
sproc would take a prohibitively long time and it would be ideal if I
could do it within the table object on the layout tab. I have played
around with the filtering capabilities in the table properties, but
they are very limited. Is there any way I can produce a DISTINCT list
within the results of a dataset?
Thanks in advance for any help or guidance.
...ChrisMy suggestion is to have your result in the stored procedure to be put into
a temp table. Have an extra field to store the distinct results and then
update the field with the distinct values. I guarantee you this will be
faster than anything RS can do (plus I don't know how to do what you want in
RS).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<craniumgroup@.gmail.com> wrote in message
news:1116866722.102195.305620@.g14g2000cwa.googlegroups.com...
>I have a dataset that is created by a Stored Procedure that is
> cumulative of all values that I need to report against. The problem is
> that I need DISTINCT results of certain columns. To do it within the
> sproc would take a prohibitively long time and it would be ideal if I
> could do it within the table object on the layout tab. I have played
> around with the filtering capabilities in the table properties, but
> they are very limited. Is there any way I can produce a DISTINCT list
> within the results of a dataset?
> Thanks in advance for any help or guidance.
> ...Chris
>

dataset linked to stored procedure return no data

I created a new dataset for a new report that gets data from a stored
procedure. But when I run the dataset in the Data tab, it only returns the
column names with no data. The stored procedure runs fine in the Query
Analyzer with several returned records. Any help will be appreciated!On Mar 7, 4:14 pm, obnddc <obn...@.discussions.microsoft.com> wrote:
> I created a new dataset for a new report that gets data from a stored
> procedure. But when I run the dataset in the Data tab, it only returns the
> column names with no data. The stored procedure runs fine in the Query
> Analyzer with several returned records. Any help will be appreciated!
Have you verified that the command type of the dataset is set to
stored procedure (instead of text)?
Enrique Martinez
Sr. SQL Server Developer|||Since it is returning all the columns it means it has accessed stored proc,
Just check the datasource using "test connection" if possible recreate the
datasource, more over do a "Refresh". Check for the server you connected
using Query Analyzer and the datasource are same.
Amarnath
"obnddc" wrote:
> I created a new dataset for a new report that gets data from a stored
> procedure. But when I run the dataset in the Data tab, it only returns the
> column names with no data. The stored procedure runs fine in the Query
> Analyzer with several returned records. Any help will be appreciated!|||Thanks for the input, Emartinez and Amarnath!
I found out that the problem lay in the data itself. I have an input
parameter used in the where condition, like "WHERE tbl_name.customerID LIKE
@.custID"
The test data I used has several spaces after cutomerID charactors. It is
interesting to see the LIKE statement will ignore the spaces in Query
Analyzer but fail in SQL reporting services.
I tried to LTRIM and RTRIM customerID, or use @.custID+'%' but none worked.
Anyone here can help? Thanks.|||Finally I found out, it's not the spaces but the parameters.
I have begin date and end date as input parameters, and end date is
optional. When it is null, getdate(). In Query Analyzer, I left it empty, it
returned records. In SQL report, I have to enter an end date and the date I
entered happens to be the only date that has records. So no record met the
dates, no record reurned.
What I learned:
Before you conclude same process ran differently in different envirements,
make sure they ran under EXACTLY same conditions!
Thanks to all!

Dataset blues

i don't know if this is the right forum for this question but here goes. I have created a gridview to display a list of records retrieved by a SQL query. Everytime I run it, I get the following error:

Object must implement IConvertible.

I thought it might have to do with the data I'm passing in the session string from the previous page. Here is that code:

ProtectedSub cmdSubmit_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles cmdSubmit.Click

Session.Add("Shift", shShift.SelectedItem)

Session.Add("Type", shType.SelectedItem)

Session.Add("SelDate", OccDate.SelectedDate)

Response.Redirect("SelectIncRep.aspx")

Here is the code that is supposed to execute and display the data on page_init:

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataKeyNames="RID"DataSourceID="SqlDataSource1"Width="1215px"BorderStyle="Groove"BorderWidth="4px">

<Columns>

<asp:BoundFieldDataField="RID"HeaderText="Record ID:"InsertVisible="False"ReadOnly="True"

SortExpression="RID"/>

<asp:BoundFieldDataField="Date"HeaderText="Date:"SortExpression="Date"/>

<asp:BoundFieldDataField="Shift_ID"HeaderText="Shift:"SortExpression="Shift_ID"/>

<asp:BoundFieldDataField="Xref_ID"HeaderText="Type Of Occurence:"SortExpression="Xref_ID"/>

<asp:BoundFieldDataField="Notes"HeaderText="Notes:"SortExpression="Notes"/>

</Columns>

</asp:GridView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:PerfDataConnectionString %>"

SelectCommand="SELECT [RID], [Notes], [Date], [Shift_ID], [Xref_ID] FROM [Safety_data] WHERE (([Shift_ID] = @.Shift_ID) AND ([Xref_ID] = @.Xref_ID) AND ([Date] = @.Date))">

<SelectParameters>

<asp:SessionParameterName="Shift_ID"SessionField="Shift_ID"Type="Int32"/>

<asp:SessionParameterName="Xref_ID"SessionField="Type"Type="Int32"/>

<asp:SessionParameterName="Date"SessionField="Date"Type="DateTime"/>

</SelectParameters>

</asp:SqlDataSource>

What could be causing the issue?

BezerkRogue:

Session.Add("Shift", shShift.SelectedItem)
Session.Add("Type", shType.SelectedItem)
Session.Add("SelDate", OccDate.SelectedDate)

.....

<asp:SessionParameterName="Shift_ID"SessionField="Shift_ID"Type="Int32"/>
<asp:SessionParameterName="Xref_ID"SessionField="Type"Type="Int32"/>
<asp:SessionParameterName="Date"SessionField="Date"Type="DateTime"/>

|||

Thanks. I made that correction but still get the same error. I dumped my temporary internet files to make sure I wasn't loading cached data. Could there be anything else I might have missed?

|||

Did you already googled this error?

I found some results that all pointed in the same direction, which seams to be the case here also. You omitted the select type, so maybe this is the solution?

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:PerfDataConnectionString %>"

SelectCommand="SELECT [RID], [Notes], [Date], [Shift_ID], [Xref_ID] FROM [Safety_data] WHERE (([Shift_ID] = @.Shift_ID) AND ([Xref_ID] = @.Xref_ID) AND ([Date] = @.Date))"SelectType="Text">

|||

I added the selectcommandtype line in and still get the same error. The stack trace is chinese to me so I have no idea what the problem is.

Object must implement IConvertible.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: Object must implement IConvertible.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidCastException: Object must implement IConvertible.] System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +2514354 System.Web.UI.WebControls.Parameter.GetValue(Object value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges) +264 System.Web.UI.WebControls.Parameter.get_ParameterValue() +66 System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control) +254 System.Web.UI.WebControls.SqlDataSourceView.InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList) +276 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +754 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

---

Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

|||

Ok, I found the variable that is being passed wrong and corrected it. I have verified that I am getting variables passed and have printed them on screen. I have also verified that I have records available for those variables, now I just get no data. Am I missing a binding somewhere?

<asp:gridviewrunat="server"ID="RecordView"AutoGenerateColumns="False"DataKeyNames="RID"DataSourceID="SqlDataSource1"Width="1134px"GridLines="Both"EnableViewState="true"Visible="true">

<Columns>

<asp:BoundFieldDataField="RID"HeaderText="RID"Visible="true"ReadOnly="True"

SortExpression="RID"/>

<asp:BoundFieldDataField="Notes"HeaderText="Notes"Visible="true"SortExpression="Notes"/>

</Columns>

</asp:gridview>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:PerfDataConnectionString %>"

SelectCommand="SELECT [RID], [Notes] FROM [Safety_data] WHERE (([Date] = @.Date) AND ([Shift_ID] = @.Shift_ID) AND ([Xref_ID] = @.Xref_ID))"SelectCommandType="Text">

<SelectParameters>

<asp:QueryStringParameterName="Date"QueryStringField="strSelDate"Type="DateTime"/>

<asp:QueryStringParameterName="Shift_ID"QueryStringField="strShiftPull"Type="Decimal"/>

<asp:QueryStringParameterName="Xref_ID"QueryStringField="strTypePull"Type="Decimal"/>

</SelectParameters>

</asp:SqlDataSource>

DataSet and Insert method

hi,

i created a query to insert a row in DataSet in Visual Studio 2005. i gave the method name to the query i created. as i understood it returns '1' if successful or '0' if not.

is it possible to get the ID or the row instead?

what did it say,or whats the eror code if there is?|||

there is no error. the point is i would like to get the id of the row that i insert instead of default int value.

|||Hi,

are you using a SQL 2k5? There is a new ouput clause in the syntax, where you can get values back. Look in the BOL for more information about that, or raise a hand if you need further assistance.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

i am using SQLExpress...

sorry should have mentioned earlier.

|||

Hi,

ok then go the OUTPUT way (described in the BOL)

INSERT INTO Sometable (Columnlisthere....)
OUTPUT INSERTED.*
VALUES ...Values here...

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot Jens

Dataset - Command type question

Hi to all
I need to use a set of stored procedures as source of my report datasets, my
problem is that I have several reports already created some of them using
StoredProcedure as command type, and others using Text and using "EXEC
MyStoreProcedure @.Param1,@.Param2" as the Query String , it seems to work in
the same way, change one of the sets of reports can cost some resources of
my team.
My question is if no exist differences or I can have problems with this
second type of datasets?
ThanksFor "SQL Server" connections they are for all practical purposes identical.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:#XmzhXedEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi to all
> I need to use a set of stored procedures as source of my report datasets, my
> problem is that I have several reports already created some of them using
> StoredProcedure as command type, and others using Text and using "EXEC
> MyStoreProcedure @.Param1,@.Param2" as the Query String , it seems to work in
> the same way, change one of the sets of reports can cost some resources of
> my team.
> My question is if no exist differences or I can have problems with this
> second type of datasets?
> Thanks
>sql

Dataset

hey guys,

I created a dataset using a design view of the MDX query designer. As you know, in the filter pane of the MDX query, there is check box if you want to parameterized your report. When I checked that option, SQL Server created a dataset specifically for the report parameter. So my question is that I can see this dataset in the data tab of the report designer but not in the Report Datasets list. Do you have any idea why this happened?

waiting to hear soon.

Sincerely,

Amde

Report designer will mark MDX parameter datasets as "auto-generated" in the underlying RDL representation:
<rd:AutoGenerated>true</rd:AutoGenerated>

Reasoning: in general the parameter valid value lists of MDX queries are only relevant for parameter selection.

You can still use those datasets by removing the the rd:AutoGenerated element from the dataset in the RDL file (and have it then show up in the dataset list), or by looking up the name and fields of the autogenerated dataset and hand-writing expressions.

-- Robert

|||

Hey Robert,

I really appreciate your assistance. I got the solution. By the way removing the rd:AutoGenerated element from the rdl file didn't help me to see the dataset. What I did is changing the hidden property of the dataset to false. Now I can see the dataset in the list.

Thank you Robert again.

Sincerely,

Amde

|||

Yes you right - I forgot to mention to remove the <rd:Hidden> element.

-- Robert

|||Hi Robert,
Is there any tutorial/link for rdlc report creation using mdx query/OLAP database?

Dataset

hey guys,

I created a dataset using a design view of the MDX query designer. As you know, in the filter pane of the MDX query, there is check box if you want to parameterized your report. When I checked that option, SQL Server created a dataset specifically for the report parameter. So my question is that I can see this dataset in the data tab of the report designer but not in the Report Datasets list. Do you have any idea why this happened?

waiting to hear soon.

Sincerely,

Amde

Report designer will mark MDX parameter datasets as "auto-generated" in the underlying RDL representation:
<rd:AutoGenerated>true</rd:AutoGenerated>

Reasoning: in general the parameter valid value lists of MDX queries are only relevant for parameter selection.

You can still use those datasets by removing the the rd:AutoGenerated element from the dataset in the RDL file (and have it then show up in the dataset list), or by looking up the name and fields of the autogenerated dataset and hand-writing expressions.

-- Robert

|||

Hey Robert,

I really appreciate your assistance. I got the solution. By the way removing the rd:AutoGenerated element from the rdl file didn't help me to see the dataset. What I did is changing the hidden property of the dataset to false. Now I can see the dataset in the list.

Thank you Robert again.

Sincerely,

Amde

|||

Yes you right - I forgot to mention to remove the <rd:Hidden> element.

-- Robert

|||Hi Robert,
Is there any tutorial/link for rdlc report creation using mdx query/OLAP database?

DataRow in a CLR Stored Procedure

I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
created a Database project where I've written some simple CLR Stored
Procedures. I can deploy and call the simple CLR Stored Procedures from my
host WinForm application. This all works great.
I'd now like to write a CLR Stored Procedure with a parameter of type
System.Data.DataRow. Doing this compiles just fine, but when I attempt to
deploy, I get the following error:
Cannot find data type DataRow.
Any suggestions on what I might do to get a CLR Stored Procedure with a
DataRow parameter to compile AND deploy?
Thanks,
--
Randyexamnotes <randy1200@.newsgroups.nospam> wrote in
news:4A6B0CA3-733E-4E43-AA94-CD9D98B295C2@.microsoft.com:

> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
>
Can not be done. Your CLR procedures can only have params of types that are
T-SQL compatible.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||randy1200 (randy1200@.newsgroups.nospam) writes:
> I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
> created a Database project where I've written some simple CLR Stored
> Procedures. I can deploy and call the simple CLR Stored Procedures from my
> host WinForm application. This all works great.
> I'd now like to write a CLR Stored Procedure with a parameter of type
> System.Data.DataRow. Doing this compiles just fine, but when I attempt to
> deploy, I get the following error:
> Cannot find data type DataRow.
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
You can't do that. A CLR stored procedure can only take parameters
that maps to types used in SQL Server, and DataRow is not such a type.
Keep in mind that even if the stored procedure is implemented in the CLR,
there is still is a CREATE PROCEDURE statement which looks just like
the CREATE PROCEDURE statement for a T-SQL procedure as far as the
parameter list is concerned.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could serialize the DataRow and use a VarChar or VarBinary parameter.
Dino Esposito covers serializing/deserializing ADO.NET objects in depth in
"Applied XML Programming for Microsoft .NET" See chapter 9 "ADO.NET XML Dat
a
Serialization." You could use an XML representation, however there is a
particularly interesting example of serializing/deserializing a DataTable an
d
the DataRows it contains using an efficient custom binary representation on
pages 424-428. The sample code is in C# and there isn't much code needed.
Hope this helps.
Jack Whitney
"randy1200" wrote:

> I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
> created a Database project where I've written some simple CLR Stored
> Procedures. I can deploy and call the simple CLR Stored Procedures from my
> host WinForm application. This all works great.
> I'd now like to write a CLR Stored Procedure with a parameter of type
> System.Data.DataRow. Doing this compiles just fine, but when I attempt to
> deploy, I get the following error:
> Cannot find data type DataRow.
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
> Thanks,
> --
> Randy|||I have been playing around with Dino Esposito's example of custom binary
serialization of a DataTable and the DataRows it contains. (This is the
example that I mentioned in my earlier posting on this thread.) It is very
nicely implemented. The code to serialize a DataTable to a file and
deserialize the file back to a DataTable, including a worker class definitio
n
and comments is 78 lines of code. Dino provides a sample Windows applicatio
n
that connects to a Northwind database. You enter SQL in a textbox like
"SELECT * FROM [Order Details]" and the application gets the data from the
database into a DataTable, serializes the DataTable to a file, deserializes
the file back to a DataTable, and renders the DataTable in a DataGrid. He
actually serializes the DataTable using two methods and compares the output
for size efficiency. The two methods are ordinary .NET framework binary
serialization and his custom binary serialization. The custom binary
serialization produces much smaller output. The code was written for .NET
1.1, but I just converted it to .NET 2.0 and accessed SQL Server 2005 with n
o
hitches. If you are interested in pursuing this, you should definitely take
a look at this sample.
Note that to serialize just a DataRow, you would need to serialize some
properties of the containing DataTable object, minimally the column names an
d
types, in addition to the values in the DataRow. Dino's example demonstrate
s
this.
Hope this helps.
Jack Whitney
"Jack Whitney" wrote:
> You could serialize the DataRow and use a VarChar or VarBinary parameter.
> Dino Esposito covers serializing/deserializing ADO.NET objects in depth in
> "Applied XML Programming for Microsoft .NET" See chapter 9 "ADO.NET XML D
ata
> Serialization." You could use an XML representation, however there is a
> particularly interesting example of serializing/deserializing a DataTable
and
> the DataRows it contains using an efficient custom binary representation o
n
> pages 424-428. The sample code is in C# and there isn't much code needed.
> --
> Hope this helps.
> Jack Whitney
>
> "randy1200" wrote:
>

datareader doing updates

We created a Role (Alpha) and made it a member of the datareader Role. Our
hope was that members of this role would only be able to preform data reads.
On a test we granted Alpha exec rights to an update Stored Procedure. The
members of Alpha are now able to run the Stored Procedure and do updates eve
n
thought they are NOT members of datawriter. Further testing showed the same
for insert and delete stored procedures.
Does this sound right?
Thanks, Randy"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:0EA38A7F-70DF-44D3-91C0-6555FB42A4FA@.microsoft.com...
> We created a Role (Alpha) and made it a member of the datareader Role.
> Our
> hope was that members of this role would only be able to preform data
> reads.
> On a test we granted Alpha exec rights to an update Stored Procedure. The
> members of Alpha are now able to run the Stored Procedure and do updates
> even
> thought they are NOT members of datawriter. Further testing showed the
> same
> for insert and delete stored procedures.
> Does this sound right?
>
Yes. Look up "ownership chains" in BOL. If the user can run the
procedure, then permission checks on all objects owned by the owner of the
stored procedure are supressed.
David

Datamining Viewer Client Problem

Hello!

I am running a SQL Server 2005. I created the mining models from the tutorials. I can connect with the DataminingViewerClient directly on the SQL Server(i.e. to localhost), but when i try to connect to the Server from my other computer it is not working i always get connection faild and i should check that the server is running. I can connect to the Server with other programms but the viewer is not working. Any ideas what that could be?

Can you connect to other servers from the machine where the DataminingViewerClient application works? If so, it may be that there are additional client components that need to be installed. Let us know and we can continue to diagnose.

Thanks

-Jamie

|||Ok i now tried to connect from Server A to Server B, not working either. It works on both when i run the viewer directly on the server and connect to localhost but not from Server A to B or vice versa or from any other computer. Could it be that some permissions are missing, because in the dataminingviewer client i dont have to specify a user/password for the server where the analysis-service runs. I dont think that components are missing because i can compile the source code without errors, but i'll be happy about any hint that might help.

Another thing i tried: When i create a datasource on Server A to connect to Server B(or from any other computer) it only works when I use the sa user(Windows Authentication doesnt work), otherwise i cant browse/select the databases.|||In general AS only uses Windows Authentication, unless you connect over http (requires configuration - see books online). Connecting over http allows you to specify a username and password. For the DM Viewer Client, you cna modify the code to use a http connection, or prompt for username and password. The source code is provided.|||

Hello again!

I found the error...the server was not in the domain, after we added it it worked.

Datamining a Olap Cube

I'm new to datamining and have gather some knowledge by following the tutorial that comes with sql server 2005.
I have following created a association mining model and tested it by using various DMX queries. I works quite satisfactory.

But I would like to use this mining model on a cube. Ie a calculated measure that would show top 1 recommendation for my customer portfolio.
My knowlede reaches to the extent that you need to feed data by using the OpenQuery or OpenRowset queries.
I would like to "stick" my model to the cube on some way and the extract values in the same manner as filtering criterias in stead of manually generating DMX queries.

I have googled a bit and searched MSDN without luck. Maybe I'm looking at this all wrong.

Is this possible?

Regards Bergur

Could this help?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1640504&SiteID=1

It presents an example of how to create a custom action containing a data mining prediction. Not really a calculated measure, but still a way to associate a prediction result with an cube cell.

Alternately, you could try this more complex approach:

- have your transaction data in the same cube

- build an OLAP mining model based on the cube

- use the MDXPredict MDX function to retrieve predictions from a mining model inside the calculated measure

Datamining a Olap Cube

I'm new to datamining and have gather some knowledge by following the tutorial that comes with sql server 2005.
I have following created a association mining model and tested it by using various DMX queries. I works quite satisfactory.

But I would like to use this mining model on a cube. Ie a calculated measure that would show top 1 recommendation for my customer portfolio.
My knowlede reaches to the extent that you need to feed data by using the OpenQuery or OpenRowset queries.
I would like to "stick" my model to the cube on some way and the extract values in the same manner as filtering criterias in stead of manually generating DMX queries.

I have googled a bit and searched MSDN without luck. Maybe I'm looking at this all wrong.

Is this possible?

Regards Bergur

Could this help?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1640504&SiteID=1

It presents an example of how to create a custom action containing a data mining prediction. Not really a calculated measure, but still a way to associate a prediction result with an cube cell.

Alternately, you could try this more complex approach:

- have your transaction data in the same cube

- build an OLAP mining model based on the cube

- use the MDXPredict MDX function to retrieve predictions from a mining model inside the calculated measure

Dataflow Tab:There is no ODBC Source option in the Toolbox

I need to extract data from tables in a database that I can only access via ODBC.

I have successfully created a connection in Connection Manager (ConnectionManagerType = ODBC) for this database.

However I’m unable to add this connection as a Data Flow Source. There is no ODBC Source option in the Toolbox.

This is a major because we have been using the system dsn Microsoft Visual Foxpro Driver to access free table directory .dbf files under ODBC with DTS for years. To install a new Microsoft OLEDB driver for foxpro is out of the question on a production system as it would cost many thousands of dollars to go through our BAT testing process

How do I extract data from tables in a database via ODBC?

Thanks in advance

Dave

There is too, though it's not marked as such. Use the Data Reader Source.|||

You can use the script component as source.

|||

Thanks for that the data reader souce can actually use a ODBC source. I have it working fine.

I do think there is a peformance overhead and its slower than the ODBC connector in DTS.

|||

well that is interesting How can it connect to a foxpro file .dbf and matching .fpt file the .fpt files are used for memo text fieldsthe foxprpro ODBC driver does this for you behind the scenes

|||

Vijay Thirugnanam wrote:

You can use the script component as source.

True, though it shouldn't be faster than using the prepackaged source connectors.

Data-driven subscription not working

I created a data-driven subscription for my report with "Null Delivery Provider" set in report manager. I set the processing to "On a schedule created for this subscription" and scheduled the report to "run once" in the next 1 minute so I could test it. The subscription added successfully, but I don't see the "last run information" even after 5 minutes. Am I missing something?

Thanks in advance!!!

Check SQL Server for any failures reported in the log.

DataDirection output Problems

I have the below code. When the SqlParameter is created the @.UserID is set as input instead of output. anyone have any ideas why this is happining and how I can get it to work. I can create the variable as a single parameter and set the direction to output and it works but when I try it in the code below it won't work. Any help will be appreciated

Endeavor

Dim parametersAs SqlParameter() = {New SqlParameter("@.strSex", Data.SqlDbType.Char, 50), _New SqlParameter("@.intRace", Data.SqlDbType.Int), _New SqlParameter("@.intAge", Data.SqlDbType.Int), _New SqlParameter("@.UserID", Data.SqlDbType.SmallInt, Data.ParameterDirection.Output)}The overload that takes 3 parameters does not have the direction as the 3rd parameter. If you want to do it that way, you'll need the full overload that takes the 10ish parameters.|||

Thanks for your help. OK I don't need all of the rest of the parameters. Is there any way to make the variable an output and still keep it in the array or add it to the array after creating the sqlparameter.

Endeavor

|||cmd.Parameter("@.Name").ParameterDirection=Output

Databinding question

Hi,

I have a page created within VS 2005 which uses a detailsView with a SQLDataSource which has insert, edit and delete items allowed with it.

The problem is that if I delete a record I dont want to refresh the page as I want to set a label value to say item deleted. The problem then though is to select the item to delete I have a drop down which populates the details view on index change, but if I delete the item I cannot do a databind when its complete because it just binds to the existing dataset and does not do a fresh call on the database.

Is there a command I can run to refresh the dataset on click of the delete button?

Thanks

I think I would do databinding in a sub that does nothing else but the databinding. I normally do this in a sub called something like sub bindcontrols() or something like that. Then when I need to refresh the data I can just call that sub.|||

Thanks for your reply.

The problem is the databinding is handled by VS and I dont think I have a choice of where it runs.

|||try the following after deleting detailsView.databind() it should refresh the content Hope this helps