PROBLEM: MSDTC seems to be working. e.g. Linked queries to servers or in this case a text file can be queried etc. while running individual commands in Query Analyzer, but fail running the entire procedure.
Not caring the whys of it, what is wrong with DTC that we can fix?
For Windows 2003 SP1, see these instructions. Basically don’t use Local System account. Try this account and search for the local account login of NetworkService. When it comes up there is something in the password field, ooops. Seems to work if we clear both password textboxes.
After your changes simply bounce the MSDTC service and run your SQL query again. This time it should work.