"Invalid object name" in stored procedure

"Invalid object name" in stored procedure

Post by Laur » Sat, 10 Mar 2001 06:26:29



Has anyone ever run across the problem that an object (in this case, a view)
created in a dbo-owned stored procedure can not later be referenced in that
stored procedure?  Running 'sp_help viewname' tells me that the object
exists and that I am the owner of it, however, when it's referenced in the
stored procedure, I am getting an "Invalid object name" error when I try to
select from the view.

Here is a mightily watered-down version of what I'm doing:




                    select idvalue = max(id_field) from test_table';

/* at this point, running 'sp_help tmp_view' shows me that tmp_view exists
and is owned by me */


"Invalid object name" */
drop view tmp_view;
return

I'm running this proc by calling it like this:

The issue seems to be that the proc is owned by dbo.  Any idea how to work
my way around this issue?

Thanks,
Laura

 
 
 

"Invalid object name" in stored procedure

Post by Stefan » Sat, 10 Mar 2001 06:28:28


Laura,

If you are in the db_owner role, create the view as "create view
dbo.tmp_view ......" and you won't have any problems.


> Has anyone ever run across the problem that an object (in this case, a
view)
> created in a dbo-owned stored procedure can not later be referenced in
that
> stored procedure?  Running 'sp_help viewname' tells me that the object
> exists and that I am the owner of it, however, when it's referenced in the
> stored procedure, I am getting an "Invalid object name" error when I try
to
> select from the view.

> Here is a mightily watered-down version of what I'm doing:




>                     select idvalue = max(id_field) from test_table';

> /* at this point, running 'sp_help tmp_view' shows me that tmp_view exists
> and is owned by me */


> "Invalid object name" */
> drop view tmp_view;
> return

> I'm running this proc by calling it like this:


> The issue seems to be that the proc is owned by dbo.  Any idea how to work
> my way around this issue?

> Thanks,
> Laura


 
 
 

"Invalid object name" in stored procedure

Post by Keith Kratochvi » Sat, 10 Mar 2001 06:33:33


You do not need to use dynamic-sql.
You do not need to use a view.

If you post more information about what you WANT to do instead of what you
are doing, maybe I can help steer you in the right direction.

At the very least, you could simply have this within your stored procedure:
select idvalue = max(id_field) from test_table

If you want to use it later or return it as a column, (which I suspect you
do) you can do something like this:


SELECT idvalue = MAX(id_field) FROM test_table

And then later on within your stored procedure, you can do something like
this:

FROM SomeTable

Again, it would be helpful to know what you actually want to do with the
data...

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Has anyone ever run across the problem that an object (in this case, a
view)
> created in a dbo-owned stored procedure can not later be referenced in
that
> stored procedure?  Running 'sp_help viewname' tells me that the object
> exists and that I am the owner of it, however, when it's referenced in the
> stored procedure, I am getting an "Invalid object name" error when I try
to
> select from the view.

> Here is a mightily watered-down version of what I'm doing:




>                     select idvalue = max(id_field) from test_table';

> /* at this point, running 'sp_help tmp_view' shows me that tmp_view exists
> and is owned by me */


> "Invalid object name" */
> drop view tmp_view;
> return

> I'm running this proc by calling it like this:


> The issue seems to be that the proc is owned by dbo.  Any idea how to work
> my way around this issue?

> Thanks,
> Laura

 
 
 

"Invalid object name" in stored procedure

Post by Laur » Sat, 10 Mar 2001 06:52:37


Thanks for your quick replies!  I'll do my best to explain the situation a
little better:

This is a proc which will be run by various people on the server.  That is
why I don't preface the view name with 'dbo.'  Everyone needs to have their
own view so that if two people try to run the proc at the same time it won't
cause problems.

The reason I'm using a view in the first place is that the from and where
clause of the query are built on the fly in my proc (guess I simplified my
example a little too much).

What I REALLY want to do is set a variable based on a query I build on the
fly.  I use the process of concatinating my string and running it with
'exec' all over the place for other things.  When I had to set a variable
with the string, the only thing I could come up with was creating the view.
If anyone has a better method I'd really appreciate hearing it.

Thanks again,
Laura


> You do not need to use dynamic-sql.
> You do not need to use a view.

> If you post more information about what you WANT to do instead of what you
> are doing, maybe I can help steer you in the right direction.

> At the very least, you could simply have this within your stored
procedure:
> select idvalue = max(id_field) from test_table

> If you want to use it later or return it as a column, (which I suspect you
> do) you can do something like this:


> SELECT idvalue = MAX(id_field) FROM test_table

> And then later on within your stored procedure, you can do something like
> this:

> FROM SomeTable

> Again, it would be helpful to know what you actually want to do with the
> data...

> --
> Keith
> ==============
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Has anyone ever run across the problem that an object (in this case, a
> view)
> > created in a dbo-owned stored procedure can not later be referenced in
> that
> > stored procedure?  Running 'sp_help viewname' tells me that the object
> > exists and that I am the owner of it, however, when it's referenced in
the
> > stored procedure, I am getting an "Invalid object name" error when I try
> to
> > select from the view.

> > Here is a mightily watered-down version of what I'm doing:




> >                     select idvalue = max(id_field) from test_table';

> > /* at this point, running 'sp_help tmp_view' shows me that tmp_view
exists
> > and is owned by me */


> > "Invalid object name" */
> > drop view tmp_view;
> > return

> > I'm running this proc by calling it like this:


> > The issue seems to be that the proc is owned by dbo.  Any idea how to
work
> > my way around this issue?

> > Thanks,
> > Laura

 
 
 

"Invalid object name" in stored procedure

Post by Keith Kratochvi » Sat, 10 Mar 2001 07:22:04


Quote:>This is a proc which will be run by various people on the server.  That is
>why I don't preface the view name with 'dbo.'  Everyone needs to have their
>own view so that if two people try to run the proc at the same time it
won't
>cause problems.

What problems?  Shouldn't the logic be the same for everyone?
Anyway, this is not your real problem.

Quote:>The reason I'm using a view in the first place is that the from and where
>clause of the query are built on the fly in my proc (guess I simplified my
>example a little too much).

I am sorry to hear that you build your select dynamically.
I would recommend against using dynamic-sql.  You are opening yourself up to
security issues as well as potential performance problems.
Sometimes dynamic-sql is unavoidable.

Quote:>What I REALLY want to do is set a variable based on a query I build on the
>fly.  I use the process of concatinating my string and running it with
>'exec' all over the place for other things.  When I had to set a variable
>with the string, the only thing I could come up with was creating the view.
>If anyone has a better method I'd really appreciate hearing it.

I am guessing that the column within the 'where' that you are dynamically
creating is already known.
If the column name is the same, you could just tack on the variable (ColName

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

 
 
 

1. fails with "Invalid object name"

I'm attempting to run a DTS package to move both Schema and data from one
SQL box to another.  It fails with error:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo.my_table_name'.

Table "my_table_name" exists on both servers with dbo as the owner.

Has anybody seen this error?

2. Linker problems

3. DTS Export "Invalid Object Name"

4. why can sa delete os files?

5. Error #208 "Invalid Object Name"

6. SQL DMO Bulkcopy runtime error

7. Invalid object name ".....temp_tat"

8. T-SQL Debugger

9. Help with Query "Invalid Object Name"

10. Msg 207 "invalid column name"

11. "The instance name specified is invalid"

12. error: "invalid column name"

13. EDBEngineError "Invalid File Name"