SSMA - the Memo bug - part 2

My last post discussed a method for changing the data type of a field in SQL Server. My suggestion was to use the SSMS table designer to create the script for you, but there is a faster easier way provided that you can write some basic T-SQL.

Keep in mind that the design requirement is to create a script to apply the change, because this same change needs to be applied to three different databases with the exact same result. If you were going to do this on a one-off basis then you could just go in there & make the change.

The new approach mimics what I would do in MS-Access:

• Create a new field with the desired data type
• Copy all the data from the existing field into the new field, while making any conversions or adjustments.
• Delete the original field
• Rename the new field to match the original name

Here is a sample of the T-SQL script. The existing field MyField in the table MyTable is being changed from nvarchar(max) to nvarchar(4000):

ALTER TABLE dbo.MyTable ADD Tmp_MyField nvarchar(4000) NULL
GO
UPDATE dbo.MyTable SET Tmp_MyField = CONVERT(nvarchar(4000), MyField)
ALTER TABLE dbo.MyTable DROP COLUMN MyField
GO
EXECUTE sp_rename N'dbo.MyTable.Tmp_MyField', N'MyField', 'COLUMN'
GO

The only shortcoming of this approach is that the "renamed" field will move to the last position in the table, so if that matters in your application then the SSMS script method is the better choice.

In my case, the app didn't care about the ordinal position of the field. Plus, I had 59 fields scattered in 25 different tables that needed conversion, so I wrote some VBA to loop thru the TableDefs in Access & create the change script for me. If you're interested you can view the VBA module here: SQL Memo Fixup.

PLEASE NOTE that if you have Memo fields in your Access database, you will need to run this code to create this script in Access before you run the SSMA conversion, and then apply the script to the SQL database after you run the conversion.