Syntax error in SQL Server script
I'm currently working on table partitioning. I'm struggling with using this " SELECT MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY' " in order to get the last used Filegroup. Now this works just fine, but when I run the following script: --Decalre variables DECLARE @LastFilegroupName VARCHAR(50) DECLARE @FilegroupName VARCHAR(50) --Retuns the next FileGroup to be used SET @LastFilegroupName = (select MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY') SET @LastFilegroupName = Replace(@LastFilegroupName,'FileGrp','') SET @FilegroupName = 'FileGrp' + CAST((@LastFilegroupName + 1) as varchar(10)) --Alter database statement ALTER DATABASE VadivelTesting ADD FILEGROUP @NewFG_Name This script gives the following error "Incorrect syntax near '@NewFG_Name'." When I give it a static name it works fine, but not with the variable. What should I do?
I'm currently working on table partitioning. I'm struggling with using this " SELECT MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY' " in order to get the last used Filegroup. Now this works just fine, but when I run the following script: --Decalre variables DECLARE @LastFilegroupName VARCHAR(50) DECLARE @FilegroupName VARCHAR(50) --Retuns the next FileGroup to be used SET @LastFilegroupName = (select MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY') SET @LastFilegroupName = Replace(@LastFilegroupName,'FileGrp','') SET @FilegroupName = 'FileGrp' + CAST((@LastFilegroupName + 1) as varchar(10)) --Alter database statement ALTER DATABASE VadivelTesting ADD FILEGROUP @NewFG_Name This script gives the following error "Incorrect syntax near '@NewFG_Name'." When I give it a static name it works fine, but not with the variable. What should I do?
I don't believe that the filegroup name can be a variable in the ALTER DATABASE statement. Try the following:
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.
EXEC ('ALTER DATABASE VadivelTesting ADD FILEGROUP ' + @NewFG_Name)
This was first published in February 2007
Dig Deeper
PRO+
Content
Find more PRO+ content and other member only offers, here.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our SQL Server experts
View all SQL Server questions and answers
0 comments
Oldest Newest