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)

SSMA bug watch

The SQL Server 2005 Developer Edition installed on my Windows XP SP3 laptop had crashed over the weekend, so after a fresh reinstall including SP2 with hotfixes up to version 3073, I decided to re-run my SQL Server Migration Assistant for Access (SSMA) to create a new conversion file for testing.

The application performance was extremely poor, so I tried running SQL Profiler to try to find the bottlenecks, but I was seeing reads in the range of 20-50 which seemed trivial, however the Audit Login and Audit Logout events were literally taking minutes instead of seconds. I had saved the trace to a table, so I copied some of the SQL statements into SSMS and they ran very quickly, each barely taking a second.

I did a bit of research on connection pooling, but unfortunately that .NET option is not available in either SQL Server 2005 or MS-Access 2003. So why was Access connecting & disconnecting for every single database access?

Then I looked into the Access app and discovered that the SSMA was using the "SQL Server" provider in the connection strings. I changed that to the "SQL Native Client" and voila! the app performance sped up by a factor of at least 10x. Overall, the performance is now marginally slower than when using the native Access tables. That's fine, because this database will ultimately be running on a real Windows 2003 server with four dual-core CPU's and mucho RAM, waaay more powerful than the puny Centrino in my laptop.

I also took a couple of miscellaneous Access files & converted those as a further test, and they all converted using the old "SQL Server" intended for SQL 2000.

I've always run the SSMA using the wizard to save time, but now I'll need to revisit that to see if there is an option to use the correct provider for SQL 2005.

Max Locks Per File

I have a replicated MS-Access database that is used to store archive data from the production system. This archive is Access 2002 format and is approx 140 MB in size.

The design master was recovered from a replica & saved to a local C: drive, and then a new replica was created & published to a network share. So far, so good.

However, when I attempted to sync the two files, I received an error that the MaxLocksPerFile setting in the registry was exceeded and the sync could not be done. The "Help" button said that the default was 9500 (decimal), and suggested that I use SetOption to change it, but SetOption() has no such option.

I found it curious that a brand-new replica would require the complete sync process even though nothing was changed in either design or data, but I digress...

In any event, I did find the key in the registry by that name, it is located at:

HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile

=dword:0000251c

I changed the setting to 12,000 and the progress meter showed two bars and then froze with the error message. With 15,000 it reached five bars; and finally 25,000 which worked. The progress meter displayed the typical appearance & the sync was successful after a few seconds.

I then went back to experiment with a lower setting of 20,000 to try to find the go/no-go level but the sync was nearly instantaneous, so I suppose that the act of moving the replica forced Access to run the sync process on the entire contents of the file.

Excel prompts to save when no changes made

I have a large Excel workbook that is a catch-all for budgeting, time analysis & other miscellaneous items. When I would open the file to look at something, not change anything but simply close it, Excel would prompt me to save changes?

I found this article that explains "volatile formulas" in Excel such as CELL(), NOW(), RAND(), TODAY() and a few others. Here is the full article:

"Save changes in " prompt even if no changes are made

In the database world, expressions like NOW() are called non-deterministic functions, because they don't have a single repeatable value - NOW() always returns the current date / time which of course changes every time you invoke it.

If your worksheet uses those functions, the only way to avoid the "save" prompt is to turn off automatic recalculation & check the option for recalculate before save. But that makes your worksheet "dead" in the sense that changing cell values does not recalc, which is one of the great things about Excel. You'd have to either press F9 or click the Save icon to recalc after every edit.

Apparently this affects all versions of Excel from 97 up thru 2007.

Sharing Linux folders for Windows

Sharing Windows folders for access by Ubuntu Linux is fairly straightforward using Samba (SMB) but going in the opposite direction had me stumped.

From Windows, I would go into My Network Places ~ Entire Network ~ Microsoft Windows Network ~ Workgroup and I could see the Linux machine. Then I would double-click on it and enter my Linux name and password, but it would not connect.

The solution is that you need to add a Samba user, and you can't do that from the Linux GUI. You have to open a Terminal window and add the Samba user like this, where username is your Linux user name:

sudo smbpasswd -a username

after which you are prompted for the password, which is your Linux password.

At that point, you can access your Linux shares from Windows just like you would any other network share.

Replicated Queries

MS-Access makes it very easy to change the SQL of a query simply by using a statement like

CurrentDb.QueryDefs("MyQuery").SQL = "SELECT * FROM MyTable"

