refresh fields button does not work?

I have a dataset that calls a stored procedure. it's command type is set to
stored procedure.
The stored procedure returns 1 result set from a temp table (like select *
from #temp). In sql query analyzer it only returns 1 grid so I know it's not
returning multiple result sets.
when I hit the ! button I fill out my parameters and run the stored
procedure. It returns all the columns with some data. good. but the only
field I see is one called ID of type database field.
I hit the refresh fields button and nothing changes. The dataset is
returning 1 result set with the columns and data. WhyOWhy are the fields not
being filled out?What backend are you going against (SQL Server, OLEDB, ODBC?).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> I have a dataset that calls a stored procedure. it's command type is set
to
> stored procedure.
> The stored procedure returns 1 result set from a temp table (like select *
> from #temp). In sql query analyzer it only returns 1 grid so I know it's
not
> returning multiple result sets.
> when I hit the ! button I fill out my parameters and run the stored
> procedure. It returns all the columns with some data. good. but the only
> field I see is one called ID of type database field.
> I hit the refresh fields button and nothing changes. The dataset is
> returning 1 result set with the columns and data. WhyOWhy are the fields
not
> being filled out?|||I figured it out!
in my stored procedure I was doing this
create table [#whatever]
select * from #whatever
this runs fine from sql query analyzer but does not return a list of fields
in report designer gui.
but changing my stored procedure to
create table [#whatever]
select * from [#whatever]
fixed the problem. I now get the entire list of fields in the gui.
I'm not sure if I would call that a bug or not but it sure was anoying
trying to figure it out over the last several hours.
"Bruce L-C [MVP]" wrote:
> What backend are you going against (SQL Server, OLEDB, ODBC?).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > I have a dataset that calls a stored procedure. it's command type is set
> to
> > stored procedure.
> >
> > The stored procedure returns 1 result set from a temp table (like select *
> > from #temp). In sql query analyzer it only returns 1 grid so I know it's
> not
> > returning multiple result sets.
> >
> > when I hit the ! button I fill out my parameters and run the stored
> > procedure. It returns all the columns with some data. good. but the only
> > field I see is one called ID of type database field.
> >
> > I hit the refresh fields button and nothing changes. The dataset is
> > returning 1 result set with the columns and data. WhyOWhy are the fields
> not
> > being filled out?
>
>|||Hmmm, you must have had some special characters in it. I create temporary
tables and do a select * from it without have to put [] around it. I'll
remember that though since from time to time I have been unable to help
people who do not get the field list. Learn something new every day.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> I figured it out!
> in my stored procedure I was doing this
> create table [#whatever]
> select * from #whatever
> this runs fine from sql query analyzer but does not return a list of
fields
> in report designer gui.
> but changing my stored procedure to
> create table [#whatever]
> select * from [#whatever]
> fixed the problem. I now get the entire list of fields in the gui.
> I'm not sure if I would call that a bug or not but it sure was anoying
> trying to figure it out over the last several hours.
>
> "Bruce L-C [MVP]" wrote:
> > What backend are you going against (SQL Server, OLEDB, ODBC?).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > > I have a dataset that calls a stored procedure. it's command type is
set
> > to
> > > stored procedure.
> > >
> > > The stored procedure returns 1 result set from a temp table (like
select *
> > > from #temp). In sql query analyzer it only returns 1 grid so I know
it's
> > not
> > > returning multiple result sets.
> > >
> > > when I hit the ! button I fill out my parameters and run the stored
> > > procedure. It returns all the columns with some data. good. but the
only
> > > field I see is one called ID of type database field.
> > >
> > > I hit the refresh fields button and nothing changes. The dataset is
> > > returning 1 result set with the columns and data. WhyOWhy are the
fields
> > not
> > > being filled out?
> >
> >
> >|||all right I take it back after more testing I can reproduce this behavior but
I was not aware it worked like this.
forget what I said before it was just a fluke I must have made a mistake
while testing it.
here is the reproducable behavior.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE
dbo.whatever
@.param_me int
as
-- end results temp table
create table [#whatever]
(
[key] int not null,
)
if (@.param_me is null)
begin
select 'how did you get here?'
end
else
begin
insert into [#whatever]([key])values(1)
insert into [#whatever]([key])values(2)
insert into [#whatever]([key])values(3)
end
select * from [#whatever]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create a dataset that calls whatever passing a param value
it looks like even though the select 'how did you get here?' never gets run
it somehow gets returned as the first result set?
bruce could you give this a whirl and see if you see the same result? I
would appreciate it thanks.
"Bruce L-C [MVP]" wrote:
> Hmmm, you must have had some special characters in it. I create temporary
> tables and do a select * from it without have to put [] around it. I'll
> remember that though since from time to time I have been unable to help
> people who do not get the field list. Learn something new every day.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> > I figured it out!
> >
> > in my stored procedure I was doing this
> > create table [#whatever]
> > select * from #whatever
> >
> > this runs fine from sql query analyzer but does not return a list of
> fields
> > in report designer gui.
> >
> > but changing my stored procedure to
> > create table [#whatever]
> > select * from [#whatever]
> >
> > fixed the problem. I now get the entire list of fields in the gui.
> >
> > I'm not sure if I would call that a bug or not but it sure was anoying
> > trying to figure it out over the last several hours.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > > > I have a dataset that calls a stored procedure. it's command type is
> set
> > > to
> > > > stored procedure.
> > > >
> > > > The stored procedure returns 1 result set from a temp table (like
> select *
> > > > from #temp). In sql query analyzer it only returns 1 grid so I know
> it's
> > > not
> > > > returning multiple result sets.
> > > >
> > > > when I hit the ! button I fill out my parameters and run the stored
> > > > procedure. It returns all the columns with some data. good. but the
> only
> > > > field I see is one called ID of type database field.
> > > >
> > > > I hit the refresh fields button and nothing changes. The dataset is
> > > > returning 1 result set with the columns and data. WhyOWhy are the
> fields
> > > not
> > > > being filled out?
> > >
> > >
> > >
>
>|||I think what is happening is that it is considering the first select to be
the first return result. You could temporarily remove that part, get your
field list and design your report and then put it back in.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> all right I take it back after more testing I can reproduce this behavior
> but
> I was not aware it worked like this.
> forget what I said before it was just a fluke I must have made a mistake
> while testing it.
> here is the reproducable behavior.
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE
> dbo.whatever
> @.param_me int
> as
> -- end results temp table
> create table [#whatever]
> (
> [key] int not null,
> )
> if (@.param_me is null)
> begin
> select 'how did you get here?'
> end
> else
> begin
> insert into [#whatever]([key])values(1)
> insert into [#whatever]([key])values(2)
> insert into [#whatever]([key])values(3)
> end
> select * from [#whatever]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> create a dataset that calls whatever passing a param value
> it looks like even though the select 'how did you get here?' never gets
> run
> it somehow gets returned as the first result set?
> bruce could you give this a whirl and see if you see the same result? I
> would appreciate it thanks.
>
> "Bruce L-C [MVP]" wrote:
>> Hmmm, you must have had some special characters in it. I create temporary
>> tables and do a select * from it without have to put [] around it. I'll
>> remember that though since from time to time I have been unable to help
>> people who do not get the field list. Learn something new every day.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
>> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
>> > I figured it out!
>> >
>> > in my stored procedure I was doing this
>> > create table [#whatever]
>> > select * from #whatever
>> >
>> > this runs fine from sql query analyzer but does not return a list of
>> fields
>> > in report designer gui.
>> >
>> > but changing my stored procedure to
>> > create table [#whatever]
>> > select * from [#whatever]
>> >
>> > fixed the problem. I now get the entire list of fields in the gui.
>> >
>> > I'm not sure if I would call that a bug or not but it sure was anoying
>> > trying to figure it out over the last several hours.
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
>> > > message
>> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
>> > > > I have a dataset that calls a stored procedure. it's command type
>> > > > is
>> set
>> > > to
>> > > > stored procedure.
>> > > >
>> > > > The stored procedure returns 1 result set from a temp table (like
>> select *
>> > > > from #temp). In sql query analyzer it only returns 1 grid so I
>> > > > know
>> it's
>> > > not
>> > > > returning multiple result sets.
>> > > >
>> > > > when I hit the ! button I fill out my parameters and run the stored
>> > > > procedure. It returns all the columns with some data. good. but
>> > > > the
>> only
>> > > > field I see is one called ID of type database field.
>> > > >
>> > > > I hit the refresh fields button and nothing changes. The dataset
>> > > > is
>> > > > returning 1 result set with the columns and data. WhyOWhy are the
>> fields
>> > > not
>> > > > being filled out?
>> > >
>> > >
>> > >
>>|||Yes I think thatâ's become obvious; the first result set that is tripping the
refresh fields button up. Because if I give the select 'how did you get
here?' as error_column. I see error_column in the list of fields, even
though that result set did not get returned and the data for the other result
set is really being displayed.
It's just confusing. All of the tools I'm using query analyzer, query
designer don't return this result set when I run the stored procedure with a
param value other than null.
Whatever the refresh fields button does it's not refresh fields, it's should
be called "return first potential result field list regardless of what
columns are really returned"
It's not a show stopper there are lots of easy work a rounds. It just sucks
using a version 1 product with so many of these little time wasters. But I
already know you think rs is super duper, I don't think so just yet.
try using rs.exe to create a datasource, publish a report and set the
published report datasource in your rs.exe script. It does not work I opened
up a support case 2 weeks ago with microsoft and they are still trying to
figure out why it does not work. lots of little time wasters.
ok I'm done complaining now.
"Bruce L-C [MVP]" wrote:
> I think what is happening is that it is considering the first select to be
> the first return result. You could temporarily remove that part, get your
> field list and design your report and then put it back in.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> > all right I take it back after more testing I can reproduce this behavior
> > but
> > I was not aware it worked like this.
> >
> > forget what I said before it was just a fluke I must have made a mistake
> > while testing it.
> >
> > here is the reproducable behavior.
> >
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > CREATE PROCEDURE
> > dbo.whatever
> > @.param_me int
> >
> > as
> >
> > -- end results temp table
> > create table [#whatever]
> > (
> > [key] int not null,
> > )
> >
> > if (@.param_me is null)
> > begin
> > select 'how did you get here?'
> > end
> > else
> > begin
> > insert into [#whatever]([key])values(1)
> > insert into [#whatever]([key])values(2)
> > insert into [#whatever]([key])values(3)
> > end
> > select * from [#whatever]
> >
> > GO
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > create a dataset that calls whatever passing a param value
> >
> > it looks like even though the select 'how did you get here?' never gets
> > run
> > it somehow gets returned as the first result set?
> >
> > bruce could you give this a whirl and see if you see the same result? I
> > would appreciate it thanks.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Hmmm, you must have had some special characters in it. I create temporary
> >> tables and do a select * from it without have to put [] around it. I'll
> >> remember that though since from time to time I have been unable to help
> >> people who do not get the field list. Learn something new every day.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> >> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> >> > I figured it out!
> >> >
> >> > in my stored procedure I was doing this
> >> > create table [#whatever]
> >> > select * from #whatever
> >> >
> >> > this runs fine from sql query analyzer but does not return a list of
> >> fields
> >> > in report designer gui.
> >> >
> >> > but changing my stored procedure to
> >> > create table [#whatever]
> >> > select * from [#whatever]
> >> >
> >> > fixed the problem. I now get the entire list of fields in the gui.
> >> >
> >> > I'm not sure if I would call that a bug or not but it sure was anoying
> >> > trying to figure it out over the last several hours.
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
> >> > > message
> >> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> >> > > > I have a dataset that calls a stored procedure. it's command type
> >> > > > is
> >> set
> >> > > to
> >> > > > stored procedure.
> >> > > >
> >> > > > The stored procedure returns 1 result set from a temp table (like
> >> select *
> >> > > > from #temp). In sql query analyzer it only returns 1 grid so I
> >> > > > know
> >> it's
> >> > > not
> >> > > > returning multiple result sets.
> >> > > >
> >> > > > when I hit the ! button I fill out my parameters and run the stored
> >> > > > procedure. It returns all the columns with some data. good. but
> >> > > > the
> >> only
> >> > > > field I see is one called ID of type database field.
> >> > > >
> >> > > > I hit the refresh fields button and nothing changes. The dataset
> >> > > > is
> >> > > > returning 1 result set with the columns and data. WhyOWhy are the
> >> fields
> >> > > not
> >> > > > being filled out?
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||Glad you got it to work out. Yes I am a supporter of RS but I don't deny
there are things that need to get better (it is version 1). That said, I
think any product or development work has these types of frustrations. I
just spent a day mucking around with linked databases and finally found that
the issue comes down to the oledb/odbc provider for Sybase. So, day wasted
and figure out some other way to do it. I just try to get people to be able
to get done what they are trying to do.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:D3C917F1-06B6-4B03-92DC-7D6D261EFAC4@.microsoft.com...
> Yes I think that's become obvious; the first result set that is tripping
the
> refresh fields button up. Because if I give the select 'how did you get
> here?' as error_column. I see error_column in the list of fields, even
> though that result set did not get returned and the data for the other
result
> set is really being displayed.
> It's just confusing. All of the tools I'm using query analyzer, query
> designer don't return this result set when I run the stored procedure with
a
> param value other than null.
> Whatever the refresh fields button does it's not refresh fields, it's
should
> be called "return first potential result field list regardless of what
> columns are really returned"
> It's not a show stopper there are lots of easy work a rounds. It just
sucks
> using a version 1 product with so many of these little time wasters. But
I
> already know you think rs is super duper, I don't think so just yet.
> try using rs.exe to create a datasource, publish a report and set the
> published report datasource in your rs.exe script. It does not work I
opened
> up a support case 2 weeks ago with microsoft and they are still trying to
> figure out why it does not work. lots of little time wasters.
> ok I'm done complaining now.
>
> "Bruce L-C [MVP]" wrote:
> > I think what is happening is that it is considering the first select to
be
> > the first return result. You could temporarily remove that part, get
your
> > field list and design your report and then put it back in.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> > > all right I take it back after more testing I can reproduce this
behavior
> > > but
> > > I was not aware it worked like this.
> > >
> > > forget what I said before it was just a fluke I must have made a
mistake
> > > while testing it.
> > >
> > > here is the reproducable behavior.
> > >
> > > SET QUOTED_IDENTIFIER OFF
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > >
> > > CREATE PROCEDURE
> > > dbo.whatever
> > > @.param_me int
> > >
> > > as
> > >
> > > -- end results temp table
> > > create table [#whatever]
> > > (
> > > [key] int not null,
> > > )
> > >
> > > if (@.param_me is null)
> > > begin
> > > select 'how did you get here?'
> > > end
> > > else
> > > begin
> > > insert into [#whatever]([key])values(1)
> > > insert into [#whatever]([key])values(2)
> > > insert into [#whatever]([key])values(3)
> > > end
> > > select * from [#whatever]
> > >
> > > GO
> > > SET QUOTED_IDENTIFIER OFF
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > >
> > > create a dataset that calls whatever passing a param value
> > >
> > > it looks like even though the select 'how did you get here?' never
gets
> > > run
> > > it somehow gets returned as the first result set?
> > >
> > > bruce could you give this a whirl and see if you see the same result?
I
> > > would appreciate it thanks.
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> Hmmm, you must have had some special characters in it. I create
temporary
> > >> tables and do a select * from it without have to put [] around it.
I'll
> > >> remember that though since from time to time I have been unable to
help
> > >> people who do not get the field list. Learn something new every day.
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
message
> > >> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> > >> > I figured it out!
> > >> >
> > >> > in my stored procedure I was doing this
> > >> > create table [#whatever]
> > >> > select * from #whatever
> > >> >
> > >> > this runs fine from sql query analyzer but does not return a list
of
> > >> fields
> > >> > in report designer gui.
> > >> >
> > >> > but changing my stored procedure to
> > >> > create table [#whatever]
> > >> > select * from [#whatever]
> > >> >
> > >> > fixed the problem. I now get the entire list of fields in the gui.
> > >> >
> > >> > I'm not sure if I would call that a bug or not but it sure was
anoying
> > >> > trying to figure it out over the last several hours.
> > >> >
> > >> >
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
> > >> > > message
> > >> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > >> > > > I have a dataset that calls a stored procedure. it's command
type
> > >> > > > is
> > >> set
> > >> > > to
> > >> > > > stored procedure.
> > >> > > >
> > >> > > > The stored procedure returns 1 result set from a temp table
(like
> > >> select *
> > >> > > > from #temp). In sql query analyzer it only returns 1 grid so I
> > >> > > > know
> > >> it's
> > >> > > not
> > >> > > > returning multiple result sets.
> > >> > > >
> > >> > > > when I hit the ! button I fill out my parameters and run the
stored
> > >> > > > procedure. It returns all the columns with some data. good.
but
> > >> > > > the
> > >> only
> > >> > > > field I see is one called ID of type database field.
> > >> > > >
> > >> > > > I hit the refresh fields button and nothing changes. The
dataset
> > >> > > > is
> > >> > > > returning 1 result set with the columns and data. WhyOWhy are
the
> > >> fields
> > >> > > not
> > >> > > > being filled out?
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> > >>
> >
> >
> >