Renaming logical names with variables

user_10284145 0
Viewed 149

I'm trying to rename files logical names with variable. The problem is not the query in variable @ndfnameold, because if I put the simple string, i'll get same error.

DECLARE @ndfnameold Nvarchar(MAX)
SET @ndfnameold =  (SELECT name from [sys].[database_files] where physical_name like '%ndf')
DECLARE @query varchar(MAX)

SET @query = 'ALTER DATABASE [MYDATABASE] MODIFY FILE ( NAME =  '+ @ndfnameold +', NEWNAME = MYDATABASE_1_Data )'

EXEC(@query)

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'.

I don't get whats wrong with this. SQL Server 2017

tsql sql-server
1 Answers

Since there's no - in the rest of your SQL, we can infer that there is one in the old logical file name. Since - isn't an allowed character for a Regular Identifier, we'll have to delimit it.

Safest is to use the QUOTENAME function that will delimit the name whilst respecting escaping rules if other characters are also problematic:

SET @query = 'ALTER DATABASE [MYDATABASE] MODIFY FILE ( NAME = ' +
             QUOTENAME(@ndfnameold) +
             ', NEWNAME = MYDATABASE_1_Data )'
user_154980
Related