It takes years to output data via Microsoft SQL Server BCP (Bulk Copy Program utility)?

If you are using Microsoft SQL Server BCP (Bulk Copy Program) utility, or a.k.a. XPShellCmd, and you found that your scripts take forever to execute.

Solution:

a). Always make sure your Select statement comes with NoLock for each tables.

Update OpenQuery v.s. Update LinkedServer.Table

In our recent findings, we have found that the Update statement with LinkedServer could cause database performance issue, which end up giving you a "server is not responding" or timeout error message.

The solution to resolve this issue, is to change the Update LinkedServer.Table statement to Update OpenQuery statement.

This solution is tested and proven workable.