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