dropping and creating stored procedure


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: dropping and creating stored procedure

  1. #1
    Geetha Guest

    dropping and creating stored procedure


    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


  2. #2
    Geetha Guest

    Re: dropping and creating stored procedure


    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
    >



  3. #3
    DaveSatz Guest

    Re: dropping and creating stored procedure

    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
    >




  4. #4
    Geetha Guest

    Re: dropping and creating stored procedure


    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
    >>

    >
    >



  5. #5
    DaveSatz Guest

    Re: dropping and creating stored procedure

    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
    > >>

    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center