Disappearing Linked Server Stored Procedures

[Ted Calhoon]

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

[Simon Hayes]

"Ted Calhoon" <te********@hotmail.com> wrote in message
news:fc**************************@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

[Ted Calhoon]

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