Disappearing sp???

[olemma]

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 ....
Any ideas???

 

[tlbroadbent]

Does the "loop" or delay occur in the creation of the SP or in the execution? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[olemma]

It happens while execution in the Query Anaylizer...
exec xyz

 

[tlbroadbent]

Exactly what do you mean by disappearing sp? What do you mean "it is still there after I cancel it?" Still where? In QA or in the Process Info under Current Activity in EM?

You indicate it "disappears" after closing and reopening EM. Do you mean that the connection no longer shows in EM Current Activity after you reopen EM? If that is the case, do the following. After you kill the process in QA, right click Current Activity in EM and select refresh. Reopen the Process Info. The connection should no longer appear if it has terminated.

You also indicated that you want some help with your SP. If this is so, we need more info.

Are you sure it is looping or is it simply running too long?

Have you run a trace on the local and remote servers to see what is happening? Have you used Performance Monitor to check processor usage, cache hit ratio, etc?

If we are to help you debug the SP we need to see the code. You only posted the first line. That is not helpful. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.