Newbie question about sprocs

When I run my stored proceedure in query analyzer and copy the text to the pane in the report designer, all is well, but if I run the same stored proceedure as a stored procedure in the report designer it doesn't return all the fields that it does as text. What's the difference? What do I do to fix this?Two things come to mind. Ownership and location of the stored procedure.
OWNERSHIP:
Objects like tables, views, and stored procedures can be owned by different
logins.
Lets say you have two users in your database
user1
user2
They can both create a table called FOO
Each table can have different columns.
Each user can also create a stored procedure named procFOO
These stored procedures can return different columns.
LOCATION OF THE STORED PROCEDURE:
It is possible to create stored procedures in multiple databases. For
example, procFOO within northwind could select from the Products table and
procFOO within pubs could select from the authors table. Are you calling
the stored procedure from the database that you created it in (and are
running it from Query Analyzer)?
--
Keith
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:8F8426EB-7C6C-49C5-9C8F-825EB089A880@.microsoft.com...
> When I run my stored proceedure in query analyzer and copy the text to the
pane in the report designer, all is well, but if I run the same stored
proceedure as a stored procedure in the report designer it doesn't return
all the fields that it does as text. What's the difference? What do I do to
fix this?|||So I hit the nail on the head; was it an ownership or a location issue?
--
Keith
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:8EC3378E-037A-4C6A-93BE-7367603FDBE3@.microsoft.com...
> Thanks you were a great help. All is well!
> "Keith Kratochvil" wrote:
> > Two things come to mind. Ownership and location of the stored
procedure.
> >
> > OWNERSHIP:
> > Objects like tables, views, and stored procedures can be owned by
different
> > logins.
> > Lets say you have two users in your database
> > user1
> > user2
> > They can both create a table called FOO
> > Each table can have different columns.
> > Each user can also create a stored procedure named procFOO
> > These stored procedures can return different columns.
> >
> > LOCATION OF THE STORED PROCEDURE:
> > It is possible to create stored procedures in multiple databases. For
> > example, procFOO within northwind could select from the Products table
and
> > procFOO within pubs could select from the authors table. Are you
calling
> > the stored procedure from the database that you created it in (and are
> > running it from Query Analyzer)?
> >
> > --
> > Keith
> >
> >
> > "Arly" <Arly@.discussions.microsoft.com> wrote in message
> > news:8F8426EB-7C6C-49C5-9C8F-825EB089A880@.microsoft.com...
> > > When I run my stored proceedure in query analyzer and copy the text to
the
> > pane in the report designer, all is well, but if I run the same stored
> > proceedure as a stored procedure in the report designer it doesn't
return
> > all the fields that it does as text. What's the difference? What do I do
to
> > fix this?
> >
> >

newbie ? about stored procedures

I currently have several sprocs that require a variable which determines
which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
returns fields 4, 5, 6. When I run the sprocs the appropriate fields show up
in the results window but when I refresh the fields, reguardless of what
variable I used it displays X's fields in the fields window. Can I not create
two datasets running the same sproc using different variables. If not is this
so this something that I'll be able to do in the near future? Any help would
be most appreciated.Hey there,
As far as I know Reporting Services scans the Stored Procedure, caches the
field names and uses those.
Why don't you have your stored procedure return generic colum names such as
"Col1", "Col2", "Col3" and then apply some conditional formatting on those
column headers when they're placed on the report. Something like this:
txtColumn1.Value = IIF(Parameters!MyCondition.Value = "X", "Zip Code",
"Postal Code")
I'm pretty sure that this is by design since Reporting Services needs to
know the column names.
Hopefully I'm on the same page as you...
Ben
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:045E8DA3-0DA9-4EAE-9A7E-91343815D69D@.microsoft.com...
> I currently have several sprocs that require a variable which determines
> which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
> returns fields 4, 5, 6. When I run the sprocs the appropriate fields show
up
> in the results window but when I refresh the fields, reguardless of what
> variable I used it displays X's fields in the fields window. Can I not
create
> two datasets running the same sproc using different variables. If not is
this
> so this something that I'll be able to do in the near future? Any help
would
> be most appreciated.|||Thanks, Ben but that would mean changing hundreds of sprocs that are being
used by our VB programmers as well. Any other ideas?
"Benjamin Pierce" wrote:
> Hey there,
> As far as I know Reporting Services scans the Stored Procedure, caches the
> field names and uses those.
> Why don't you have your stored procedure return generic colum names such as
> "Col1", "Col2", "Col3" and then apply some conditional formatting on those
> column headers when they're placed on the report. Something like this:
> txtColumn1.Value = IIF(Parameters!MyCondition.Value = "X", "Zip Code",
> "Postal Code")
> I'm pretty sure that this is by design since Reporting Services needs to
> know the column names.
> Hopefully I'm on the same page as you...
>
> Ben
>
> "Arly" <Arly@.discussions.microsoft.com> wrote in message
> news:045E8DA3-0DA9-4EAE-9A7E-91343815D69D@.microsoft.com...
> > I currently have several sprocs that require a variable which determines
> > which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
> > returns fields 4, 5, 6. When I run the sprocs the appropriate fields show
> up
> > in the results window but when I refresh the fields, reguardless of what
> > variable I used it displays X's fields in the fields window. Can I not
> create
> > two datasets running the same sproc using different variables. If not is
> this
> > so this something that I'll be able to do in the near future? Any help
> would
> > be most appreciated.
>
>