there is no need to Dim or Set anything, simply plug in the SQL and you're ready to go.

However, I just discovered that this does not work in a replicated database. Or I should say, it works in the Design Master but it does not work in a Replica. I needed to apply some complex criteria, more complex than could be expressed simply by using a WHERE condition when opening a report. Testing the code in the DM worked just fine, but users received a message "cannot update...object is read-only" because Access considers this to be a design change & does not allow that in a Replica.

You can code things like changing visibility, font colors, enabling & disabling controls, but QueryDefs are off limits.

The challenge: I had an existing report that used a query for the detail section, and the report footer contained a subreport with a summary that used the same query as the body of the report. Opening the report with a filter or where argument would properly filter the detail section, but the subreport would display all of the data. I tried using the OnOpen or OnFormat event to apply a filter to the subreport but that did not work.

(EDIT) At this point, I tried to change the SQL for the QueryDef using the type of statement shown above, to filter the data for both the body of the report and the summary subreport; this worked in the DM but not the Replica. My original post has not been clear about that. (EDIT)

My solution was basically to create a staging table for the subreport, and flush-and-fill the table prior to opening the report.

In the DM I did this:

• Ran the old (bad) code to set up the SQL statement
• Switched the query to a make-table, to create the staging table
• Switched the query to an append query
• Copied the append SQL and used that to build up a dynamic SQL statement in VBA

The button OnClick would then delete all records from the staging table, run the dynamic SQL to populate that table, and then open the report.

Moving Days

{EDIT} Around about September 2008, I decided not to move articles off this blog after all. I might copy a few to the tips page at my Web site, but otherwise I'm going to keep this blog intact. {EDIT}

When I started this blog three years ago, I had intended to use it as a quick way to publish my technology findings both for the general Internet community, and also to use it as a knowledge base for myself.

As it turns out, the older articles wind up being buried in the archives, grouped by month, so many times I've had to guess the month & drill down from there. The built-in search is quite effective, but being able to scan down a complete list of topics is much faster.

For those reasons, I've been moving articles from blogger to my personal web site, where I use a classic ASP page and the FileSystemObject to present a scrollable list of all articles, sizes, dates etc. The current list can be found at http://www.wvmitchell.com/tips.

Another difficulty using the blogger format is that many html tags are scrubbed-out from posts, so any code samples will lose their indenting & formatting, making them hard to decipher. My workaround has been to present a link to the actual code on a separate web page, but I would prefer to be able to post a complete article intact with all the formatting.

The blogger search facilities work very well, and Google indexes the blogger posts, so much information can be found via a normal Google search, but my web site also includes a search feature so nothing will be lost.

In closing, this blog will eventually be shrunk down to include only tech comments and observations.

Batch Files

By all accounts, DOS is dead. But since cmd.exe is still part of Windows, you can still write DOS batch programs to automate certain tasks.

For example, I am working with an Access application that is rebuilt every month. The previous process was to have the user navigate to a shared drive and use Windows Explorer to copy a new version of the MDB file and then paste it to a specific folder on their C: drive.

The new process uses a simple batch file like this:

@ECHO OFF
CLS
ECHO.
ECHO *** XYZ file setup program ***
ECHO.
IF NOT EXIST C:\XYZ MD C:\XYZ

IF NOT EXIST C:\XYZ\XYZ.MDB GOTO NOFILE
ECHO.
ECHO You already have a copy of XYZ on your computer
ECHO ===============================================
ECHO.
ECHO  Do you want to replace your existing copy?
ECHO  ------------------------------------------
GOTO HASFILE

:NOFILE
ECHO *** COPYING FILE, PLEASE WAIT ***
COPY /Y \\MYSERVER\XYZ\XYZ.MDB C:\XYZ
GOTO END

:HASFILE
ECHO  Y = Yes, replace my old file
ECHO  N = No, keep my existing copy of the file
ECHO.
SET CHOICE=
SET /P CHOICE= Type the letter and press {ENTER}
IF /I '%CHOICE%'=='Y' GOTO NOFILE
ECHO.
ECHO  (Nothing was copied or replaced)
ECHO.
:END
PAUSE
EXIT

For a new user, the folder is created if it does not exist.
Then, if the file exists the user is prompted to either overwrite or keep their existing copy.

Without the CHOICE statement, DOS would ask the overwrite question as
Yes/No/All
and this avoids the confusing "All" option.