Jon Sayce

Restart regularly to avoid Last Known Good Configuration problems

jonsayce — Mon, 03 Nov 2008 21:04:00 GMT

Until last week I didn't really know what last known good configuration actually meant. It turns out that what it means is "I'm going to throw away all the settings you've changed since the previous start-up of this PC", which can be a bit problematic, to say the least.

We had a problem with a production server which ended up requiring a reboot. Unfortunately the server failed to restart properly and the only option left was to choose last known good configuration.

The event log shows that the uptime was over 18 million seconds - the last reboot was at the end of March, 7 months ago. In this time various services had been installed, which all vanished as the system rolled back the registry changes.

My advice would be to reboot as regularly as possible, so that if you do ever have to choose last known good configuration then you don't wipe out too many changes.

Missing (Hidden) ODBC Data Sources

jonsayce — Thu, 30 Oct 2008 21:02:00 GMT

Have you ever created an ODBC System DSN, only to watch as it doesn't appear in the list in the ODBC Data Source Administrator control panel? I recently used a PC (Windows XP SP2) where the registry showed that new DSNs were being successfully created, but the dialog refused to display them.

The real problem was that I was creating the DSNs for use in a Crystal report, and they weren't showing up in the list of available DSNs in the Crystal designer either.

This doesn't seem to be a very common problem, but I did eventually find evidence that other people had suffered from and fixed the same issue.

Under the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources key there's a string value called (Default), which should be be empty: it will display as (value not set) in the list view:

or as a blank string if you open up the dialog to modify the data:

The key point here is that the data is not a blank string, and is not the text "(value no set)" which one blogger found! It must be set to the registry equivalent of NULL.

The problem can easily be recreated by clicking OK on the above dialog, rather than cancel. If you do this you'll notice that (value not set) disappears from the list view, as the empty value is replaced by a blank string:

Any new ODBC System DSNs will now fail to appear in the Administrator dialog, even though they are stored in the registry.

To fix the problem, simply delete the (Default) value. It will be instantly recreated as (value not set), and if you re-open the Administrator dialog then all of your DSNs will now be visible. They will also show up in other applications like the Crystal designer.

MobileMe and Outlook contacts: Invalid ENTRYID

jonsayce — Sun, 05 Oct 2008 22:20:00 GMT

Apple's MobileMe service has been through some troubled times since its launch in July, but they seem to have finally fixed a nasty problem which corrupted the Outlook address book.

Following the very first sync, Outlook contacts became unusable in emails. Hitting ctrl-k didn't autocomplete the names as you'd expect, and clicking on the To button brought up a list of names but no email addresses. Worst of all, right-clicking and requesting the properties of a contact from this list gave the following message:

Recently a new version of the MobileMe control panel has been released, and the online support chat advice was as follows:

Install iTunes 8.0.1
Install new MobileMe control panel (this happened automatically on one of my PCs)
Reboot
Sign out of MobileMe
Sign back in

At this point all sync settings had been reset and the control panel showed that syncing wasn't enabled, so I would add a few extra steps:

Enable automatic syncing
Enable contacts

There seemed to be some confusion about whether the computer could really have the same name as one already in use with this account:

I chose Use same name. Once this was dealt with and I hit Apply, there was a further question about how the sync should work. I selected replacing the data on the PC to get rid of all the broken ENTRYIDs (this choice only affects the first sync, not the regular merging which works in both directions):

The Outlook contacts were then all wiped out, and the new ones now work perfectly in emails, in the To dialog, etc.

Despite all the criticism of MobileMe, the contacts sync now seems to work perfectly and, to me, will be well worth £59 a year when the perpetually-extending trial period finally ends in a few month's time.

I've never tried the calendar sync on MobileMe as I prefer the sharing functionality of Google Calendar - to sync this I use the completely awesome NuevaSync, which is totally free and highly recommended.

Free SQL Server 2008 E-Learning from Microsoft

jonsayce — Fri, 18 Apr 2008 08:12:00 GMT

Microsoft are offering three free SQL Server 2008 E-Learning courses:

What's New in Microsoft SQL Server 2008 for Database Development
What's New in Microsoft SQL Server 2008 for Enterprise Data Platform
What's New in Microsoft SQL Server 2008 for Business Intelligence

The courses are available at http://go.microsoft.com/?linkid=8731212

New MSDN SQL Server Wiki

jonsayce — Thu, 17 Apr 2008 10:35:00 GMT

The Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums have created a new wiki to present Solutions to Common T-SQL Problems at http://code.msdn.microsoft.com/SQLExamples. At the moment mere mortals can't update the solutions but you can leave comments.

This type of page is not served

