Here's some code I did for a data-driven conversion in SQL 2005. It should work for SQL 2000, but you won't need the section for the varchar(max).

First the stored proc:

create proc up_add_field_to_NCEM43_Data

@fld_name nvarchar(30), @fld_size int, @fld_type varchar(30)

as

begin

declare @sqlbuff nvarchar(255)

if (substring(@fld_name,1,1) = '$')

select @fld_name = substring(@fld_name,2,datalength(@fld_name)-1)

if (@fld_size > 8000)

begin

set @sqlbuff = 'Alter table NCEM43_Data '

+ 'add ['

+ @fld_name

+ '] '

+ @fld_type

+ '(max) NULL'

end

else

begin

set @sqlbuff = 'Alter table NCEM43_Data '

+ 'add ['

+ @fld_name

+ '] '

+ @fld_type

+ '('

+ convert(varchar(4),@fld_size)

+ ') NULL'

end

print @sqlbuff

exec (@sqlbuff)

if (@@error != 0)

return -1

else

return 0

end

go

Then some calling code:

declare @error_countint

DECLARE @retstatint

declare @fld_namenvarchar(30)

execute @retstat = up_add_field_to_NCEM43_Data @fld_name, 255, 'nvarchar'

if (@retstat != 0)

begin

set @error_count = 1 + @error_count

end

Of course you still will have the challenge of updating a column. My solution was to also put the update statement into a stored procedure much like the first one.

Good luck!