Ask the Expert

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?

    Requires Free Membership to View

    Login

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)

This was first published in February 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top