I have a stored procedure that creates log entries during a data warehouse load that don't get rolled back if the load fails (and rolls back) by using SQL-DMO to connect back in to the SQL Server and therefore put the entry outside the scope of the transaction.
The object is created here:
EXEC sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT
I've migrated the warehouse from SQL Server 2000 to 2008 and this is now returning NULL, presumably because SQL-DMO is no longer implemented.
Is there an alternative to use in place of SQLDMO.SQLServer that behaves in the same way?
If not, can I just install SQL-DMO on the server and be done with it?