I've been running our usual deployment scripts against a SQL 2005 database to see what the damage was. Not too bad as it turns out, 2 procedures which used alias.columnname in the ORDER BY clause, 2 logons where the password wasn't secure enough, and 2 DTS packages which aren't deployed due to :
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

I want to use sp_configure to achieve this, as our product is deployed mainly automatically, including the database scripts. Most of the help I could find revolved around the SQL Server Surface Area Configuration and using either the SQL Server 2005 Surface Area Configuration tool or even the command line sac Utility. I decided to initially take a look at the tool to get an idea, and on investigation, the option I need falls under Surface Area Configuration for Features and is listed under OLE Automation. After a bit more digging, I found that the sp_configure option is Ole Automation Procedures and so the code is:
EXEC sp_configure 'Ole Automation Procedures';
GO
but this results in the following:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.
and so needs to be paired with the Show Advanced Options setting, and so the following is required:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
This results in the following:
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
and when I re-run the deployment scripts I get no errors. Hurrah!