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.

SSMA - the Memo bug

I'm working with the Microsoft SQL Server Migration Assistant to convert an Access 2003 db to SQL Server 2005. Just today I found another little bug.

In Access you can have a Memo field that holds up to 64k characters, give or take. This is great for adding comments and notes.

The SSMA converts those as nvarchar(max) and correctly moves the data, however when linking to it using the SQL Native Client, Access thinks it is a text(255) field and displays only the last 255 characters of the field. Not good.

The fix is that you have to change the data type to nvarchar(4000) and relink; Access will then retrieve the entire field (up to 4,000 chars of course). In the event that you have Memo fields that exceed 4,000 characters - good question. You might want to consider saving those on the file system & storing their locations in SQL.

The safest way to make the table change:

• using SSMS, open the table in design view
• make the change but don't save it
• click the script icon to save the table change as a T-SQL file
• close the table without saving the change
• make a backup of your database - !
• apply the script
• relink the Access front-end
• test

Time Sheets

I am working on an MS-Access time & billing program for consultants. This is an old program that was originally designed in Access 2.0, and over the years it was converted to 97 and then 2000, and finally was split into separate front end / back end files.

One of the new features requested is to allow the consultant to view their hours in comparison to the standard 8-hour day and 5-day week. For example, if they work 6 hours on Monday, then 10 on Tuesday, then 7 on Wednesday, the program should show 23 hours worked vs. the standard 24 hours (3 days x 8 hours) and indicate that they are 1 hour short. This required a few new functions so I'd like to share those.

First of all, I needed to filter the dates to the current week. For that I created two functions, fairly straightforward, as follows:

Function Get_StartOfWeek() As Date
Dim d As Date
d = Date
Do While DatePart("w", d, vbSunday) > 1
d = DateAdd("d", -1, d)
Loop
Get_StartOfWeek = d
End Function


Function Get_EndOfWeek() As Date
Dim d As Date
d = Date
Do While DatePart("w", d, vbSunday) < 7
d = DateAdd("d", 1, d)
Loop
Get_EndOfWeek = d
End Function

...and then the WHERE clause in the query looks like this:

WHERE [WorkDate]>=Get_StartOfWeek() And [WorkDate]<=Get_EndOfWeek()

The next step was a bit of a challenge, because the consultant might work on a Sunday or Saturday and those hours need to be included in the weekly totals, but not affect the "standard week" hour calculation. My solution was to use the Choose() function which then allowed me to set the daily expectations according to the day of the week. This is used inside a query, so I could not use the vbSunday constant, that's why the "1" is hard-coded into the DateDiff() function:

StandardWeekToDate:
8*Choose(DateDiff("d",Get_StartOfWeek(),[WorkDate],1),1,2,3,4,5,5)

You may ask, why are there only six items in the Choose arguments, when there are seven days in a week? The reason is that the Choose arguments are 1-based, so if the [WorkDate] is a Sunday, then DateDiff returns a zero and so Choose returns a Null. Which is fine in this particular scenario, because Sunday is not a normal workday.

If you need to load values for all seven days of the week, then you have to subtract 1 from the [WorkDate] in order to get a Sunday date to return item 1 from the Choose. You can't substitute vbSaturday (7) for the start of week in DateDiff; that would give the wrong answer. Here is the seven-day version:

StandardWeekToDate:
8*Choose(DateDiff("d",Get_StartOfWeek(),DateDiff("d",-1,[WorkDate]),1),0,1,2,3,4,5,5)