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 - empty result set for all queries?

I am a newbie and have installed SQL Server myself on my home machine,
where I'm having problems. When I run the Query Analyzer, I get an
empty result set for all queries, with the message, "The command(s)
completed successfully." So, for instance, if I were to go to
Northwind and type "SELECT * from Employees", which we KNOW isn't
empty, all I see is "The command(s) completed successfully." How do I
get query results?

I thought maybe the problem was permissions. I'm logged into my local
server, which is the computer on which I'm running. The owner is just
"ZIPPY/Owner", ZIPPY being my machine name; since I'm the only one who
uses the computer, I don't even log in, I just take the default "Owner"
account. I start up the SQL analyzer with Windows credentials.

Anyone have a clue what I'm doing wrong?shelah (shelah2@.verizon.net) writes:
> I am a newbie and have installed SQL Server myself on my home machine,
> where I'm having problems. When I run the Query Analyzer, I get an
> empty result set for all queries, with the message, "The command(s)
> completed successfully." So, for instance, if I were to go to
> Northwind and type "SELECT * from Employees", which we KNOW isn't
> empty, all I see is "The command(s) completed successfully." How do I
> get query results?

You are not even getting an empty result set. Apparently you are in some
sort of noexec mode where the query is sent to SQL Server for validation
only.

There are a couple of ways to get this behaviour. First make sure that
you press the green arrow to run the query. The blue tick is for validation
only.

If the problem is not that simple, check Tools->Options->Connection
properties. If any of SET NOEXEC ON or SET PARSEONLY ON are checked,
uncheck them.

If this does not help, send these commands to SQL Server:

SET PARSEONLY OFF
SET NOEXEC OFF
SET FMTONLY OFF

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you, that was an enormous help. You were right; I was clicking
the check and not the green arrow. A great relief to know that I have a
usable database and don't have to do some wierd chown gyrations.

*** Sent via Developersdex http://www.developersdex.com ***|||thanks it was a great help even to me
Erland Sommarskog wrote:
> shelah (shelah2@.verizon.net) writes:
> > I am a newbie and have installed SQL Server myself on my home machine,
> > where I'm having problems. When I run the Query Analyzer, I get an
> > empty result set for all queries, with the message, "The command(s)
> > completed successfully." So, for instance, if I were to go to
> > Northwind and type "SELECT * from Employees", which we KNOW isn't
> > empty, all I see is "The command(s) completed successfully." How do I
> > get query results?
> You are not even getting an empty result set. Apparently you are in some
> sort of noexec mode where the query is sent to SQL Server for validation
> only.
> There are a couple of ways to get this behaviour. First make sure that
> you press the green arrow to run the query. The blue tick is for validation
> only.
> If the problem is not that simple, check Tools->Options->Connection
> properties. If any of SET NOEXEC ON or SET PARSEONLY ON are checked,
> uncheck them.
> If this does not help, send these commands to SQL Server:
> SET PARSEONLY OFF
> SET NOEXEC OFF
> SET FMTONLY OFF
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||To avoid this in the future just hit F5

http://sqlservercode.blogspot.com/

newbie - DEBUG MODE?

Hello,
I have a query I'm working on in the Query Analyzer. My query has
variables...I wanted to know if there is a way to track the variable and
see its value (I.e. step through) is there a debug mode in the Query
Analyzer?
Thanks in advance.You can use SQL Debbuger.
From Query Analyzer, right-click on the stored procedure in the Object
Browser and select Debug. That will open SQL Debuuger.
"Fab" wrote:

> Hello,
> I have a query I'm working on in the Query Analyzer. My query has
> variables...I wanted to know if there is a way to track the variable and
> see its value (I.e. step through) is there a debug mode in the Query
> Analyzer?
> Thanks in advance.
>
>|||COOL THANKS JACK!
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:7BCCA5CF-34FC-44B7-820D-29D35A68F263@.microsoft.com...
> You can use SQL Debbuger.
> From Query Analyzer, right-click on the stored procedure in the Object
> Browser and select Debug. That will open SQL Debuuger.
> "Fab" wrote:
>