dropping and creating stored procedure

[Geetha]

I have a stored procedure which gives me an error message as Error 208:Invalid
object name. If I drop and recreate the same stored procedure it does not
give me the error any more...Why does dropping and recreating a procedure
should fix the problem?????...I am confused

Here is the stored procedure:

if exists (select * from sysobjects where id = object_id('<stored_procedure>')
and sysstat & 0xf = 4)
drop procedure <stored_procedure>
GO

CREATE PROCEDURE dbo.<stored_procedure>
…
…

DECLARE
….
….

select @counter = count(*)
from dbo.sysobjects A
where A.name = '<table_name>' and
A.uid = user_id()

if @counter > 0
drop table .<table_name>


declare …


select *
into .<table_name>
from dbo.<existing_table> A
where …

…
…

select @counter = count(*)
from dbo.sysobjects A
where A.name = <table_name> and
A.uid = user_id()

if @counter > 0
drop table <table_name>


GO

[Geetha]

The exact error message is : Msg 208 "Invalidobject name .<table_name>"

The "<table_name>" is the one that is dropped and created and dropped in
the stored procedure

"Geetha" <gelangov@hotmail.com> wrote:
>
>I have a stored procedure which gives me an error message as Error 208:Invalid
>object name. If I drop and recreate the same stored procedure it does not
>give me the error any more...Why does dropping and recreating a procedure
>should fix the problem?????...I am confused
>
>Here is the stored procedure:
>
>if exists (select * from sysobjects where id = object_id('<stored_procedure>')
>and sysstat & 0xf = 4)
> drop procedure <stored_procedure>
>GO
>
>CREATE PROCEDURE dbo.<stored_procedure>
>…
>…
>
>DECLARE
>….
>….
>
> select @counter = count(*)
> from dbo.sysobjects A
> where A.name = '<table_name>' and
> A.uid = user_id()
>
> if @counter > 0
> drop table .<table_name>
>
>
> declare …
>
>
>select *
> into .<table_name>
> from dbo.<existing_table> A
> where …
>
>…
>…
>
> select @counter = count(*)
> from dbo.sysobjects A
> where A.name = <table_name> and
> A.uid = user_id()
>
> if @counter > 0
> drop table <table_name>
>
>
>GO
>

[DaveSatz]

are you using SQL Server 6.5 ? or 7.0+ ?
--

HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
-----------------------------------------------------------------

"Geetha" <gelangov@hotmail.com> wrote in message
news:3baf4ef0$1@news.devx.com...
>
> I have a stored procedure which gives me an error message as Error
208:Invalid
> object name. If I drop and recreate the same stored procedure it does not
> give me the error any more...Why does dropping and recreating a procedure
> should fix the problem?????...I am confused
>
> Here is the stored procedure:
>
> if exists (select * from sysobjects where id =
object_id('<stored_procedure>')
> and sysstat & 0xf = 4)
> drop procedure <stored_procedure>
> GO
>
> CREATE PROCEDURE dbo.<stored_procedure>
> .
> .
>
> DECLARE
> ..
> ..
>
> select @counter = count(*)
> from dbo.sysobjects A
> where A.name = '<table_name>' and
> A.uid = user_id()
>
> if @counter > 0
> drop table .<table_name>
>
>
> declare .
>
>
> select *
> into .<table_name>
> from dbo.<existing_table> A
> where .
>
> .
> .
>
> select @counter = count(*)
> from dbo.sysobjects A
> where A.name = <table_name> and
> A.uid = user_id()
>
> if @counter > 0
> drop table <table_name>
>
>
> GO
>

[Geetha]

I am using sqlserver 6.5 and the error message is:

Msg 208, Level 16, State 1
Invalid object name '.<table_name>'.


