It all depends upon the target database server. http://msdn.microsoft.com/en-us/library/ms188279.aspx

This link has a nice diagram for linked servers. As you can see, the provider can be anything. With each ODBC or OLE DB provider, the syntax is a little different.

I try to stick with ANSI standard SQL when possible to make the query portable.

As for errors, I pointed out three conditions that I think should be move to the WHERE clause.

If you are having issues with a linked server, trace what is going on at the server.

For ODBC, enable logging.

For SQL Server or Oracle, start a trace.

To wrap this up, if it is sporadic issue with target server, look at the server options page. Make sure you understand the options.

http://msdn.microsoft.com/en-us/library/ms186839(v=sql.105).aspx

Also, make sure MSDTC is enabled if you are doing distributed transactions.

http://support.microsoft.com/kb/2027550

Like any normal debugging session, look at your logs. Both Windows & SQL Server during the time when the issue occurs.

Without any more detail such as an error message or id, I am only guessing at what might be the issue.

John