"Create Database"-SQL with name from variable fails

Question

I used an existing database to generate a "CREATE"-Script via the MS SQL Server Management Tool. Then i replaced the string literals for the DB Name with variables. However, when i execute the script, it keeps saying "wrong syntax near @DBFullName" (the first use in 'NAME = @DBFullName ...'). I have no idea what the issue is, other than the possibility, that the use of variables is forbidden here.

DECLARE @DBNAME nvarchar(MAX);
SET @DBNAME = 'MyDataBase'

DECLARE @DBFullName text;
SET @DBFullName = 'MySuperDataBase';

DECLARE @DBFileName text;
SET @DBFileName = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPMENT\MSSQL\DATA\\' + @DBName + '.mdf';
DECLARE @DBLogName text;
SET @DBLogName = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPMENT\MSSQL\DATA\\' + @DBName + '.ldf';

CREATE DATABASE [@DBNAME] ON  PRIMARY 
( NAME = @DBFullName + '_Data', FILENAME = @DBFileName , SIZE = 30720KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = @DBFullName + '_Log', FILENAME = @DBLogName , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO

Please see the following for a method on doing this stackoverflow.com/questions/727788/… — 3dd
– 3dd, Commented Mar 16, 2015 at 9:05
You'll have to use Dynamic SQL for that. Furthermore, I believe text isn't allowed as a variable data type. — NickyvV
– NickyvV, Commented Mar 16, 2015 at 9:05

Charlieface · Accepted Answer · 2022-01-09 02:10:41Z

2

You have to use dynamic SQL. Also, you need to replace text with nvarchar(max) otherwise text concatenation won't work.

Here is full script for you:

DECLARE @DBNAME sysname;
SET @DBNAME = 'MyDataBase'

DECLARE @DBFullName sysname;
SET @DBFullName = 'MySuperDataBase';

DECLARE @DBFileName nvarchar(max);
SET @DBFileName = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPMENT\MSSQL\DATA\' + @DBName + '.mdf';
DECLARE @DBLogName nvarchar(max);
SET @DBLogName = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPMENT\MSSQL\DATA\' + @DBName + '.ldf';

DECLARE @SQL nvarchar(max);
SET @SQL = N'
  CREATE DATABASE ' + QUOTENAME(@DBNAME) + ' ON PRIMARY
    (NAME = ' + QUOTENAME(@DBFullName + '_Data') + ',
    FILENAME = ' + QUOTENAME(@DBFileName, '''') + ',
    SIZE = 30720KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%)
   LOG ON (
     NAME = ' + QUOTENAME(@DBFullName + '_Log') + ',
     FILENAME = ' + QUOTENAME(@DBLogName, '''') + ',
     SIZE = 2048KB,
     MAXSIZE = UNLIMITED,
     FILEGROWTH = 10%)'

PRINT @SQL

EXEC (@SQL)
GO

Works on my machine! :)

edited Jan 9, 2022 at 2:10

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

answered Mar 16, 2015 at 9:08

Kaspars Ozols

7,0371 gold badge22 silver badges33 bronze badges

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

1 Comment

Alexander Mills Over a year ago

Ah well :\ this means i'm probably better of with implementing the creation in c#, since my script is a bit longer than only the "Create Database"-statement. It's a giant script, generated by the SQL Management Tool, and i only wanted to add the Name of the DB to control that bit throughout the whole script. If i have to do it with string concatenation on every usage of the DB name, there is little to no improvement ;-) But thank you

Collectives™ on Stack Overflow

"Create Database"-SQL with name from variable fails

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related