Reputation: 405
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?
Upvotes: 1
Views: 1841
Reputation: 70678
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
Upvotes: 4
Reputation: 8877
Try adding another backslash to your @FileLoc NVarchar(50) = 'D:\\Data\\'
Upvotes: 0