Missing Fields

I am calling a stored procedure from reporting services. The problem I'm
having is that it's only returning the first field in the result set in the
field list.
When I query under the data tab, data and field names are there and all data
is returned successfully.
I have tried refreshing the fieds, refreshing data, creating a new
report...you name it.
I have used both the sql server and ole db providers and both produce the
same behavoir.
The stored procedure has 5 parameters. I have tried both temp tables and
table variables in the stored procedure to no avail.
When I manually add the fields I get errors as well.
Can anyone provide any help or insight? I'm stumped on this one.
Thanks!OK...I think I have this figured out, although I'm not really happy about the
behavoir. Perhaps MS can put this on their list of issues.
It appears that if you use a stored procedure within another stored
procedure (say the one your calling to populate your report) , it throws off
the fields that are returned.
In my case, I have a stored procedure internal to the one I was calling that
figures out the current date. The return value was the only field being
returned within the reporting services .net interface.
Perhaps it's the way I'm calling the SP and assigning the return value to an
internal variable.
Nonetheless...I hope this helps. Hopefully someone from MS will comment on
this behavoir for us.
"Scott M" wrote:
> I am calling a stored procedure from reporting services. The problem I'm
> having is that it's only returning the first field in the result set in the
> field list.
> When I query under the data tab, data and field names are there and all data
> is returned successfully.
> I have tried refreshing the fieds, refreshing data, creating a new
> report...you name it.
> I have used both the sql server and ole db providers and both produce the
> same behavoir.
>
> The stored procedure has 5 parameters. I have tried both temp tables and
> table variables in the stored procedure to no avail.
>
> When I manually add the fields I get errors as well.
> Can anyone provide any help or insight? I'm stumped on this one.
> Thanks!|||Can you duplicate this behavior with either adventureworks2000 or northwind.
I would like to investigate this and see if there is a workaround.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott M" <ScottM@.discussions.microsoft.com> wrote in message
news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> OK...I think I have this figured out, although I'm not really happy about
the
> behavoir. Perhaps MS can put this on their list of issues.
> It appears that if you use a stored procedure within another stored
> procedure (say the one your calling to populate your report) , it throws
off
> the fields that are returned.
> In my case, I have a stored procedure internal to the one I was calling
that
> figures out the current date. The return value was the only field being
> returned within the reporting services .net interface.
> Perhaps it's the way I'm calling the SP and assigning the return value to
an
> internal variable.
> Nonetheless...I hope this helps. Hopefully someone from MS will comment
on
> this behavoir for us.
> "Scott M" wrote:
> > I am calling a stored procedure from reporting services. The problem
I'm
> > having is that it's only returning the first field in the result set in
the
> > field list.
> >
> > When I query under the data tab, data and field names are there and all
data
> > is returned successfully.
> >
> > I have tried refreshing the fieds, refreshing data, creating a new
> > report...you name it.
> >
> > I have used both the sql server and ole db providers and both produce
the
> > same behavoir.
> >
> >
> > The stored procedure has 5 parameters. I have tried both temp tables
and
> > table variables in the stored procedure to no avail.
> >
> >
> > When I manually add the fields I get errors as well.
> >
> > Can anyone provide any help or insight? I'm stumped on this one.
> >
> > Thanks!|||Bruce,
I don't have those databases installed here at work...but I can give you the
essence of what happened.
There was a stored procedure called within a wrapper stored procedure. The
internal stored procedure was written to return the current period code
(200408, 200409 etc).
The wrapper stored procedure returned a recordset based on the period code
that was returned in the internal stored procedure.
When the report executed the wrapper stored procedure, it was only returning
the record from the internal stored procedure. So all I got was one field
with a value of the current period code.
I resolved the issue by creating a function to return the period code. That
seemed to fix it.
There might be a better way to call an internal stored procedure than the
way this was used. It was assigning an internally declared variable within
the wrapper stored procedure to the output parameter on the stored procedure
that returns the current period code.
Not sure if that helps. Seems like a bug to me, but perhaps it's by design
for some strange reason. Nonetheless, I'll have to re-write a lot of SP's in
order to utilize Reporting Services since the developer who originally
created a lot of the procedures in our environment didn't seem to utilize
functions.
Thanks for your response. Let me know if I can provide any further insight.
Scott
"Bruce L-C [MVP]" wrote:
> Can you duplicate this behavior with either adventureworks2000 or northwind.
> I would like to investigate this and see if there is a workaround.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott M" <ScottM@.discussions.microsoft.com> wrote in message
> news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> > OK...I think I have this figured out, although I'm not really happy about
> the
> > behavoir. Perhaps MS can put this on their list of issues.
> >
> > It appears that if you use a stored procedure within another stored
> > procedure (say the one your calling to populate your report) , it throws
> off
> > the fields that are returned.
> >
> > In my case, I have a stored procedure internal to the one I was calling
> that
> > figures out the current date. The return value was the only field being
> > returned within the reporting services .net interface.
> >
> > Perhaps it's the way I'm calling the SP and assigning the return value to
> an
> > internal variable.
> >
> > Nonetheless...I hope this helps. Hopefully someone from MS will comment
> on
> > this behavoir for us.
> >
> > "Scott M" wrote:
> >
> > > I am calling a stored procedure from reporting services. The problem
> I'm
> > > having is that it's only returning the first field in the result set in
> the
> > > field list.
> > >
> > > When I query under the data tab, data and field names are there and all
> data
> > > is returned successfully.
> > >
> > > I have tried refreshing the fieds, refreshing data, creating a new
> > > report...you name it.
> > >
> > > I have used both the sql server and ole db providers and both produce
> the
> > > same behavoir.
> > >
> > >
> > > The stored procedure has 5 parameters. I have tried both temp tables
> and
> > > table variables in the stored procedure to no avail.
> > >
> > >
> > > When I manually add the fields I get errors as well.
> > >
> > > Can anyone provide any help or insight? I'm stumped on this one.
> > >
> > > Thanks!
>
>|||I've encountered the same problem. I've created about 50 reports based on
stored procedures and this is the first time this has occured.
The proc in question has 11 parameters. It returns the result set in the
Data Preview section, but only returns a single field in the field list. The
field returned is not even in the actual result set.
The proc I'm calling does call other procs. It was created bby a third party
and is used for other functions as well as the report I'm writing, so I can't
modify it.
Any ideas?
"Bruce L-C [MVP]" wrote:
> Can you duplicate this behavior with either adventureworks2000 or northwind.
> I would like to investigate this and see if there is a workaround.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott M" <ScottM@.discussions.microsoft.com> wrote in message
> news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> > OK...I think I have this figured out, although I'm not really happy about
> the
> > behavoir. Perhaps MS can put this on their list of issues.
> >
> > It appears that if you use a stored procedure within another stored
> > procedure (say the one your calling to populate your report) , it throws
> off
> > the fields that are returned.
> >
> > In my case, I have a stored procedure internal to the one I was calling
> that
> > figures out the current date. The return value was the only field being
> > returned within the reporting services .net interface.
> >
> > Perhaps it's the way I'm calling the SP and assigning the return value to
> an
> > internal variable.
> >
> > Nonetheless...I hope this helps. Hopefully someone from MS will comment
> on
> > this behavoir for us.
> >
> > "Scott M" wrote:
> >
> > > I am calling a stored procedure from reporting services. The problem
> I'm
> > > having is that it's only returning the first field in the result set in
> the
> > > field list.
> > >
> > > When I query under the data tab, data and field names are there and all
> data
> > > is returned successfully.
> > >
> > > I have tried refreshing the fieds, refreshing data, creating a new
> > > report...you name it.
> > >
> > > I have used both the sql server and ole db providers and both produce
> the
> > > same behavoir.
> > >
> > >
> > > The stored procedure has 5 parameters. I have tried both temp tables
> and
> > > table variables in the stored procedure to no avail.
> > >
> > >
> > > When I manually add the fields I get errors as well.
> > >
> > > Can anyone provide any help or insight? I'm stumped on this one.
> > >
> > > Thanks!
>
>