The live DB gave me a surprise in the early morning today:

“OLE DB provider “SQLNCLI10” for linked server “linkedserver01” returned message “Deferred prepare could not be completed.”.
Msg 7399, Level 16, State 1, Line 2
OLE DB provider ‘MSDASQL’ reported an error. The provider reported an unexpected catastrophic failure.
OLE DB error trace [OLE/DB Provider ‘MSDASQL’ IDBInitialize::Initialize returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].”

1. First thing that came into my mind-> connection down? Try to ping and looks good. Hmm, what else?

2. Credential issues? but seems not possible cause it should be return Access Denied message.

3. Query syntax error? Hmm, SQL Server should return something else to warn me about syntax is wrong.

4. Finally found out the problem was on the linked server on that linked server “linkedserver01”  !!! Just need to email the DBA of USA about this finding, job done! 😉

5. Updated from DBA – was because of MySQL driver issue, issue solved after rebooted the server.