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 '18 at 15:08
a_horse_with_no_name
296k46451546
asked Nov 13 '18 at 14:42
atr4statr4st
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 '18 at 15:08
a_horse_with_no_name
296k46451546
asked Nov 13 '18 at 14:42
atr4statr4st
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 '18 at 15:08
a_horse_with_no_name
296k46451546
asked Nov 13 '18 at 14:42
atr4statr4st
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 '18 at 15:08
a_horse_with_no_name
296k46451546
asked Nov 13 '18 at 14:42
atr4statr4st
82
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
asked Nov 13 '18 at 14:42
atr4statr4st
82
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
edited Nov 13 '18 at 15:08
a_horse_with_no_name
296k46451546
296k46451546
asked Nov 13 '18 at 14:42
atr4statr4st
82
asked Nov 13 '18 at 14:42
atr4statr4st
82
asked Nov 13 '18 at 14:42
atr4statr4st
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 '18 at 14:49
Damien_The_UnbelieverDamien_The_Unbeliever
194k17248335
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 '18 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 '18 at 14:49
Damien_The_UnbelieverDamien_The_Unbeliever
194k17248335
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 '18 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 '18 at 14:49
Damien_The_UnbelieverDamien_The_Unbeliever
194k17248335
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 '18 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 '18 at 14:49
Damien_The_UnbelieverDamien_The_Unbeliever
194k17248335
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 '18 at 14:49
Damien_The_UnbelieverDamien_The_Unbeliever
194k17248335
answered Nov 13 '18 at 14:49
Damien_The_UnbelieverDamien_The_Unbeliever
194k17248335
answered Nov 13 '18 at 14:49
Damien_The_UnbelieverDamien_The_Unbeliever
194k17248335
answered Nov 13 '18 at 14:49
Damien_The_UnbelieverDamien_The_Unbeliever
194k17248335
194k17248335
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 '18 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 '18 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 '18 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 '18 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.
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
