May 6, 2009 at 12:51 am
Works:
------
ALTER DATABASE CCBS ADD FILEGROUP [1111]
Does Not Work:
---------------
declare @filegroupname varchar(10)
set @filegroupname = '11111'
ALTER DATABASE CCBS ADD FILEGROUP @filegroupname
Question:
As you can see, in the first scenario, I am having the name of the filegroup in the command itself and it works fine.
But when I try to assign it to a variable and then use this variable in the 'alter database..' command, it fails by giving an error msg (Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '@filegroupname'.)
Is not possible to assign the name of the filegroup into a variable and use it in the 'alter database...' command???
May 6, 2009 at 2:27 am
Vinoo (5/6/2009)
Works:------
ALTER DATABASE CCBS ADD FILEGROUP [1111]
Does Not Work:
---------------
declare @filegroupname varchar(10)
set @filegroupname = '11111'
ALTER DATABASE CCBS ADD FILEGROUP @filegroupname
Question:
As you can see, in the first scenario, I am having the name of the filegroup in the command itself and it works fine.
But when I try to assign it to a variable and then use this variable in the 'alter database..' command, it fails by giving an error msg (Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '@filegroupname'.)
Is not possible to assign the name of the filegroup into a variable and use it in the 'alter database...' command???
You can't use variables like that. You need to use dynamic sql.
Something like this
DECLARE @cmd nvarchar(2000), @filegroupname varchar(10)
SET @filegroupname = '11111'
SET @cmd = 'ALTER DATABASE CCBS ADD FILEGROUP ' + @filegroupname
EXEC sp_executesql @cmd
[font="Verdana"]Markus Bohse[/font]
May 6, 2009 at 4:05 am
As MarkusB showed, dynamic sql is the solution if you want to do it using TSQL.
vbs or powershell using SQLSMO is an alternative.
FYI I use this script to add a user filegroup and making it default (after db create) to keep primary clean for the catalog info.
/*
* alzdba dd 20070509
* add filegroup FGUserData with one file and make it the DEFAULT filegroup
* to split user-objects from system(catalog) objects to support filegroup-restores
* so we can bring up the catalog asap
* and then add the needed filegroup-restores
*
* sp_helpdb
*
*/
DECLARE @ExcludedDb bit
/*
* check excluded databases
*/
IF EXISTS (SELECT *
FROM master.sys.databases
WHERE NAME not in ('master', 'msdb', 'tempdb', 'model' -- SQLServer system databases (The master and model databases cannot have files added to them)
,'Spotlight' ,'DDBADummy' ,'DJOBI' -- ALZDBA workdatabases
,'AdventureWorks', 'AdventureWorksDW', 'Northwind', 'Pubs' -- SQLServer sample databases
,'DDBAServerPing' -- ALZDBA pingdatabase
)
AND name = db_name()
)
-- Rij gevonden: alles OK
SET @ExcludedDb = 0
ELSE
SET @ExcludedDb = 1
/*
* check if db has a non-PRIMARY filegroup
*/
IF EXISTS (Select *
FROM sys.filegroups
WHERE NAME 'PRIMARY' )
OR @ExcludedDb = 1
BEGIN
IF @ExcludedDb = 1
BEGIN
PRINT '*'
PRINT '*'
PRINT '- Database [' + db_name() + '] is an excluded database for this operation ! '
PRINT '*'
PRINT '*'
END
ELSE
BEGIN
PRINT '*'
PRINT '*'
PRINT '- Database already contains a non-PRIMARY filegroup'
PRINT '*'
PRINT '*'
END
END
ELSE
BEGIN
DECLARE @rc INT
DECLARE @cmd NVARCHAR(MAX)
DECLARE @InitialSizeKB VARCHAR(15)
DECLARE @IncrementKB VARCHAR(15)
DECLARE @Filename NVARCHAR(260)
SET @rc = -1
SELECT TOP 1 @Filename = REPLACE (filename , '.mdf' , '_FGUserData.NDF')
, @InitialSizeKB = CAST(([Size] * 8 ) AS VARCHAR(15))
, @IncrementKB = CAST(CEILING ([growth] * 8 ) AS VARCHAR(15)) -- normaly we use growth in MB
FROM sys.sysfiles
WHERE LOWER(filename) LIKE '%.mdf'
SELECT @cmd = 'Print ''* [' + db_name() + '] *'' ; '
+ CHAR(10) + 'Alter database [' + db_name() + '] '
+ CHAR(10) + ' ADD FILEGROUP FGUserData ;'
+ CHAR(10)
+ 'Alter database [' + db_name() + '] '
+ CHAR(10) + ' ADD FILE ( NAME = FUserData, FILENAME = ''' + @Filename + ''', '
+ CHAR(10) + ' SIZE = ' + @InitialSizeKB + 'KB, MAXSIZE = UNLIMITED , FILEGROWTH = ' + @IncrementKB + 'KB )'
+ char(10) + ' TO FILEGROUP FGUserData;'
+ CHAR(10) + 'Alter database [' + db_name() + '] '
+ CHAR(10) + ' MODIFY FILEGROUP FGUserData DEFAULT;'
--PRINT @cmd
--PRINT '*'
--PRINT '*'
--PRINT '*'
EXEC @rc = sp_executesql @stmt = @cmd
IF @rc = 0
BEGIN
PRINT '*'
PRINT '* FILEGROUP FGUserData added to database [' + DB_NAME()+ '].'
PRINT '*'
END
ELSE
BEGIN
PRINT '*'
PRINT '- Failure for database [' + DB_NAME()+ '] rc = [' + cast(@Rc as varchar(15)) + ']'
PRINT '*'
END
END
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply