Tuesday, October 27, 2009

SQL 2008 Replication - Convert from TEXT to VARCHAR(MAX)

SQL Server Replication has evidently come a long way since I began using it. Back in the day it seemed like every action was equivalent to a visit to the dentist, but today after hours of searching and digging, I found the solution to my problem was actually quite simple.

The task at hand was that that I had three columns that I wanted to convert from TEXT data types to VARCHAR(MAX).

First I tried the obvious: simply changing the data type via the SSMS GUI, but received the following error "Unable to modify table. Cannot drop the table 'tablename' because it is being used for replication." Strike one.

Next, I tried to script out my changes, but SSMS gave me a pretty awful script that basically created a temporary table, migrated the data, dropped the original, and renamed the temporary table back. That just didn't sound like a sound plan in a replicated environment.

Then I started investigating the replication stored procedures to see if there was one that allowed me to change the properties of an article (table in this case), to no avail.

Continuing on, I considered removing the article from the publication, modifying the column, then adding it back into the publication. Now, while I figured this method would work, I wanted to try one last thing: I did recall reading that running the raw ALTER TABLE ALTER COLUMN command may work, and having not tried that, I gave it a shot.

I opened a new query window and ran the following:
tablename ALTER COLUMN columnname VARCHAR(MAX)

BINGO! I checked my subscribers and within seconds they too inherited the schema change (which BTW is an option you can select on the publication properties).

Too easy... but had to share. Hopefully it sames someone some time down the road.