SQL Server 2005 maintenance plan fails and service pack fail to install

I needed to setup a maintenance plan for a client to backup their databases and remove backups older than 4 weeks. They run SQL Server 2005, so I created the maintenance plan with a Back Up Database Task and a Maintenance Cleanup Task to remove the old backups, which is straightforward enough. When I tried to test it however I got the error: **“Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed." **

I had a quick look at the version of the server and it was 9.0.1399 (SQL Server 2005 RTM!!!). So I downloaded SP4 and went to install it, however when it gave me the options for the components to update, it wouldn’t let me check Database Services and when I selected it, the details box said "This update requires language ENU. The language for product instance MSSQLSERVER is . Download the update for language .”

Now, I know this particular server hosts a lot of Sage 200 databases as well as the database for my app. The guy that setup Sage 200 changed the language to be British English because for some reason Sage don’t use ISO date formatting (yyyyMMdd) and instead use UK style dates (ddMMyyyy).

The fix for this was to change the language values for SQL Server in the registry.

Stop the SQL Server services via services.msc, fire up regedit.exe and write down the current decimal value for the Language value in the following two keys.

HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL.1setup

HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerCurrentVersion

Now change them both to 1033 decimal (which is US English) and re-run SP4 setup, which should now allow you to upgrade the Database Services component. Once you’re upgraded return the registry values to their original settings.

I actually ran into another problem after this (Error Code: 0x80070534 (1332) Windows Error Text: No mapping between account names and security IDs was done.), which I had to dig around in the SP4 install log for, but it turned out quite easy to fix by deleting some registry keys and letting SP4 setup recreate them.