November 6, 2007 at 7:50 am
I tried using dynamic sql for creating a filegroup as a task in the beginning of the package but that is giving me an error now..could you plz give me some kind of direction on how i can solve this:
declare @newfilegroup varchar(60)
set @newfilegroup = substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)
EXEC ('ALTER DATABASE test ADD FILEGROUP ' + @newfilegroup)
this gives me a synatx error where as
declare @newfilegroup varchar(60)
set @newfilegroup = 'a' + substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)
EXEC ('ALTER DATABASE test ADD FILEGROUP ' + @newfilegroup)
works fine
November 6, 2007 at 8:07 am
This is interesting, the problem is not with the dynamic sql. The problem is the filegroup name is expecting a character as the starting character.
Prasad Bhogadi
www.inforaise.com
November 6, 2007 at 8:31 am
yes.. maybe but the thing is that right now they are created manually as yearlys like 07, 08 that works fine..
November 6, 2007 at 9:21 am
Cory Ellingson (11/6/2007)
declare @newfilegroup varchar(60)set @newfilegroup = substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)
EXEC ('ALTER DATABASE northwind ADD FILEGROUP [' + @newfilegroup + ']')
It needs to be wrapped in the [ ].
November 6, 2007 at 9:50 am
Good One. It should work like a charm...
Prasad Bhogadi
www.inforaise.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply