none
Getting Invalid filegroup error

    질문

  • --adding new filegroup STRBM to AS1BM
    ALTER DATABASE AS1BM
    ADD FILEGROUP STRBM
    GO


    ALTER DATABASE AS1BM
    ADD FILE (NAME = FILE1_BM
    , FILENAME = 'E:\SCHOOL\ITM610\AS1\FILE1_BM.NDF'
    , SIZE = 10MB
    , FILEGROWTH = 2MB
    , MAXSIZE = 100MB
    )
    , (NAME = FILE2_BM
    , FILENAME = 'E:\SCHOOL\ITM610\AS1\FILE2_BM.NDF'
    , SIZE = 10MB
    , FILEGROWTH = 2MB
    , MAXSIZE = 100MB
    ) TO FILEGROUP STRBM 
    GO



    CREATE SCHEMA STORAGEBM 
    GO

    CREATE TABLE STORAGEBM.FILEGROUPSBM
    (filegrp_id_bm int identity (10,10)
    constraint filegrp_pk primary key
    , filegrp_instance_bm varchar(100) not null
    , filegrp_db_bm varchar(100) not null
    ) ON STRBM

    GO 

    I get a problem when i try to create the table. It says "Invalid filegroup 'STRBM' specified." what did I do wrong?

    Thanks a lot.
    Billy
    2009년 6월 13일 토요일 오후 9:41

답변

  • Hi Billy,
    Chances are good that you have not set the database context to the AS1BM database.  Try inserting the following code before your CREATE SCHEMA statement.  

    USE AS1BM 
    GO

    Whatever database you were using when you ran the CREATE SCHEMA statement now as a schema named STORAGEBM, so you may want to drop that schema from that database.

    Regards,
    Gail


    Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights
    • 답변으로 표시됨 waachaa 2009년 6월 14일 일요일 오전 1:39
    2009년 6월 14일 일요일 오전 12:38
  • here is step by code which works on my system
    --STEP 1 : Created a Test Db and Added a File Group
    CREATE DATABASE [TEST] ON  PRIMARY 
    ( NAME = N'TEST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TEST.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'TEST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TEST_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
    GO
    ALTER DATABASE [TEST] ADD FILEGROUP [Test1FG1]
    GO
    --SETP 2 :- Add the 2 file groups
    ALTER DATABASE [TEST] 
    ADD FILE 
    ( NAME = test1dat3,
      FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
      SIZE = 5MB,
      MAXSIZE = 100MB,
      FILEGROWTH = 5MB),
    ( NAME = test1dat4,
      FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
      SIZE = 5MB,
      MAXSIZE = 100MB,
      FILEGROWTH = 5MB)
    TO FILEGROUP Test1FG1
    ---ALTERED default Filegroup tp TestFG1 (so even if you dont specify the Filegroup Name your object will get created on this filegroup)
    
    ALTER DATABASE [TEST]
    MODIFY FILEGROUP Test1FG1 DEFAULT
    GO
    ---STEP 3:- Create required schema
    USE [TEST]
    GO
    CREATE SCHEMA STORAGEBM 
    GO
    
    --STEP 4:-Create The Table (THIS IS WHERE YOU GETTING ERROR)
    USE [TEST]
    GO
    CREATE TABLE STORAGEBM.FILEGROUPSBM
     (filegrp_id_bm int identity (10,10)  constraint filegrp_pk primary key
     , filegrp_instance_bm varchar(100) not null
     , filegrp_db_bm varchar(100) not null
     ) ON Test1FG1
    
    GO
    USE master 
    GO 
    drop DATABASE [TEST] 
    
    
    Have a look here for step by step details  http://www.mssqltips.com/tip.asp?tip=1112

    Hope it helps

    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    • 답변으로 표시됨 waachaa 2009년 6월 14일 일요일 오전 1:39
    2009년 6월 14일 일요일 오전 1:24

모든 응답

  • Hi Billy,
    Chances are good that you have not set the database context to the AS1BM database.  Try inserting the following code before your CREATE SCHEMA statement.  

    USE AS1BM 
    GO

    Whatever database you were using when you ran the CREATE SCHEMA statement now as a schema named STORAGEBM, so you may want to drop that schema from that database.

    Regards,
    Gail


    Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights
    • 답변으로 표시됨 waachaa 2009년 6월 14일 일요일 오전 1:39
    2009년 6월 14일 일요일 오전 12:38
  • here is step by code which works on my system
    --STEP 1 : Created a Test Db and Added a File Group
    CREATE DATABASE [TEST] ON  PRIMARY 
    ( NAME = N'TEST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TEST.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'TEST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TEST_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
    GO
    ALTER DATABASE [TEST] ADD FILEGROUP [Test1FG1]
    GO
    --SETP 2 :- Add the 2 file groups
    ALTER DATABASE [TEST] 
    ADD FILE 
    ( NAME = test1dat3,
      FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
      SIZE = 5MB,
      MAXSIZE = 100MB,
      FILEGROWTH = 5MB),
    ( NAME = test1dat4,
      FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
      SIZE = 5MB,
      MAXSIZE = 100MB,
      FILEGROWTH = 5MB)
    TO FILEGROUP Test1FG1
    ---ALTERED default Filegroup tp TestFG1 (so even if you dont specify the Filegroup Name your object will get created on this filegroup)
    
    ALTER DATABASE [TEST]
    MODIFY FILEGROUP Test1FG1 DEFAULT
    GO
    ---STEP 3:- Create required schema
    USE [TEST]
    GO
    CREATE SCHEMA STORAGEBM 
    GO
    
    --STEP 4:-Create The Table (THIS IS WHERE YOU GETTING ERROR)
    USE [TEST]
    GO
    CREATE TABLE STORAGEBM.FILEGROUPSBM
     (filegrp_id_bm int identity (10,10)  constraint filegrp_pk primary key
     , filegrp_instance_bm varchar(100) not null
     , filegrp_db_bm varchar(100) not null
     ) ON Test1FG1
    
    GO
    USE master 
    GO 
    drop DATABASE [TEST] 
    
    
    Have a look here for step by step details  http://www.mssqltips.com/tip.asp?tip=1112

    Hope it helps

    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    • 답변으로 표시됨 waachaa 2009년 6월 14일 일요일 오전 1:39
    2009년 6월 14일 일요일 오전 1:24
  • Dropped the schema, used USE before creating the schema again and now everything is working right!

    Thanks!
    Billy


    2009년 6월 14일 일요일 오전 1:43