Dynamically Pass the Location and File name for the Create Database script

Question

I am trying to add the new file group and file into existing database.

My script:

DECLARE @AlterDB NVarchar(4000),
        @FGName NVarchar(30) = 'Partition_DW2_',
        @FileLoc NVarchar(50) ='D:\Data\',
        @CurrentYR NVarchar(10) ='2014' ,
        @FullName Nvarchar(max) 
 Set @FullName = @FileLoc+@FGName+@CurrentYR+'.ndf'
        Print @fullName
et @AlterDB = 'ALTER DATABASE Partition_DWJ2
                        ADD FILE 
                        (   NAME =  '+@FGName+@CurrentYR+',FILENAME ='+@FullName +',SIZE = 2048KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB
                        )TO FILEGROUP '+@FGName+@CurrentYR

            EXECUTE SP_Executesql @AlterDB ,N'@FullName NVarchar(max)',@FullName=@FullName
            Print @ALterDB

This gives the error 'Incorrect syntax near 'D:'.'

What have I done wrong?

343 · Accepted Answer · 2013-07-30 15:59:57Z

4

Well, assuming that you are actually using Set @AlterDB instead of et @AlterDB on your script, you need to enclose the filename on single quotes:

DECLARE @AlterDB NVarchar(4000),
        @FGName NVarchar(30) = 'Partition_DW2_',
        @FileLoc NVarchar(50) ='D:\Data\',
        @CurrentYR NVarchar(10) ='2014' ,
        @FullName Nvarchar(max) 
 Set @FullName = @FileLoc+@FGName+@CurrentYR+'.ndf'
        Print @fullName
 Set @AlterDB = 'ALTER DATABASE Partition_DWJ2
                        ADD FILE 
                        (   NAME =  '+@FGName+@CurrentYR+',FILENAME ='+CHAR(39)+@FullName+CHAR(39)+',SIZE = 2048KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB
                        )TO FILEGROUP '+@FGName+@CurrentYR

            EXECUTE SP_Executesql @AlterDB ,N'@FullName NVarchar(max)',@FullName=@FullName
            Print @ALterDB

edited Jul 30, 2013 at 15:59

343

4052 gold badges9 silver badges22 bronze badges

answered Jul 30, 2013 at 15:35

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

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

2 Comments

John Riselvato Over a year ago

why are you adding Partition_DWJetblue2_?

Lamak Over a year ago

@JohnRiselvato Because that was the name op was using when I answered

Milen · Accepted Answer · 2013-07-30 15:23:05Z

0

Try adding another backslash to your @FileLoc NVarchar(50) = 'D:\\Data\\'

answered Jul 30, 2013 at 15:23

Milen

8,8977 gold badges47 silver badges59 bronze badges

Collectives™ on Stack Overflow

Dynamically Pass the Location and File name for the Create Database script

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related