How to tailor CREATE DATABASE for an arbitrary instance

Suppose the following:

As part of a product install (using InstallShield)...


I create a SQL Server Express instance (say "X") via a silent install

I supply a script to create a database in instance X.

The idea, of course, is to have a fully automated install. But there's one problem I can't quite see how to work around:

- the CREATE DATABASE statement needs the name of a file to contain the database, and that file needs to be in a folder that belongs to the instance (e.g. Microsoft SQL Server\MSSQL.1, Microsoft SQL Server\MSSQL.2, etc).

Is there a syntactic variant that allows me to avoid this problem?

Thanks
Josh

hi,

don't know if this can be a solution to your problem, but in the CREATE DATABASE syntax only the database name is mandatory.. http://msdn2.microsoft.com/en-us/library/ms176061.aspx

if you only specify

CREATE DATABASE myDb;

the "myDb" name will be used for physical files naming, resulting in a "myDb.Mdf" and "myDb_log.Ldf" files, stored in the folder hosting all other databases..

the new "myDb" will inherits all settings applied to "model" database, both regarding database settings as physical settings as well (size, growth, etc)...

but you loose control over the physical and logical name specification, and, of course, you can this way no longer specify size, growth, max size, ...

regards

|||

Andrea:

Between then and now, I found what I needed in one of your posts from yesterday.

With very minor adaptation, I arrived at the following (which worked like a charm):

DECLARE @.itemp VARCHAR(255);
DECLARE @.RegKey VARCHAR(255);
DECLARE @.instance VARCHAR(255);
DECLARE @.subfolder VARCHAR(255);
DECLARE @.rootfolder VARCHAR(255);
DECLARE @.DBFileName VARCHAR(255);
DECLARE @.DBLogName VARCHAR(255);
DECLARE @.CreateDB VARCHAR(MAX);

SELECT @.itemp = CONVERT(varchar, SERVERPROPERTY('InstanceName'));
IF @.itemp IS NULL
SET @.instance = 'MSSQLServer';
ELSE
SET @.instance = @.itemp;

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
@.value_name = @.instance,
@.value = @.subfolder OUTPUT;

SET @.RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @.subfolder + '\Setup';

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key = @.RegKey,
@.value_name = 'SQLDataRoot',
@.value = @.rootfolder OUTPUT;

SET @.DBFileName = @.rootfolder + '\DATA\' + 'MyDatabase.mdf';
SET @.DBLogName = @.rootfolder + '\DATA\' + 'MyDatabase_log.mdf';

-- Create the database

SET @.CreateDB = 'CREATE DATABASE [OTHGMD] ON PRIMARY
(NAME = ''MYDATABASE'', FILENAME = ''' +
@.DBFileName + ''', SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) ' +
'LOG ON (NAME = N''MYDATABASE_log'', FILENAME = ''' +
@.DBLogName + ''', SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) ' +
'COLLATE SQL_Latin1_General_CP1_CI_AS;'

EXEC (@.CreateDB);
GO

Thanks Very Much
Josh

p.s. A pox on the SQL Syntax committee for making us have to do this. Can you think of a good reason why the file name can't be a variable?

|||

hi,

usually referenced objects are to be considered constants or literals... only parameters can (and should be) variable

as you surely know, you are executing dynamic SQL, composed on the fly and executed... SQL Server takes even care of that as it can, somehow and sometime, avoid recompilation of some dynamic statements, but that' all... just a "limitation" of current implementation of DBMS engines, and, as per the CREATE DATABASE statement, this regards SQL Server "only" as each different vendor provides it's own "syntax" to generate new catalogues and database... it's not that standard... AFAIK the ANSI standard starts with the SCHEMA, and, for the ANSI commetee, there's no part for "variables", as all must be resolved in "literals"... it is the compilation phase, in SQL Server as in other engines, that pepare the final statement, replacing variables with literals (where allowed by the host language/tool/engine) and finally passes the "final" result as output to the compiling phase.. tokenizer, algebrizer and other technologies come then into play.. but this is another story

regards