Possible to Create A DB using a Variable for MDF file?

• Dave -310292

  SSC-Addicted

  Points: 440

  More actions

  June 29, 2006 at 3:56 pm

  #60919

  Hello, I am devoping a program to create a database automatically.  Ideally I would like to pass the location of the MDF file using a variable.  However when I try this in Query Analyzer it error out.  Any ideas?

   

  declare @file varchar (30)

  set @file ="N'c:\dave\davetest_data.mdf'"

  CREATE DATABASE [davetest]  ON

  (NAME = N'davetest_data', FILENAME = @file , size=100 ) LOG ON (NAME = N'davetest_log', FILENAME = N'C:\dave\davetest_log.ldf' , SIZE = 1)

  GO

• Michael Grivas

  Grasshopper

  Points: 20

  More actions

  June 30, 2006 at 3:15 am

  #646909

  Hi Dave,

  we are in the same situation, for that reason we wrote a stored prcedure with all the necessary parameter. The sp should be create on in master db first.

  that works fine.

   

  ======================================================================

  PRINT 'DELETING EXISTING PROCEDURES "sdsp_createdb"'

  GO

  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sdsp_createdb]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

  DROP PROCEDURE [dbo].[sdsp_createdb]

  GO

  PRINT 'CREATING EXISTING PROCEDURES "sdsp_createdb"'

  GO

  create procedure sdsp_createdb

  @dbname nvarchar(250),  -- DB_NAME = SD350REP_X

  @data_name nvarchar(250),  -- DATA_NAME = SD350REP_X

  @data_filename nvarchar(250),  -- DATA_FILENAME = E:\SQL\Data\SD350REP_X.mdf

  @data_size nvarchar(10),  -- DATA_SIZE = 10

  @log_name nvarchar(250),  -- LOG_NAME = SD350REP_X_log

  @log_filename nvarchar(250),  -- LOG_FILENAME = E:\SQL\Data\SD350REP_X.ldf

  @log_size nvarchar(10)   -- LOG_SIZE = 5

  as

  set nocount on

   

  declare @stmt nvarchar(1500)

   

  select @stmt = 'if exists ( select name from master.dbo.sysdatabases where name = N''' + @dbname + ''' ) drop database [' + @dbname + ']'

  execute sp_executesql @stmt

  -- select @stmt

   

  select @stmt =  'create database [' +  @dbname + '] on (name = N''' + @data_filename + ''', filename = N''' + @data_filename +

    ''', size = ' + @data_size + ', filegrowth = 20) log on (name = N''' + @log_name + ''', filename = N''' + @log_filename +

    ''', size = ' + @log_size + ', filegrowth = 10)'

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''autoclose'', N''true'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''bulkcopy'', N''true'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''trunc. log'', N''true'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''torn page detection'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''read only'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''dbo use'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''single'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''autoshrink'', N''true'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''ANSI null default'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''recursive triggers'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''ANSI nulls'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''concat null yields null'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''cursor close on commit'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''default to local cursor'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''quoted identifier'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''ANSI warnings'', N''false'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''auto create statistics'', N''true'''

  execute sp_executesql @stmt

  --select @stmt

  select @stmt = 'exec sp_dboption N''' + @dbname + ''', N''auto update statistics'', N''true'''

  execute sp_executesql @stmt

  --select @stmt

  ======================================================================