Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Posts
    4

    Unanswered: Disappering sp???

    Does anyone know why a sp would disappear after loging out & in of the Enterprise Manager in SQL Server 2000? When I execute the sp from the Query Analizer it seems to loop but it is still there after I cancel it. If I run just the code in the Query Anaylizer, it runs fine. Due that it involves tables in 2 sql servers, to create the sp I run :

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    SET ANSI_DEFAULTS ON
    set XACT_ABORT on
    GO
    Create procedure xyz as ....

    The error reported in the log is :
    Error: 15457, Severity: 0, State: 1

    It's weird how after I close EM and go back again, the sp is not in the sp list anymore.

    Any ideas??? PLS HELP!

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    The SPs displayed in EM are cached. If the SP is dropped or created e.g. from a script then the current state will not be displayed in EM. If you refresh the list by right clicking on Stored Procedures then you will get the correct state at that moment - this has the same effect as logging out and logging in.
    If an SP is dropped or changes ID the EM will give an error if you try to display it.

    Try select name from sysobjects where xtype = 'P' to get the list.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •