SQL Server 2008 - Saving Modified Stored Procedure

[Hack]

I have a ton of stored procedures that I developed when we had SQL Server 2000.

Today, I had to make a change to one of them. We are now running SQL Server 2008 and I'm using Management Studio, not Enterprise Manager.

In Management Studio, I right clicked on the stored procedure, clicked "Modify", and a whole bunch of changes, but it doesn't want to let me save them back to the stored procedure. It wants to save what I did to a disk file as SQLQuery1.sql

How do simply resave the stored procedure?

[GaryMazzone]

Once the sp is in the query windows (It will have ALTER PROCEDURE at the top). Make the changes in this window. Once all changes made either press Execute button in the tool bar or Press F5 key to do it. This will write the changes back to the DB

[CVMichael]

Can you create a procedure ? (and then modify it ?)

For example, can you run this ?

IF OBJECT_ID('dbo.spx_test') IS NOT NULL
	DROP PROCEDURE dbo.spx_test
GO

CREATE PROCEDURE dbo.spx_test
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT NEWID() AS New_ID
END
GO

ALTER PROCEDURE [dbo].[spx_test]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT NEWID() AS New_ID
END
GO

DROP PROCEDURE dbo.spx_test
GO

I'm thinking that you don't have the correct permissions. When you backup (from 2000) and recover (to 2008), you probably lost the "connection" between the users that are on database level and the users that on SQL Server level.

You probably have to remove the users at the database level, and re-add them.

[Hack]

Once the sp is in the query windows (It will have ALTER PROCEDURE at the top). Make the changes in this window. Once all changes made either press Execute button in the tool bar or Press F5 key to do it. This will write the changes back to the DB

My Hero...thanks

[CVMichael]

LOL, I had no idea that Enterprise Manager in SQL Server 2000 does not change to "alter procedure". Even though I worked with SQL Server 2000 databases before, I always connected to the database through the Management Studio interface from 2005.

[techgnome]

as a matter of habbit, I never alter procedure... always drop and create...

-tg