Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Procedure to create procedures in master Expand / Collapse
Author
Message
Posted Wednesday, May 22, 2013 7:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 09, 2013 3:02 AM
Points: 11, Visits: 41
I'm trying to develop an "installation pack" for my servers, we are regularly adding new servers to our inventory as we host client servers. So to speed up my configuring of a new server to our standard build, I've been configuring a load of procs to apply standard settings across a range of things (maintenance, security, standard jobs etc).

Something I'm trying to do is build a procedure that will create a set of procs on the master database. There's not many, there isn't a major usage of master, it's just for 1 or 2 system wide procs. Arguably I could put this in another database and we'd all be happy, but I'd prefer them to be in master if possible.

The process I've been using is to push into a variable the command to build an object then executing the variable, something along these lines:

CREATE PROCEDURE [build].[usp_CreateGetDateView]
AS
DECLARE @EXSQL AS VARCHAR(MAX)
SET @EXSQL = '
CREATE VIEW dbo.vwGetDate
AS
SELECT GETDATE() AS Result
'
EXEC (@EXSQL)


So the above will create a proc that when executed will create a view that returns the result of GETDATE(). It means I can repeat this for multiple objects and just run the proc.

What I cannot seem to be able to do is configure a proc that once run, creates a proc in the master database. Firstly, I cannot put "CREATE PROCEDURE master.dbo.[procname]" since you cannot specify the database and I cannot put "USE master CREATE PROCEDURE dbo.[procname]" since the CREATE command needs to be the first statement in a batch (and putting GO in doesn't work either).

Does anyone have any suggestions as to how I can do this or is it fundamentally not possible?


I accept that if I simply had a script somewhere with all of this in, I could simply run it and if that's the best option then that's what I'll do, but I'm interested in whether there is a way to get around this restriction?
Post #1455500
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse