Disappearing Linked Server Stored Procedures

When I create a stored procedure like the one below and save it,
everything seems fine. But when I close Enterprise Manager and
re-open it, the stored procedure is gone.

Any ideas?

SET ANSI_DEFAULTS ON
GO
CREATE PROCEDURE [dbo].[sp_Test] AS
SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT V.VENDOR_ID,
V.VENDOR_NAME FROM VENDOR V')
GO

Ted"Ted Calhoon" <tedcalhoon@.hotmail.com> wrote in message
news:fc57c4d8.0312121455.1e0677d8@.posting.google.c om...
> When I create a stored procedure like the one below and save it,
> everything seems fine. But when I close Enterprise Manager and
> re-open it, the stored procedure is gone.
> Any ideas?
>
> SET ANSI_DEFAULTS ON
> GO
> CREATE PROCEDURE [dbo].[sp_Test] AS
> SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT V.VENDOR_ID,
> V.VENDOR_NAME FROM VENDOR V')
> GO
>
> Ted

If you put only the CREATE PROCEDURE statement into the create procedure
dialogue, it will work:

CREATE PROCEDURE [dbo].[sp_Test] AS
SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT V.VENDOR_ID,
V.VENDOR_NAME FROM VENDOR V')

Presumably, this is a 'feature' in EM, which seems to have a number of
quirks. If you need full control over the CREATE PROC statement, you should
execute it in Query Analyzer instead, which is probably a better practice in
general.

Also, don't use sp_ as a procedure name prefix, that's reserved for system
stored procedures.

Simon|||Thanks, Simon. I will take your advice and:

1. Use Query Analyzer to create stored procedures
2. Avoid using sp_ when naming my stored procedures

Ted