Nested stored procedure

I have a report, say for example "Dummy report" which has its dataset to be a
stored procedure SP1. This stored procedure SP1 gets its data from another
stored procedure (SP2) that is nested in it.
When I try to generate "Dummy report" from report designer for the first
time, it fails. What I need to do now is run SP2 individually from SSMS, and
then run the "Dummy report" from designer to see the report output. Every
time I make a change to SP2, I have to do the same thing - run SP2
individually and then generate the report. I am not able to understand why
this is happening. Can anyone help me with this problem? Thanks in advance.
SQL Server 2005 SP1/Windows Server 2003On Mar 9, 12:23 pm, KMP <K...@.discussions.microsoft.com> wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003
If I'm understanding you correctly, it has something to do w/why a
stored procedure cannot be initially used when creating a report.
Basically, the report needs to know the dataset format and what data
to expect, etc up front. Hopefully in a future version of SSRS, this
will be corrected.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Have you checked, that your sp1 is working from SSMS ? Just check whether
you have given hints in the query or recompile etc... and check whether it
returns single set of data.
Amarnath, MCTS
"KMP" wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003|||If your first stored procedure is creating a temp table that is then filled
by the second stored procedure then you need to add a line to your stored
procedure.
SET FMTONLY OFF
The issue is that when RS is trying to create the field list it calls the
stored procedure with the set fmtonly on (i.e. it doesn't really execute
it). This means it does not create the temp table either (although I have
found I only have this issue with nested stored procedures).
Anyway, add the statement at the top of you calling stored procedure.
The only other issue, sometimes you have to click on the refresh fields
button, it is one of the buttons to the right of the ...
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:21160AC2-8078-4CA8-A70D-494ACB4F3168@.microsoft.com...
>I have a report, say for example "Dummy report" which has its dataset to be
>a
> stored procedure SP1. This stored procedure SP1 gets its data from
> another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS,
> and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in
> advance.
> SQL Server 2005 SP1/Windows Server 2003|||SP1 does not run from SMSS. I tried WITH RECOMPILE option form both stored
procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
the query. Please advise.
By the way SET FMTONLY OFF option did not help either. Thanks for all the
ideas. Still need to find a solution though...
"Amarnath" wrote:
> Have you checked, that your sp1 is working from SSMS ? Just check whether
> you have given hints in the query or recompile etc... and check whether it
> returns single set of data.
> Amarnath, MCTS
>
> "KMP" wrote:
> > I have a report, say for example "Dummy report" which has its dataset to be a
> > stored procedure SP1. This stored procedure SP1 gets its data from another
> > stored procedure (SP2) that is nested in it.
> >
> > When I try to generate "Dummy report" from report designer for the first
> > time, it fails. What I need to do now is run SP2 individually from SSMS, and
> > then run the "Dummy report" from designer to see the report output. Every
> > time I make a change to SP2, I have to do the same thing - run SP2
> > individually and then generate the report. I am not able to understand why
> > this is happening. Can anyone help me with this problem? Thanks in advance.
> >
> > SQL Server 2005 SP1/Windows Server 2003|||Please help!!!
"KMP" wrote:
> SP1 does not run from SMSS. I tried WITH RECOMPILE option form both stored
> procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
> the query. Please advise.
> By the way SET FMTONLY OFF option did not help either. Thanks for all the
> ideas. Still need to find a solution though...
> "Amarnath" wrote:
> > Have you checked, that your sp1 is working from SSMS ? Just check whether
> > you have given hints in the query or recompile etc... and check whether it
> > returns single set of data.
> >
> > Amarnath, MCTS
> >
> >
> > "KMP" wrote:
> >
> > > I have a report, say for example "Dummy report" which has its dataset to be a
> > > stored procedure SP1. This stored procedure SP1 gets its data from another
> > > stored procedure (SP2) that is nested in it.
> > >
> > > When I try to generate "Dummy report" from report designer for the first
> > > time, it fails. What I need to do now is run SP2 individually from SSMS, and
> > > then run the "Dummy report" from designer to see the report output. Every
> > > time I make a change to SP2, I have to do the same thing - run SP2
> > > individually and then generate the report. I am not able to understand why
> > > this is happening. Can anyone help me with this problem? Thanks in advance.
> > >
> > > SQL Server 2005 SP1/Windows Server 2003|||Any help will be greatly appreciated. Thank you!
"KMP" wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003|||I thought I had answered this one.
Anyway, is the issue that fields are not showing up in RS?
If SP1 calls SP2 then when you run SP1 from RS both should execute. If the
issue is that you are not seeing the new fields then try the below:
Temp tables should work for you. I use them a whole lot. Do the following:
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
If none of these work then add Set FMTONLY Off (the below 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."
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
> Any help will be greatly appreciated. Thank you!
> "KMP" wrote:
>> I have a report, say for example "Dummy report" which has its dataset to
>> be a
>> stored procedure SP1. This stored procedure SP1 gets its data from
>> another
>> stored procedure (SP2) that is nested in it.
>> When I try to generate "Dummy report" from report designer for the first
>> time, it fails. What I need to do now is run SP2 individually from SSMS,
>> and
>> then run the "Dummy report" from designer to see the report output. Every
>> time I make a change to SP2, I have to do the same thing - run SP2
>> individually and then generate the report. I am not able to understand
>> why
>> this is happening. Can anyone help me with this problem? Thanks in
>> advance.
>> SQL Server 2005 SP1/Windows Server 2003|||Sorry if I am posting this over and over again. Seems to be more a SQL Server
issue, not RS. But below is my previous post and what happened when I tried
the different ideas:
"SP1 does not run from SMSS. I tried WITH RECOMPILE option for both stored
procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
the query. Please advise.
By the way SET FMTONLY OFF option did not help either."
"Bruce L-C [MVP]" wrote:
> I thought I had answered this one.
> Anyway, is the issue that fields are not showing up in RS?
> If SP1 calls SP2 then when you run SP1 from RS both should execute. If the
> issue is that you are not seeing the new fields then try the below:
> Temp tables should work for you. I use them a whole lot. Do the following:
> 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
> If none of these work then add Set FMTONLY Off (the below 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."
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
> > Any help will be greatly appreciated. Thank you!
> >
> > "KMP" wrote:
> >
> >> I have a report, say for example "Dummy report" which has its dataset to
> >> be a
> >> stored procedure SP1. This stored procedure SP1 gets its data from
> >> another
> >> stored procedure (SP2) that is nested in it.
> >>
> >> When I try to generate "Dummy report" from report designer for the first
> >> time, it fails. What I need to do now is run SP2 individually from SSMS,
> >> and
> >> then run the "Dummy report" from designer to see the report output. Every
> >> time I make a change to SP2, I have to do the same thing - run SP2
> >> individually and then generate the report. I am not able to understand
> >> why
> >> this is happening. Can anyone help me with this problem? Thanks in
> >> advance.
> >>
> >> SQL Server 2005 SP1/Windows Server 2003
>
>|||Ahh, this is not a Reporting Services issue. If you cannot run this from
outside of Reporting Services then all the advice I gave does not help you.
I suggest posting on the SQL Server newsgroups. They will be able to help
you,. First make sure everything works from outside of RS before trying to
create a report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:07013913-3721-4A9C-9B54-C6B2259ED23D@.microsoft.com...
> Sorry if I am posting this over and over again. Seems to be more a SQL
> Server
> issue, not RS. But below is my previous post and what happened when I
> tried
> the different ideas:
> "SP1 does not run from SMSS. I tried WITH RECOMPILE option for both stored
> procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
> the query. Please advise.
> By the way SET FMTONLY OFF option did not help either."
>
> "Bruce L-C [MVP]" wrote:
>> I thought I had answered this one.
>> Anyway, is the issue that fields are not showing up in RS?
>> If SP1 calls SP2 then when you run SP1 from RS both should execute. If
>> the
>> issue is that you are not seeing the new fields then try the below:
>> Temp tables should work for you. I use them a whole lot. Do the
>> following:
>> 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
>> If none of these work then add Set FMTONLY Off (the below 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."
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
>> > Any help will be greatly appreciated. Thank you!
>> >
>> > "KMP" wrote:
>> >
>> >> I have a report, say for example "Dummy report" which has its dataset
>> >> to
>> >> be a
>> >> stored procedure SP1. This stored procedure SP1 gets its data from
>> >> another
>> >> stored procedure (SP2) that is nested in it.
>> >>
>> >> When I try to generate "Dummy report" from report designer for the
>> >> first
>> >> time, it fails. What I need to do now is run SP2 individually from
>> >> SSMS,
>> >> and
>> >> then run the "Dummy report" from designer to see the report output.
>> >> Every
>> >> time I make a change to SP2, I have to do the same thing - run SP2
>> >> individually and then generate the report. I am not able to understand
>> >> why
>> >> this is happening. Can anyone help me with this problem? Thanks in
>> >> advance.
>> >>
>> >> SQL Server 2005 SP1/Windows Server 2003
>>