March 26, 2009 at 10:52 am
Newbie question.
I want to create a procedure, but don't want its content to run when I create it.
In SQL Server 2005 Management Studio Express, I highlight Stored Procedures and press the New Query button. I paste in my query code and press Execute.
The following creates the procedure I want, but it also runs the DBCC SHRINKFILE command:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE .[My_Procedure]
AS
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Set the following number to the desired size in MB of the Log file.
DBCC SHRINKFILE(MyDatabase_log, 40)
How can I create this procedure without running it? Also, if my code contains unnecessary stuff or if there is a better or easier way to create a query in Studio Express, I am all ears (eyes).Thank you.
March 26, 2009 at 10:59 am
a GO statement ends your procedure, and runs the next command; you've pasted commands INCLUDING the "GO" which is not what you want.
this is a better format:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE .[My_Procedure]
AS
BEGIN --explcitly show code for the proc is between a begin and end
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Set the following number to the desired size in MB of the Log file.
DBCC SHRINKFILE(MyDatabase_log, 40)
END --PROC
GO
Lowell
March 26, 2009 at 11:17 am
That works beautifully. Thank you for the help.
March 26, 2009 at 11:57 am
That's a very clear and good explanation Lowell. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply