How to create SQL database using EXEC() statement

Question

Need to create a folders and databases inside them. Since there is no ability to create databases using variable name and path I'm trying to use EXEC() statement. But it fails with error 'Incorrect syntax near 'LOG''. What I'm doing wrong?

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @DBName1 sysname
DECLARE @DataPath1 nvarchar(500),
DECLARE @LogPath1 nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
DECLARE @version sysname
declare @sql nvarchar(500)
declare @fulldbpath varchar(500)
declare @fulllogpath varchar(500)

-- 2 - Initialize variables
SET @version= 
      CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 4)
        WHEN '9.00' THEN '2005'
        WHEN '10.0' THEN '2008'
        WHEN '10.5' THEN '2008R2'
        WHEN '11.0' THEN '2012'
        WHEN '12.0' THEN '2014'
    END
SET @DBName1 = 'DB1'
SET @DataPath1 = 'G:\StorageGroup1\SQL' + @version+ '\'+ @DBName1 
SET @LogPath1 = 'F:\StorageGroup1\SQL\' + @version+ '\'+ @DBName1
SET @fulldbpath=@DataPath1+'\'+@DBName1+'.mdf'
SET @fulllogpath=@LogPath1+'\'+@DBName1+'_log.ldf'

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath1

-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName1)
EXEC master.dbo.xp_create_subdir @DataPath1

-- 5 - Remove all records from @DirTree
DELETE FROM @DirTree

-- 6 - @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath1

-- 7 - Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName1)
EXEC master.dbo.xp_create_subdir @LogPath1


--8 -Create database
  set @sql = 'CREATE DATABASE [@DBName1]
    ON (NAME = [@DBName1],
        FILENAME = [@fulldbpath]
    LOG ON (NAME = [@DBName1]
        FILENAME = [@fulllogpath]
            )'                      
    EXEC(@sql)          


SET NOCOUNT OFF
GO

PRINT out the @sql command that is failing and post that back here. — gvee
– gvee, Commented Aug 27, 2014 at 13:11
Here it is: CREATE DATABASE [@DBName1] ON (NAME = [@DBName1], FILENAME = [@fulldbpath] LOG ON (NAME = [@DBName1] FILENAME = [@fulllogpath] ) — user1980915
– user1980915, Commented Aug 27, 2014 at 13:18
See the problem? ;-) what happens when you try to execute that exact statement? — gvee
– gvee, Commented Aug 27, 2014 at 13:26

SchmitzIT · Accepted Answer · 2014-08-27 13:25:46Z

1

Your CREATE DATABASE statement is:

CREATE DATABASE [@DBName1]
    ON (NAME = [@DBName1],
        FILENAME = [@fulldbpath]
    LOG ON (NAME = [@DBName1]
        FILENAME = [@fulllogpath]
            )

You're missing a closing parenthesis after [@fulldbpath]:

set @SQL = ' CREATE DATABASE QUOTENAME(' + @DBName1 + ')
        ON (NAME = QUOTENAME(' + @DBName1 + '_data),
            FILENAME = QUOTENAME(' + @fulldbpath + '))
        LOG ON (NAME = QUOTENAME(' + @DBName1 + '_log)
            FILENAME = QUOTENAME(' + @fulllogpath + '))'

Also, remove the comma after the declaration of @DataPath1.

edited Aug 27, 2014 at 13:25

answered Aug 27, 2014 at 13:19

SchmitzIT

9,74912 gold badges71 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jpw Over a year ago

I don't think this will work as the variables never gets replaced... You should try it.

SchmitzIT Over a year ago

@jpw Well spotted, I didn't consider that. I edited the answer :)

SchmitzIT Over a year ago

@jpw Updated it again :)

jpw · Accepted Answer · 2014-08-27 13:24:18Z

This should work (it did when I tried):

USE MASTER;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @DBName1 sysname
DECLARE @DataPath1 nvarchar(500)
DECLARE @LogPath1 nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
DECLARE @version sysname
declare @sql nvarchar(500)
declare @fulldbpath varchar(500)
declare @fulllogpath varchar(500)

-- 2 - Initialize variables
SET @version= 
      CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 4)
        WHEN '9.00' THEN '2005'
        WHEN '10.0' THEN '2008'
        WHEN '10.5' THEN '2008R2'
        WHEN '11.0' THEN '2012'
        WHEN '12.0' THEN '2014'
    END
SET @DBName1 = 'DB1'
SET @DataPath1 = 'G:\StorageGroup1\SQL' + @version+ '\'+ @DBName1 
SET @LogPath1 = 'F:\StorageGroup1\SQL\' + @version+ '\'+ @DBName1    
SET @fulldbpath=@DataPath1+'\'+@DBName1+'.mdf'
SET @fulllogpath=@LogPath1+'\'+@DBName1+'_log.ldf'

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath1

-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName1)
EXEC master.dbo.xp_create_subdir @DataPath1

-- 5 - Remove all records from @DirTree
DELETE FROM @DirTree

-- 6 - @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath1

-- 7 - Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName1)
EXEC master.dbo.xp_create_subdir @LogPath1


--8 -Create database
select @fulldbpath
select @fulllogpath
set @sql = 'CREATE DATABASE ['+@DBName1+']
    ON (NAME = ['+@DBName1+'],
        FILENAME = ['+@fulldbpath+'])
    LOG ON (NAME = ['+@DBName1+'_log],
        FILENAME = ['+@fulllogpath+']
            )'                      
  EXEC(@sql)          

    print @sql


SET NOCOUNT OFF
GO

There was a surplus comma in the declaration part, and some commas missing in the @sql. Also, the log and database had the same name - the need to differ (I added _logas a suffix for the log), and the variables needed to be escaped else they won't get expanded.

Collectives™ on Stack Overflow

How to create SQL database using EXEC() statement

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related