Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

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


Group: General Forum Members
Last Login: Tuesday, January 5, 2016 6:41 AM
Points: 11, Visits: 51
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]
CREATE VIEW dbo.vwGetDate

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