I would love to have sqlcmd style variable substitution support for any of the scripts executed by RoundhousE. For example:

I'd like to be able to run:

RoundhousE -v DatabaseName:MyDatabase

and a script in the up folder specified as:

ALTER DATABASE $(DatabaseName) ...

would be executed as:

ALTER DATABASE MyDatabase ...

Let's talk about this - we already allow this kind of thing to happen. It's done in two different steps.

You define your items like that in templates and use UppercuT (http://projectuppercut.org) to build and replace the tokens with real values.

Then RH executes the scripts. Different databases at different environments?

Make it an environment script - 0001_something.ENV.sql.template (the .ENV. is necessary to tell RH it's an environment specific script) will get replaced with every environment defined by UppercuT settings. So if you have a DEV.setting, TEST.setting, QA.setting and a PROD.setting, you will have a DEV.0001_something.ENV.sql, TEST.0001_something.ENV.sql, QA.0001_something.ENV.sql, and a PROD.0001_something.ENV.sql to give to RH for running. RH will only run the one for the enviroment it is in.

I will entertain the idea of what you propose - if you can provide me with a good use case. And alter database is not a good one, only because names of databases shouldn't be in the scripts themselves (as a matter of good practices).

Thanks for the reply and consideration. I haven't looked much at UppercuT, but that might be the right tool for the job.

The actual use case is this:

I'd like to be able to parameterize the names and locations of the DB's filegroups.

For example, in production, I'd like to use separate storage media for the different filegroups for performance reasons. (DB log on one disk, Data on another, Filestream on a third disk, etc.) In a development environment, they can all be in the same location.

So, I'd like to have a script that creates/modifies the filegroups and moves them to a location specified somewhere outside the script itself.

If there is a better way to accomplish this, then I'd love to hear it.

Thanks again, RH is a great tool.

That explanation makes sense to have an alter database stuff in there. We added something similar for making those settings at create time for a database (createdatabasecustomscript or something like that) as something you could pass into RH. But defining a script itself and having it run would also be a pretty good idea - you'd get better formatting, and the alter stuff would be right there with the rest of the database stuff. Hmmmmm.....

I'm going to accept this as a low priority - it makes sense that this tool should also be able to handle this as well.

In the meantime, check out uppercut (uc) - it drives config builder, sql builder, deploy builder, and documentation builder from a set of .settings files. One place to talk about a setting per environment is really nice and one of the killer features of uc.

And thanks on the compliments! It solves a need for us, and I believe it solves a need for quite a few others as well!

This sounds like the same topic...

Having the database name available to the script would be quite helpful in deployment senarios. For example ${dbname} as below:

CREATE FUNCTION [dbo].[Split](@argument nvarchar, @delimiter nchar) RETURNS TABLE ( [id] [int], [value] nvarchar NULL ) WITH EXECUTE AS CALLER AS EXTERNAL NAME [${dbname}].[${dbname}.UserDefinedFunctions].[Split]

Another example would be to customize the deployment of the various filegroups based on the available hard-drives.

CREATE DATABASE [${dbname}] ON PRIMARY ( NAME = N'${dbname}', FILENAME = N'${dbpath}\${dbname}.mdf' , SIZE = 12288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), ( NAME = N'${dbname}_FG2', FILENAME = '${fg2Path}\${dbname}_F2.ndf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'${dbname}_log', FILENAME = '${logPath}\${dbname}.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO

Token replacement is fixed in revision 299. It works like this: Any value that is in the configuration can be tokenized in the scripts.

ALTER DATABASE {{DatabaseName}} will be replaced as ALTER DATABASE Bob when the database name is Bob. This is not case sensitive.