jonsayce — Sun, 13 Apr 2008 21:38:00 GMT

One of my experiments with ASP.NET MVC involves integration with a shopping cart product built in classic ASP. Up till now I've been happily relying on Cassini (the web server built into Visual Studio) but today I began testing the integration and ran into a problem. As soon as I requested an ASP page, my server reported that "This type of page is not served."

Initially I wondered whether the default MVC web.config had somehow prevented ASP pages working, but it turns out that it's nothing to do with MVC or the web.config - Cassini only works with HTML and ASPX pages - ASP pages must be served up from IIS.

MVC: Intercepting Controller and HandleUnknownException

jonsayce — Tue, 25 Mar 2008 22:57:00 GMT

One of the recent videos on ASP.NET MVC covers the Hands On Labs from Mix. Scott Hanselman runs through a lab called the CustomControllerFactory, demonstrating how to build an intercepting controller.

Having followed the lab and tried the code I was left dazed and confused, and I wasn't the only one. Life would have been easier if the code from the labs were available, but I couldn't find it anywhere.

There seem to be two sources of confusion:

The video doesn't mention a vital addition to the Global.asax.cs file to change the controller factory
HandleUnknownAction ends up handling all actions

Getting the Lab Code Working

Add a line to Application_Start (in Global.asax.cs) to set the framework to use the InterceptingControllerFactory instead of the DefaultControllerFactory:

With this line set, any controllers instanciated will be InterceptionControllers. Without it, the default controller factory is used and the interception classes will be completely ignored.

Catching Unknown Actions

Once the lab code is working, you'll soon notice that HandleUnknownAction is catching every single action.

This happens because the InterceptionController never has any actions. It's a wrapper around a controller of the type specified in the URL (the controller with actions), but the InterceptionController itself doesn't have any actions, so every action will trigger the HandleUnknownAction method.

The best way to get your head round this is to download the MVC code from CodePlex, add the project to your solution and reference it directly. It's a great way to learn more about how the MVC framework does its stuff: in our example, the ProcessRequest method of the MVCHandler object creates a controller using our custom controller factory, so ProcessRequest is working with an InterceptionController.

When the Execute method of the InterceptionController is called, it calls InvokeAction, which checks the actions defined on the controller. InterceptionController has none, so Execute calls HandleUnknownAction and our override fires. In the video Scott uses the override to check the actionName value and decide whether or not the controller should continue.

Base Class

If you're trying to catch only unknown actions then an alternative way is to define a base controller class, inheriting from controller, which overrides HandleUnknownAction:

Now all of your controllers can inherit from this base controller:

The controller created in ProcessRequest will now be of the type specified in the URL and will have actions defined, so HandleUnknownAction will only fire when there really is an unknown action - there is no need to filter on the actionName anymore.

Msg 7391 - OLE DB provider SQLOLEDB was unable to begin a distributed transaction

jonsayce — Sat, 01 Mar 2008 11:58:00 GMT

This error occurred when running queries joining to a linked server, when the linked server was upgraded from Windows 2000 Windows Server 2003 SP2.

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because OLE DB provider "%ls" for linked server "%ls" was unable to begin a distributed transaction.

Some common causes of the error are described on MSDN although this second article actually provided the solution we needed.

In our case the problem turned out to be that Windows Server 2003 has DTC network access disabled by default. Turning it on is pretty straightforward but stops the SQL Server service and can require the installation media.

The Microsoft SQL Server Support Blog has a post describing DTC settings in more detail, but we found that the default setup worked once network access had been enabled.

What motivates geeks?

jonsayce — Thu, 14 Feb 2008 13:45:00 GMT

I recently moved a project from .NET 2.0 to .NET 3.5 and discovered that a method I had used to deserialize some JSON had become obsolete. This prompted much debate with my colleagues about why the .NET framework continues to change.

The guys I was talking to both reckon that a large part of the motivation for Microsoft to keep changing the .NET Framework is just to make money. I guess in their view of the situation, the desire for change is being driven from the top, and Scott Guthrie and co. are coming up with changes to please their financially-minded masters.

I don't buy this. I can't imagine the .NET architects sitting round a table in Redmond saying "We have been told to change things for .NET 4.0 so our users have to upgrade - can anyone think of anything we can change?"

My view of the situation is that change is being driven from the bottom: the guys who actually design and build the Framework, who are passionate about creating the best development platform they can, are constantly hoping to improve things for the millions of developers out there using .NET.

The fact that I'm struggling to adapt to a change in JSON deserialization is very frustrating, but I have no doubt that the person who took the decision to deprecate the old method and create the DataContractJsonSerializer class did so because they thought it would be better than the JavaScriptSerializer.

