471,570 Members | 962 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,570 software developers and data experts.

ALTER DATABASE statement with Table Partitioning (SQL 2005)

I'm currently working on Table Partitioning. I have done everything succesfull for partitioning, what I'm struggling with is to use
" SELECT MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY' " 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.

Please can someone help me, I'm in struggeling with this one.
Jan 15 '07 #1
1 1936
almaz
168 Expert 100+
Use
Expand|Select|Wrap|Line Numbers
  1. EXEC ('ALTER DATABASE VadivelTesting
  2. ADD FILEGROUP ' +@NewFG_Name)
  3.  
Jan 15 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Dylan Nicholson | last post: by
7 posts views Thread by Jon Combe | last post: by
2 posts views Thread by Kums | last post: by
2 posts views Thread by Jeff_in_MD | last post: by
10 posts views Thread by shsandeep | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.