"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
>are you using SQL Server 6.5 ? or 7.0+ ?
>--
>
>HTH,
>David Satz
>Principal Software Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS
}
>(Please reply to group only - emails answered rarely)
>This posting is provided "AS IS" with no warranties, and confers no rights.
>You assume all risk for your use.
>-----------------------------------------------------------------
>
>"Geetha" <gelangov@hotmail.com> wrote in message
>news:3baf4ef0$1@news.devx.com...
>>
>> I have a stored procedure which gives me an error message as Error
>208:Invalid
>> object name. If I drop and recreate the same stored procedure it does
not
>> give me the error any more...Why does dropping and recreating a procedure
>> should fix the problem?????...I am confused
>>
>> Here is the stored procedure:
>>
>> if exists (select * from sysobjects where id =
>object_id('<stored_procedure>')
>> and sysstat & 0xf = 4)
>> drop procedure <stored_procedure>
>> GO
>>
>> CREATE PROCEDURE dbo.<stored_procedure>
>> .
>> .
>>
>> DECLARE
>> ..
>> ..
>>
>> select @counter = count(*)
>> from dbo.sysobjects A
>> where A.name = '<table_name>' and
>> A.uid = user_id()
>>
>> if @counter > 0
>> drop table .<table_name>
>>
>>
>> declare .
>>
>>
>> select *
>> into .<table_name>
>> from dbo.<existing_table> A
>> where .
>>
>> .
>> .
>>
>> select @counter = count(*)
>> from dbo.sysobjects A
>> where A.name = <table_name> and
>> A.uid = user_id()
>>
>> if @counter > 0
>> drop table <table_name>
>>
>>
>> GO
>>
>
>

[DaveSatz]

I asked since 7.0 and later have deferred name resolution so that you would
not get this error.

not sure from your code, but if you are referring to the table you create in
the SELECT/INTO in your DROP TABLE statement, the table must exist or you
will get that error. That w/b consistent with the code working every other
time.

"Geetha" <gelangov@hotmail.com> wrote in message
news:3baf7538$1@news.devx.com...
>
> I am using sqlserver 6.5 and the error message is:
>
> Msg 208, Level 16, State 1
> Invalid object name '.<table_name>'.
>
>
> "DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
> >are you using SQL Server 6.5 ? or 7.0+ ?
> >--
> >
> >HTH,
> >David Satz
> >Principal Software Engineer
> >Hyperion Solutions
> >{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
VSS
> }
> >(Please reply to group only - emails answered rarely)
> >This posting is provided "AS IS" with no warranties, and confers no
rights.
> >You assume all risk for your use.
> >-----------------------------------------------------------------
> >
> >"Geetha" <gelangov@hotmail.com> wrote in message
> >news:3baf4ef0$1@news.devx.com...
> >>
> >> I have a stored procedure which gives me an error message as Error
> >208:Invalid
> >> object name. If I drop and recreate the same stored procedure it does
> not
> >> give me the error any more...Why does dropping and recreating a
procedure
> >> should fix the problem?????...I am confused
> >>
> >> Here is the stored procedure:
> >>
> >> if exists (select * from sysobjects where id =
> >object_id('<stored_procedure>')
> >> and sysstat & 0xf = 4)
> >> drop procedure <stored_procedure>
> >> GO
> >>
> >> CREATE PROCEDURE dbo.<stored_procedure>
> >> .
> >> .
> >>
> >> DECLARE
> >> ..
> >> ..
> >>
> >> select @counter = count(*)
> >> from dbo.sysobjects A
> >> where A.name = '<table_name>' and
> >> A.uid = user_id()
> >>
> >> if @counter > 0
> >> drop table .<table_name>
> >>
> >>
> >> declare .
> >>
> >>
> >> select *
> >> into .<table_name>
> >> from dbo.<existing_table> A
> >> where .
> >>
> >> .
> >> .
> >>
> >> select @counter = count(*)
> >> from dbo.sysobjects A
> >> where A.name = <table_name> and
> >> A.uid = user_id()
> >>
> >> if @counter > 0
> >> drop table <table_name>
> >>
> >>
> >> GO
> >>
> >
> >
>