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:
EXEC ('ALTER DATABASE VadivelTesting ADD FILEGROUP ' + @NewFG_Name)
Dig Deeper on Microsoft SQL Server Database Development
Related Q&A from Greg Low
Should developers be granted permissions to production queue in a SQL Server environment? See why expert Greg Low suggests proc access by WITH ...
Find how to save stored procedure output to the network drive via SQL Server Management Studio.
Find how to query in SQL Server to get all database names created by users.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.