The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin distributed transaction

One morning I get a call from a developer about this issue. It turns out that the MSDTC service had stop on a Linked server. So, to resolve this issue, you must first start the MSDTC service on both servers involved. Windows 2008 example here: From "administrative tools" -> "component services" -> "computers" -> "my computer" , "local dtc" right click "properties" goto MSDTC tab and select "security configuration", enable checkbox for "network dtc access" and "allow outbound". Check No Authentication Required.

How to migrate databases from MS sql 2000 to MS SQL 2005/2008

There are 4 ways to migrate databases from one server to another:

1. backup bak files and restore to new server
2. copy the mdf and ldf files to new server and attached the databases to new server
   
3. use the migration wizard to move the objects and databases over
4. script the entire restore process and run in sqlcmd

DTS or SSIS works manual not scheduled???

By default , when you run an SSIS package manually it assumes your current login . However, when you schedule a package, sql uses the account running MS SQL agent service.

So you can either change the account running the agent service or create a proxy account.
To change the account running the sql agent, please use MS SQL server configuration manager.

Lastly, create a proxy in the SQL studio manager under SQL agent>proxy