How do I get a Fields List with Stored Procedures

If I type in a query I get a fields list. However I do not with a stored
procedure. I have tried pressing the Refresh button. This does not work. I
have tried entering the fields manually, but when I get to the value column,
the expression builder tells me that the dataset has no fields. I can run
the stored procedure in Reporting Services and get the correct results. I
just cannot figure out how to get a dataset to see the fields in a stored
procedure.
I've been trying everything I can think of for two days. Does anyone out
there know how to get the fields to show with a stored procedure?The value will be the same as the field name and the type is databasefield
so for example:
fieldname type value
CREW DatabaseField CREW
AND SO ON
You can't use the expression to pick fields because you have not entered the
fields manually. I find I have to enter my fields manually if I have temp
tables used in my stored proc
"Liz" wrote:
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work. I
> have tried entering the fields manually, but when I get to the value column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||They should show automatically when you execute the dataset on the Data tab.
Are you sure you have a command type of 'storedprocedure'?
Worst case scenario try deleting your dataset and recreating it.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:6C363085-B23B-4B5B-AC32-1C0C1DA4AF3F@.microsoft.com...
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work.
> I
> have tried entering the fields manually, but when I get to the value
> column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||I have tried that. I have also tried right-clicking on the fields list pane
and adding fields. Either way when I try to pull the fields into the report,
they get put in there as SUM (Fields.fieldname.value). When I edit the sum
function away, I get an error saying that the field is not in the dataset.
When I hit Refresh, the fields disappear.
There are no temp tables or table variables in the stored procedure. It
returns one resultset.
How do I delete a dataset?
"John Grant" wrote:
> The value will be the same as the field name and the type is databasefield
> so for example:
> fieldname type value
> CREW DatabaseField CREW
> AND SO ON
> You can't use the expression to pick fields because you have not entered the
> fields manually. I find I have to enter my fields manually if I have temp
> tables used in my stored proc
> "Liz" wrote:
> > If I type in a query I get a fields list. However I do not with a stored
> > procedure. I have tried pressing the Refresh button. This does not work. I
> > have tried entering the fields manually, but when I get to the value column,
> > the expression builder tells me that the dataset has no fields. I can run
> > the stored procedure in Reporting Services and get the correct results. I
> > just cannot figure out how to get a dataset to see the fields in a stored
> > procedure.
> >
> > I've been trying everything I can think of for two days. Does anyone out
> > there know how to get the fields to show with a stored procedure?|||Liz,
I fought with the same issue for 2 days when first working with sprocs in
rs. after you set the command type to stored procedure, run it it once from
the data window. Then refresh it. that did the trick for me. if you have
parameters, don't forget to include them as well.
--
John Cleveland
Network Manager
Urban systems Ltd
"Liz" wrote:
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work. I
> have tried entering the fields manually, but when I get to the value column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||mmmmmmmmmmm, do you have set nocount on inside your stored proc?
"Liz" wrote:
> I have tried that. I have also tried right-clicking on the fields list pane
> and adding fields. Either way when I try to pull the fields into the report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
> > The value will be the same as the field name and the type is databasefield
> > so for example:
> >
> > fieldname type value
> > CREW DatabaseField CREW
> > AND SO ON
> >
> > You can't use the expression to pick fields because you have not entered the
> > fields manually. I find I have to enter my fields manually if I have temp
> > tables used in my stored proc
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||You have the button to delete dataset while in the data tab
"Liz" wrote:
> I have tried that. I have also tried right-clicking on the fields list pane
> and adding fields. Either way when I try to pull the fields into the report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
> > The value will be the same as the field name and the type is databasefield
> > so for example:
> >
> > fieldname type value
> > CREW DatabaseField CREW
> > AND SO ON
> >
> > You can't use the expression to pick fields because you have not entered the
> > fields manually. I find I have to enter my fields manually if I have temp
> > tables used in my stored proc
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||You have several good suggestions. Here is a list:
1. Click on the refresh fields button (to the right of the ...)
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
5. If your stored procedure calls another stored procedure then try this:
add Set FMTONLY Off (the following is from Simon Sabin a SQL Server MVP).
"The issue with RS is that the rowset of the SP is defined by calling the SP
with SET FMTONLY ON because Temp tables don't get created if you select from
the temp table the metadata from the rowset can't be returned. This can be
worked around by turning FMTONLY OFF in the SP."
One of the 5 above should solve your problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:59D6F2CC-1CE4-450F-9D55-74B724CB5AAF@.microsoft.com...
>I have tried that. I have also tried right-clicking on the fields list
>pane
> and adding fields. Either way when I try to pull the fields into the
> report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the
> sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
>> The value will be the same as the field name and the type is
>> databasefield
>> so for example:
>> fieldname type value
>> CREW DatabaseField CREW
>> AND SO ON
>> You can't use the expression to pick fields because you have not entered
>> the
>> fields manually. I find I have to enter my fields manually if I have
>> temp
>> tables used in my stored proc
>> "Liz" wrote:
>> > If I type in a query I get a fields list. However I do not with a
>> > stored
>> > procedure. I have tried pressing the Refresh button. This does not
>> > work. I
>> > have tried entering the fields manually, but when I get to the value
>> > column,
>> > the expression builder tells me that the dataset has no fields. I can
>> > run
>> > the stored procedure in Reporting Services and get the correct results.
>> > I
>> > just cannot figure out how to get a dataset to see the fields in a
>> > stored
>> > procedure.
>> >
>> > I've been trying everything I can think of for two days. Does anyone
>> > out
>> > there know how to get the fields to show with a stored procedure?|||I'm afraid that none of this is working. I deleted the dataset and recreated
it. I commented out the "Set Nocount On". I ran the stored procedure and
got the result set. I pressed Refresh. No good.
Any other ideas?
"John Cleveland" wrote:
> Liz,
> I fought with the same issue for 2 days when first working with sprocs in
> rs. after you set the command type to stored procedure, run it it once from
> the data window. Then refresh it. that did the trick for me. if you have
> parameters, don't forget to include them as well.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>
> "Liz" wrote:
> > If I type in a query I get a fields list. However I do not with a stored
> > procedure. I have tried pressing the Refresh button. This does not work. I
> > have tried entering the fields manually, but when I get to the value column,
> > the expression builder tells me that the dataset has no fields. I can run
> > the stored procedure in Reporting Services and get the correct results. I
> > just cannot figure out how to get a dataset to see the fields in a stored
> > procedure.
> >
> > I've been trying everything I can think of for two days. Does anyone out
> > there know how to get the fields to show with a stored procedure?|||Liz,
I know how frustrating it was for me to get it going as well. So if you
would like you can contact me dierctly. I'm not with MS at all, but do know
how to get it to work. Use my contact info below if you wish.
--
John Cleveland
Network Manager
Urban systems Ltd
office: (250) 762-2517
jcleveland@.urban-systems.com
"Liz" wrote:
> I'm afraid that none of this is working. I deleted the dataset and recreated
> it. I commented out the "Set Nocount On". I ran the stored procedure and
> got the result set. I pressed Refresh. No good.
> Any other ideas?
> "John Cleveland" wrote:
> > Liz,
> > I fought with the same issue for 2 days when first working with sprocs in
> > rs. after you set the command type to stored procedure, run it it once from
> > the data window. Then refresh it. that did the trick for me. if you have
> > parameters, don't forget to include them as well.
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> >
> >
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||On Apr 12, 6:02 pm, Liz <L...@.discussions.microsoft.com> wrote:
> I'm afraid that none of this is working. I deleted the dataset and recreated
> it. I commented out the "Set Nocount On". I ran the stored procedure and
> got the result set. I pressed Refresh. No good.
> Any other ideas?
>
> "John Cleveland" wrote:
> > Liz,
> > I fought with the same issue for 2 days when first working with sprocs in
> > rs. after you set the command type to stored procedure, run it it once from
> > the data window. Then refresh it. that did the trick for me. if you have
> > parameters, don't forget to include them as well.
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> > "Liz" wrote:
In your SP make a select * into temptable from ...
and then in the last line write select * from temptable
When you do that if you refresh the fields appear, after that just
remove the into command and the "select * from temptable" but you
can't refresh otherwise the fields disappear.
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure... Hide quoted text -
> - Show quoted text -