Obviously the money men in Microsoft would have been pleased that the JSON guru decided to improve things, as it's one microscopic part of the reason that people will upgrade to 3.5, but I firmly believe that the changes are driven by people who are engrossed in the technical side of their job, and are desperate to make .NET as good as possible.

They are, after all, just another bunch of geeks. They may be more talented and more influential than your average bunch of geeks, but they are fundamentally just another team of geeks doing their jobs. In my experience, whilst geeks want to get paid just like anyone else, what they really want is to make things work better.

Programmatically Listing SQL Servers

jonsayce — Sun, 10 Feb 2008 22:37:00 GMT

Using the .NET Framework there are a variety of ways you can search for available SQL Servers, but no single technique does the whole job. The best approach is to use a combination of methods to ensure you get the most complete list possible.

Most methods rely on a UDP broadcast to locate servers - this is good for finding unknown servers which haven't been registered but doesn't guarantee to return the definitive list. Timeouts and firewalls blocking the relevant ports can lead to servers missing from the list and, if you're not on a network then even local servers will disappear from the list, as the UDP broadcast fails completely. In addition the list of servers is not guaranteed to be the same if you run the search twice.

The main alternative is to search the registry for registered SQL Servers. Whilst this is fine for servers you know about (and works when the network is unavailable) it doesn't allow you to discover new SQL Servers.

Method 1: UDP Broadcast

Used by:

Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers
System.Data.Sql.SqlDataSourceEnumerator
SQL DMO ListAvailableSQLServers

The first two actually are actually the same under the covers: EnumAvailableSqlServers is simply an abstraction of the SqlDataSourceEnumerator class.

Pros:
+ Includes SQL Servers which are not registered

Cons:
- Doesn't work when there's no network connection
- Subject to firewall rules (Blocked TCP 1433 and UDP 1434)
- Doesn't find SQL Servers if SQL Browser is off
- Doesn't find SQL Servers if they are hidden
- List contents not guaranteed to be repeatable

Method 2: Reading Registered Servers from the Registry

Used by:
Microsoft.SqlServer.Management.Smo.SmoApplication.SqlServerRegistrations.RegisteredServers

Pros:
+ Works with no network connection
+ Works regardless of firewalls
+ Shows all registered servers, regardless of SQL Browser/hidden status

Cons:
- Only shows SQL Servers which have been registered
- Will be removed in a future version of SQL Server according to MSDN

Method 3: SMO WMI

Windows Management Instrumentation provides a third way to locate SQL Servers.

Pros:
+ Works with no network connection

Cons:
- Only lists SQL Server 2005 and later
- Only finds registered servers

Putting the methods together

If you're after a list which matches that shown in SSMS (or Enterprise Manager) then looking for registered servers will probably be an acceptable solution. If, on the other hand, you want to show all available SQL Servers (as in the SSMS "Browse for Servers" dialog), then your best bet is probably to combine one of the UDP methods with a list of registered servers. This will give you a reliable list of registered servers even when the network is down, and will include unregistered servers where possible.

One of the most user-friendly ways of doing this is to initially populate your control with the registered servers (which can be done very quickly) but allow the user to initiate a search for further network servers (which can take several seconds) in case they're looking for an unregistered server. You should always allow the user to type in a server name too, as the server may be available even though it's not returned by the UDP methods described above.

And another thing...

Depending on your requirements, you may need to include the instance names in the list. The methods I've mentioned all return instance names of the non-default instances (e.g. MyPC\SQLEXPRESS) but they handle them in different ways - the MSDN documentation gives full details.

Extending SMO Classes with Extension Methods and IEnumerable(T)

jonsayce — Sat, 09 Feb 2008 14:50:00 GMT

One of the simplest ways to audit changes to a table is to have a trigger that stores a copy of any row that's inserted/updated/deleted, together with some meta data such as who made the change and when they made it. There are limitations (such as several types which can't be used in the inserted and deleted tables) but often this method of auditing will be sufficient.

Because of the limitations, you will normally need to specify a column list in your trigger, and this list needs to be kept up to date if the table being audited is ever changed.

While building a tool to keep audit triggers and audit tables in synch with the tables being audited, I recently found myself trying to inherit from various sealed SMO classes. Having discovered that this was impossible, I tried a different tack: extending the SMO classes with .NET 3.5 extension methods.

As ScottGu's examples show, extension methods can be extremely simple.

I added a simple method to the Column class to expose whether the column's type was available in the inserted and deleted tables (i.e. whether the audit trigger could use it), then extended the Table class to expose a collection of these auditable columns.

