How to create database dynamically

• avdhut.k

  SSCrazy

  Points: 2834

  More actions

  August 23, 2013 at 2:36 am

  #395352

  Hi,

  Following is the SP to rename the existing database and then create a new database ,

  but,I am getting the error as,

  Msg 102, Level 15, State 1, Line 8

  Incorrect syntax near 'D:'.

  Msg 132, Level 15, State 1, Line 10

  The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.

  I am not able to understand the following error.

  Please help me.

  Thanks in Advance!!

  USE master

  GO

  If Object_Id('SP_DataBaseCreation_00') Is Not Null

  Begin

  Drop Procedure SP_DataBaseCreation_00

  End

  Go

  Create Procedure SP_DataBaseCreation_00

  As

  Begin

  Declare @Sqlstr As Varchar(8000)

  Declare @Backup_DBname Varchar(200)

  Declare @Prev_QtrDt Varchar(20)

  Declare @AsonDate Varchar(20)

  Declare @DBName As Varchar(20)

  Select @Prev_QtrDt = Convert(varchar(8),LastNpaDate,112) + '_' + Convert(varchar(8),GetDate(),112),

  @AsonDate = Convert(varchar(8),AsonDate,112) + '_' + Convert(varchar(8),GetDate(),112)

  --,@DBName = NPAEXEC_DBNAME

  from SHFC_NPA..IGen_Settings

  Set @Backup_DBname = 'CoreDB_' + @Prev_QtrDt

  Select @Backup_DBname

  Select @AsonDate

  Set @Sqlstr = ''

  Set @Sqlstr = 'ALTER DATABASE CoreDB

  SET SINGLE_USER

  WITH ROLLBACK IMMEDIATE

  EXEC master..sp_renamedb ''CoreDB'',' + @Backup_DBname + '

  ALTER DATABASE ' + @Backup_DBname + '

  SET MULTI_USER'

  Print @Sqlstr

  Exec (@Sqlstr)

  Declare @DB_MPath As varchar(100)

  Declare @DB_LPath As varchar(100)

  Declare @DB_LName As varchar(100)

  Set @DB_MPath = 'N''D:\Test_DataBase\CoreDB_' + @AsonDate

  --Set @DB_LPath = 'N''D:\Test_DataBase\CoreDB_' + @AsonDate + '.ldf'

  Set @DB_LName = 'N''CoreDB_' + @AsonDate

  Set @Sqlstr = 'if db_id(''CoreDB'') is not null

  begin

  drop database CoreDB

  end

  CREATE DATABASE [CoreDB] ON PRIMARY

  ( NAME = ' + @DB_LName + ', FILENAME = ' + @DB_MPath + '.mdf , SIZE = 515072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

  LOG ON

  ( NAME = ' + @DB_LName + '_log, FILENAME = ' + @DB_MPath + '.ldf , SIZE = 568896KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

  --GO

  ALTER DATABASE [CoreDB] SET COMPATIBILITY_LEVEL = 100

  --GO

  IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))

  begin

  EXEC [CoreDB].[dbo].[sp_fulltext_database] @action = ''enable''

  end

  --GO

  ALTER DATABASE [CoreDB] SET ANSI_NULL_DEFAULT OFF

  --GO

  ALTER DATABASE [CoreDB] SET ANSI_NULLS OFF

  --GO

  ALTER DATABASE [CoreDB] SET ANSI_PADDING OFF

  --GO

  ALTER DATABASE [CoreDB] SET ANSI_WARNINGS OFF

  --GO

  ALTER DATABASE [CoreDB] SET ARITHABORT OFF

  --GO

  ALTER DATABASE [CoreDB] SET AUTO_CLOSE OFF

  --GO

  ALTER DATABASE [CoreDB] SET AUTO_CREATE_STATISTICS ON

  --GO

  ALTER DATABASE [CoreDB] SET AUTO_SHRINK OFF

  --GO

  ALTER DATABASE [CoreDB] SET AUTO_UPDATE_STATISTICS ON

  --GO

  ALTER DATABASE [CoreDB] SET CURSOR_CLOSE_ON_COMMIT OFF

  --GO

  ALTER DATABASE [CoreDB] SET CURSOR_DEFAULT GLOBAL

  --GO

  ALTER DATABASE [CoreDB] SET CONCAT_NULL_YIELDS_NULL OFF

  --GO

  ALTER DATABASE [CoreDB] SET NUMERIC_ROUNDABORT OFF

  --GO

  ALTER DATABASE [CoreDB] SET QUOTED_IDENTIFIER OFF

  --GO

  ALTER DATABASE [CoreDB] SET RECURSIVE_TRIGGERS OFF

  --GO

  ALTER DATABASE [CoreDB] SET DISABLE_BROKER

  --GO

  ALTER DATABASE [CoreDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

  --GO

  ALTER DATABASE [CoreDB] SET DATE_CORRELATION_OPTIMIZATION OFF

  --GO

  ALTER DATABASE [CoreDB] SET TRUSTWORTHY OFF

  --GO

  ALTER DATABASE [CoreDB] SET ALLOW_SNAPSHOT_ISOLATION OFF

  --GO

  ALTER DATABASE [CoreDB] SET PARAMETERIZATION SIMPLE

  --GO

  ALTER DATABASE [CoreDB] SET READ_COMMITTED_SNAPSHOT OFF

  --GO

  ALTER DATABASE [CoreDB] SET HONOR_BROKER_PRIORITY OFF

  --GO

  ALTER DATABASE [CoreDB] SET READ_WRITE

  --GO

  ALTER DATABASE [CoreDB] SET RECOVERY FULL

  --GO

  ALTER DATABASE [CoreDB] SET MULTI_USER

  --GO

  ALTER DATABASE [CoreDB] SET PAGE_VERIFY CHECKSUM

  --GO

  ALTER DATABASE [CoreDB] SET DB_CHAINING OFF'

  Print (@Sqlstr)

  Exec (@Sqlstr)

  End

• HanShi

  SSC-Dedicated

  Points: 33507

  More actions

  August 23, 2013 at 3:01 am

  #1644296

  At first glance (I haven't analysed or executed the code) it looks like you need extra double quotes around the path/filenames.

  Try changing this part:

  ...

  CREATE DATABASE [CoreDB] ON PRIMARY

  ( NAME = ' + @DB_LName + ', FILENAME = ' + @DB_MPath + '.mdf , SIZE = 515072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

  LOG ON

  ( NAME = ' + @DB_LName + '_log, FILENAME = ' + @DB_MPath + '.ldf , SIZE = 568896KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

  ...

  To this:

  ...

  CREATE DATABASE [CoreDB] ON PRIMARY

  ( NAME = ' + @DB_LName + ', FILENAME = ''' + @DB_MPath + '.mdf'' , SIZE = 515072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

  LOG ON

  ( NAME = ' + @DB_LName + '_log, FILENAME = ''' + @DB_MPath + '.ldf'' , SIZE = 568896KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

  ...

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **