SQL Server, Windows locales, and the default user

I've run into this before, so I'm writing it down now.

I have a package of software that contains an interactive component, as well as several services. All pieces add and update SQL server database entries that contain dates. These dates are of DateTime type.

We ran the package on a machine that was originally configured with a Canadian locale, and its associated date formats. At some point, the admin user account had its locale changed to US and then SQL server 2005 was installed.

The problem was that the interactive component created database entries with correct date formats, but the services were trying to update with incorrect date formats. Both pieces got their date values using the DateTime.Now function.

Turns out that the services were running as the LocalSystem user, and the interactive piece was running as the logged in admin user. The LocalSystem uses the locale associated with the Default user. The admin user had been configured with a US locale, and the Default user still had the Canadian locale that was set during the initial Windows install.

To fix this,

- Open Control Panel
- Select Regional and Language Options
- In XP, select the Advanced tab
- Select the check box "Apply all settings to the current user and to the Default user profile"
- Click Apply
- In Vista, select the Administrative tab
- Click the "Copy to reserved accounts..."
- Click Apply

You will need to restart any services that are running as the LocalSystem user for this to take effect.

Whew!!