Here are some scripts that demonstrate the OLE automation capabilities of T-SQL. The examples that use SQL Server as automation server needs to be modified depending on the version of SQL Server - 'SQLOLE' pertains to SQL60/65 & 'SQLDMO' pertains to SQL70.
Click here to download the SQL scripts.
1) SP from Books Online to convert hex values to decimal.
2) SP from Books Online to display OLE automation errors.
3) How to create a word document from a T-SQL SP?
4) How to trap error messages without sending to client?
5) How to use ExecuteWithResults & GetRangeString methods?
6) How to use the methods on collections?