The solution is actually extremely simple, but I found very little written about using IEnumerable or IEnumerable(T) within an extension method. That may be because it's so straightforward that no-one has written about it, but hopefully someone will find this useful...

One thing which would be nice in a future .NET release is extension properties - both the methods mentioned above would be more satisfactory if they were properties.

How to fix slow SMO performance

jonsayce — Sat, 09 Feb 2008 12:16:00 GMT

By default, when SMO queries the database for the properties of an object, it only loads the basic properties. This reduces the number of tables it needs to join in the query, so dramatically improves performance, as long as you don't need the other properties.

As an example, lets look at building a simple front end to display the tables in a database and the columns of the selected table.

Using SMO to request the TableCollection, the query requests just the schema name and table name from sys.tables. This is perfect for our requirements: we can populate our list of tables using the names and we don't care about the other details. We've saved time because we didn't join to the other system tables we would have needed if we were interested in properties such as indexing, keys, partitioning etc.

Now we come to populate the column details. If we use SMO in its default mode then populating the ColumnsCollection of our table will use a simple query getting the column's ID and name, joining sys.tables with sys.all_columns:

If we were only displaying the column name that that would be fine. However, our listview is going to show more than just the name, and we create our listviewitem objects using some other interesting properties of the column:

Running SQL Server Profiler you'll notice that SMO has lost its way a bit. We'd expect it to fire another query to get the extended properties, but it does the extended query once for each column! Given that the full query now joins 13 tables, it's no surprise that people complain about SMO being slower than DMO.

SetDefaultInitFields
The solution to this problem is to tell SMO what you're going to need in advance. Using SetDefaultInitFields you can tell SMO to load all extra properties or you can even specify a list of exactly which properties you're going to need.

SetDefaultInitFields is set at the server level. The simplest way to set it is just to tell it that all column properties should be loaded at once.

Checking the SQL Profiler again reveals that the initial query is now the 13-table version, so is a little bit slower, but there are no further queries. If we were to specify the exact list of properties we were interested in then we could cut down the number of tables joined and make it even faster.

Bonus improvements: AddRange and BeginUpdate
If you do write an application which sticks the column details into a listview, there are two other ways to improve performance: the AddRange method of the listview adds a collection or array of listviewitems to the listview in one operation, so avoids the nasty flickering as the control is repainted between add operations. If you prefer to use add, then the other way to stop the flickering is to use BeginUpdate and EndUpdate.

Customising the SSMS Table Designer

jonsayce — Mon, 28 Jan 2008 09:50:00 GMT

Rick Strahl has a great post on making the SSMS Table Designer more keyboard-friendly by removing the need to have two panes:

Uninstalling an SSMS Add-in

jonsayce — Fri, 25 Jan 2008 22:49:00 GMT

One thing I forgot to mention in my recent posts on SSMS add-ins was how to uninstall them. Normally this can be done using the standard Windows uninstall methods (Add/Remove Programs, uninstall the MSI, etc) but sometimes during development you may find you're stuck with an unwanted add-in.

The simplest thing to do in this case is what SSMS does itself when an add-in fails to initialise properly: simply remove the registry setting that causes the add-in to be loaded. Delete the key for the add-in, which will be something like this:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Shell\AddIns\Namespace.Class

This will guarantee that the add-in is never loaded again, though obviously it doesn't remove the other registry settings or the add-in files themselves.

The other problem it can leave you with is unwanted menu and toolbar items in SSMS. These can be located and removed using a similar technique to that described in section 4 of my post on building an add-in. Deleting a command should remove any menu items connected to the command.

Third-party IFrames in HTA Applications

jonsayce — Tue, 22 Jan 2008 13:43:00 GMT

I've posted before about setting Application = "Yes" on iframes in HTA applications, but if the iframe is rendered by a third-party component then this may not be straightforward. If the iframe runs JavaScript accessing it's parent window then somehow the iframe must be modified to pass the security rules enforced by the HTA model.

Using the onload event of the <BODY> element it's possible to set the Application attribute of an iframe, but this is not enough to make the iframe HTA-friendly.

Refreshing the contents of the iframe (using iframe.src = iframe.src) doesn't work either. I'm guessing that the security aspects of an iframe are baked into the element when the browser first renders it.

The solution is to use the onload event of the body to rebuild the entire iframe element:

if (document.getElementById('spIFrame')) {

  // get reference to the iFrame

  var iFr = document.getElementById('spIFrame');
   
  // set the application attribute of the iFrame (so it works in HTA)

  iFr.application = 'Yes';

  // rebuild the iFrame to make sure Application="Yes" is active

  iFr.outerHTML = iFr.outerHTML;

}