|
|
You're in the
sql server reporting services
group:How do I get a Fields List with Stored Procedures
sql server reporting services:
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
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 [quoted text, click to view] "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
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? [quoted text, click to view] "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
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 [quoted text, click to view] "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
mmmmmmmmmmm, do you have set nocount on inside your stored proc?
[quoted text, click to view] "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
You have the button to delete dataset while in the data tab
[quoted text, click to view] "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
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? [quoted text, click to view] "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
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 [quoted text, click to view] "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
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 [quoted text, click to view] "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?
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 [quoted text, click to view] "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?
[quoted text, click to view]
On Apr 12, 6:02 pm, Liz <L...@discussions.microsoft.com> wrote: In your SP make a select * into temptable from ...> 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: 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. [quoted text, click to view] > > > > 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 -
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |