Renaming logical names with variables
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
sql-server tsql edited Nov 13 at 15:08
a_horse_with_no_name
291k46444538
asked Nov 13 at 14:42
atr4st
82
add a comment |
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
sql-server tsql edited Nov 13 at 15:08
a_horse_with_no_name
291k46444538
asked Nov 13 at 14:42
atr4st
82
add a comment |
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
sql-server tsql edited Nov 13 at 15:08
a_horse_with_no_name
291k46444538
asked Nov 13 at 14:42
atr4st
82
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
sql-server tsql sql-server tsql edited Nov 13 at 15:08
a_horse_with_no_name
291k46444538
asked Nov 13 at 14:42
atr4st
82
edited Nov 13 at 15:08
a_horse_with_no_name
291k46444538
asked Nov 13 at 14:42
atr4st
82
edited Nov 13 at 15:08
a_horse_with_no_name
291k46444538
edited Nov 13 at 15:08
a_horse_with_no_name
291k46444538
edited Nov 13 at 15:08
a_horse_with_no_name
291k46444538
291k46444538
asked Nov 13 at 14:42
atr4st
82
asked Nov 13 at 14:42
atr4st
82
asked Nov 13 at 14:42
atr4st
82
82
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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 )'
answered Nov 13 at 14:49
Damien_The_Unbeliever
192k17245331
Thanks for the answering. Yes, there is - in the old logical file name. I tried to use QUOTENAME() as you posted but still the same error message. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'.
– atr4st
Nov 14 at 6:19
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53283472%2frenaming-logical-names-with-variables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 )'
answered Nov 13 at 14:49
Damien_The_Unbeliever
192k17245331
Thanks for the answering. Yes, there is - in the old logical file name. I tried to use QUOTENAME() as you posted but still the same error message. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'.
– atr4st
Nov 14 at 6:19
add a comment |
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 )'
answered Nov 13 at 14:49
Damien_The_Unbeliever
192k17245331
Thanks for the answering. Yes, there is - in the old logical file name. I tried to use QUOTENAME() as you posted but still the same error message. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'.
– atr4st
Nov 14 at 6:19
add a comment |
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 )'
answered Nov 13 at 14:49
Damien_The_Unbeliever
192k17245331
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 )'
answered Nov 13 at 14:49
Damien_The_Unbeliever
192k17245331
answered Nov 13 at 14:49
Damien_The_Unbeliever
192k17245331
answered Nov 13 at 14:49
Damien_The_Unbeliever
192k17245331
answered Nov 13 at 14:49
Damien_The_Unbeliever
192k17245331
192k17245331
Thanks for the answering. Yes, there is - in the old logical file name. I tried to use QUOTENAME() as you posted but still the same error message. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'.
– atr4st
Nov 14 at 6:19
add a comment |
Thanks for the answering. Yes, there is - in the old logical file name. I tried to use QUOTENAME() as you posted but still the same error message. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'.
– atr4st
Nov 14 at 6:19
Thanks for the answering. Yes, there is - in the old logical file name. I tried to use QUOTENAME() as you posted but still the same error message. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'.
– atr4st
Nov 14 at 6:19
Thanks for the answering. Yes, there is - in the old logical file name. I tried to use QUOTENAME() as you posted but still the same error message. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'.
– atr4st
Nov 14 at 6:19
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53283472%2frenaming-logical-names-with-variables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
