Modify TEXTIMAGE_ON

  • TBL_01 - is a table in SQL 2005 that contains an ntext column (yes, could have been an nvarchar(max)). TBL_01 is not empty. The TEXTIMAGE_ON for TBL_01 is pointing to PRIMARY filegroup instead of a separate filegroup (say blog_group) that's created for this very purpose.

    I need to modify this table to point TEXTIMAGE_ON to blob_group instead of PRIMARY. Apparently, the only way to acheive this via SQL is to create a new table TBL_02, copy over data, drop TBL_01 and rename TBL_02 to TBL_01.

    Is there no other way to alter this table in order to point the TEXTIMAGE_ON to [blog_group]?

    Thanks in advance!

    The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner
  • Creating a new table with the right settings is the only way I know of.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared is right. I'm going through that exercise at the moment with a number of tables. You'll have to create a new table with the textimage where you want it, copy the data over, drop the old table, rename the new and then recreate all the constraints and indexes.

    It's not a trivial operation if you have lots of tables or tables with lots of rows.

    If you have only a couple tables to do, you can get management studio to generate scripts for you. Edit the table (in the gui), change a column's data type, script the changes and don't save. You then have a script that almost does what you need.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks GSquared & GilaMonster

    The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply